[jira] [Commented] (DRILL-5401) isnotnull(MAP-REPEATED) - IS NULL / IS NOT NULL over a list in JSON

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-5401?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15950643#comment-15950643 ] 

Khurram Faraaz commented on DRILL-5401:
---------------------------------------

The SQL was incorrect in the above example, fixing the SQL results in SchemaChangeException

{noformat}
0: jdbc:drill:schema=dfs.tmp> select t.a.b.c from `empty_array.json` t where t.a.b.c is not null;
Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema.  Errors:

Error in expression at index -1.  Error: Missing function implementation: [isnotnull(MAP-REPEATED)].  Full expression: --UNKNOWN EXPRESSION--..

Fragment 0:0

[Error Id: e1b65f30-6f40-43f4-8162-9cb54d6f5a81 on centos-01.qa.lab:31010] (state=,code=0)
0: jdbc:drill:schema=dfs.tmp> select t.a.b.c from `empty_array.json` t where t.a.b.c is null;
Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema.  Errors:

Error in expression at index -1.  Error: Missing function implementation: [isnull(MAP-REPEATED)].  Full expression: --UNKNOWN EXPRESSION--..

Fragment 0:0

[Error Id: c964c93d-0573-4598-a3ee-6d8abc3abff0 on centos-01.qa.lab:31010] (state=,code=0)
{noformat}

> isnotnull(MAP-REPEATED) - IS NULL / IS NOT NULL over a list in JSON
> -------------------------------------------------------------------
>
>                 Key: DRILL-5401
>                 URL: https://issues.apache.org/jira/browse/DRILL-5401
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Flow
>    Affects Versions: 1.11.0
>            Reporter: Khurram Faraaz
>
> Checking if a list is null or if it is not null, results in SchemaChangeException.
> Drill 1.11.0 commit id: adbf363d
> Data used in test
> {noformat}
> [root@centos-01 ~]# cat empty_array.json
> { "a": { "b": { "c": [ { "d": { "e": "f" } } ] } } }
> { "a": { "b": { "c": [] } } }
> {noformat}
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> alter session set `store.json.all_text_mode`=true;
> +-------+------------------------------------+
> |  ok   |              summary               |
> +-------+------------------------------------+
> | true  | store.json.all_text_mode updated.  |
> +-------+------------------------------------+
> 1 row selected (0.189 seconds)
> 0: jdbc:drill:schema=dfs.tmp> select * from `empty_array.json`;
> +--------------------------------+
> |               a                |
> +--------------------------------+
> | {"b":{"c":[{"d":{"e":"f"}}]}}  |
> | {"b":{"c":[]}}                 |
> +--------------------------------+
> 2 rows selected (0.138 seconds)
> /* wrong results */
> 0: jdbc:drill:schema=dfs.tmp> select * from `empty_array.json` t where t.b.c IS NULL;
> +--------------------------------+
> |               a                |
> +--------------------------------+
> | {"b":{"c":[{"d":{"e":"f"}}]}}  |
> | {"b":{"c":[]}}                 |
> +--------------------------------+
> 2 rows selected (0.152 seconds)
> /* wrong results */
> 0: jdbc:drill:schema=dfs.tmp> select * from `empty_array.json` t where t.b.c IS NOT NULL;
> +----+
> | a  |
> +----+
> +----+
> No rows selected (0.154 seconds)
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)