[teiid-issues] [JBoss JIRA] (TEIID-247) Add support for array types

Tue Aug 13 11:03:26 EDT 2013

    [ https://issues.jboss.org/browse/TEIID-247?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12796534#comment-12796534 ] 

Steven Hawkins edited comment on TEIID-247 at 8/13/13 11:01 AM:
----------------------------------------------------------------

Added initial support:

The parser will accept h2 like array expressions:
\(\) or \(,\) - are empty array expressions
\(expr,\) - is a single array value, such that a trailing comma is required
\(expr,...\) - in general where a trailing comma is optional

And the parser will allow the cast to an array type using the standard type names with square braces for the dimensions:

cast\(obj as string[][]\)

And an array element reference expression of:

obj[index]

In ddl:

create view v \(x int[]\) as select \(1,2,3\);

There is minimal validation.  And we nominally use a runtime value of ArrayImpl - we use the java array type system to make the values more type safe.  So it's not required that the runtime type matches the value type.

For example

\(1,2\) will have a runtime type of integer[].  And we'll understand that \(1,2\)[1] is an integer.

Pushdown support is only enabled for a few jdbc databases - and no import support has yet been implemented.  Primitive array values from a source are allowed to remain an primitive array until cast as a typed Teiid array - in which case it will be converted to using wrapper objects.

Known issues:
* Support is just memory based.  Large arrays should be avoided.  array_agg is hard coded to limit to only 1000 entries.
* Limited Array method support - you cannot yet use the getResultSet methods
* Arrays of lobs will not stream appropriately over a socket transport.
* ODBC arrays are always sent to the client as string arrays.
* multidimensional primitive arrays will not yet convert to typed multidimensional teiid arrays
* casting to/from array types is limited to/from the object type - e.g. casting a 1 dimensional object[] to a 2 dimensional integer array will work, but you cannot yet cast from integer[] to long[].

Additional support may be added under this issue, but in general will be worked as needed.  This will include:

* array subquery comparison \(col, col1\) = \(select col, col1 .... \)
* a subquery array constructor
* similar to postgresql the use of ANY, TABLE, and other useful array constructs 
* possibly alternative syntax, such as the explicit ARRAY [...] or curly braces.

      was (Author: shawkins):
    Added initial support:

The parser will accept h2 like array expressions:
\(\) or \(,\) - are empty array expressions
\(expr,\) - is a single array value, such that a trailing comma is required
\(expr,...\) - in general where a trailing comma is optional