Strange behavior on MAP and ARRAY type

[Jungtaek Lim <ka...@gmail.com>]

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)

Re: Strange behavior on MAP and ARRAY type

[Jungtaek Lim <ka...@gmail.com>]

Sorry wrong recipients. It should be sent to Calcite dev. mailing list.

2016년 9월 24일 (토) 오후 3:51, Jungtaek Lim <ka...@gmail.com>님이 작성:

> 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)
>