Regarding 1 and 2: these sound like bugs. Can you please log a JIRA case for
each? If you can supply a test case, even better.
Regarding 3. I checked the SQL standard, and the required behavior is to throw
an exception. (Maybe ArrayIndexOutOfBoundsException isn’t the best exception to
throw.)
Julian
From SQL:2014...
6.24 <array element reference> Function
Return an element of an array.
Format
<array element reference> ::=
<array value expression>
<left bracket or trigraph> <numeric value expression> <right bracket or
trigraph>
Syntax Rules
1) The declared type of an <array element reference> is the element type of
the speci ed <array value expression>.
2) The declared type of <numeric value expression> shall be exact numeric with
scale 0 (zero).
Access Rules
None.
General Rules
1) If the value of <array value expression> or <numeric value expression> is
the null value, then the result of <array element reference> is the null value.
2) Let i be the value of <numeric value expression>. Case:
a) If i is greater than zero and less than or equal to the cardinality of
<array value expression>, then the result of <array element reference> is the
value of the i-th element of the value of <array value expression>.
b) Otherwise, an exception condition is raised: data exception — array element
error.
> On Sep 25, 2016, at 5:24 PM, Jungtaek Lim <[email protected]> wrote:
>
> Hi devs,
>
> I'm experimenting with ARRAY and MAP type with Calcite's JaninoRexCompiler.
> (with Calcite 1.9.0)
>
> While testing I found some behaviors, so would like to see they're
> intentional
> or bugs we want to resolve.
>
> 1. Even though I set map's key/value type or array's type explicitly,
> compiler ignores the value type and create assignment to Object.
>
> This makes extracting value from nested collection not possible. Also
> comparing extracted value with numeric constant, too.
> (For example, suppose there's a row which MAPFIELD['a'] has 1 as value.
> Writing MAPFIELD['a'] = 1 on where statement throwing an Exception saying
> there's no SqlFunctions.eq(Object, int).
>
> Btw, I took a breakpoint on BinaryImplementor.implement() to see variables
> just
> before compiler finds SqlFunctions.eq().
> The type of expression is Object which is not generic, but seems like
> matching
> RexCall operand have value type information. We might create a quick fix
> from
> here (handling type conversion) but I'm not sure this can clearly resolve
> this.
>
> 2. I just replace their type to ANY, and wrap expression with CAST like
> MAPFIELD['a'] -> CAST(MAPFIELD['a'] AS INTEGER) = 1 and succeed.
> (While using MAP type even I wrap to CAST, above error happens.)
>
> But it doesn't work when MAPFIELD['a'] is null because CAST AS INTEGER
> calls
> SqlFunctions.toInt(null) which throws RuntimeException.
> (It happens MAPFIELD['a'] doesn't have item which has 'a' as key. I guess
> same
> applies to null value. And also same applies to ARRAY, and
> ARRAYFIELD[<non integer>].)
>
> CAST(COALESCE(MAPFIELD['a'], -1) AS INTEGER) also throws Exception since
> MAPFIELD['a'] is Object and -1 is int.
> (CompileException : Incompatible expression types "java.lang.Object" and
> "int")
>
> 3. ArrayOutOfIndexException is thrown when query is trying to access the
> array
> with out of index. I don't know there's a standard rule on this, but at
> least
> PostgreSQL doesn't throw an Exception but just treats it as null.
> https://www.postgresql.org/docs/9.1/static/arrays.html
> (This means the result of ITEM operator is nullable.)
>
> Any ideas on these behaviors?
>
> Thanks,
> Jungtaek Lim (HeartSaVioR)
