[DRILL-1902] Exception when running query on json file and where clause containing cast to boolean

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.7.0
• Fix Version/s: 0.9.0
• Component/s: Execution - Data Types
• Assignee: Hanifi Gunes
• Reporter: Victoria Markman
• Created: 19/Dec/14 19:19
• Updated: 30/Jan/15 00:19
• Resolved: 30/Jan/15 00:19

Description

  git.commit.id.abbrev=e3ab2c1 

– JSON file

{ "a1": 0, "b1": 0, "c1": "true" }
{ "a1": 0, "b1": 0, "c1": "false" }
{ "a1": 0, "b1": 0, "c1": "false" }
{ "a1": 1, "b1": 1, "c1": "true" }
{ "a1": 1, "b1": 1, "c1": "true" }

– With JSON file

alter session set `planner.slice_target`= 1;
select  a1,
        b1,
        count(*)
from    `t.json`
where   cast(c1 as boolean) and 1=1
group by
        a1,
        b1;

Failure:

Error: exception while executing query: Failure while executing query. (state=,code=0)
0: jdbc:drill:schema=dfs> select  a1,
. . . . . . . . . . . . >         b1,
. . . . . . . . . . . . >         count(*)
. . . . . . . . . . . . > from    `t.json`
. . . . . . . . . . . . > where   cast(c1 as boolean) and 1=1
. . . . . . . . . . . . > group by
. . . . . . . . . . . . >         a1,
. . . . . . . . . . . . >         b1;
Query failed: Query failed: Failure while trying to start remote fragment, Expression has syntax error! line 1:28:no viable alternative at input 'BIT' [ 9d7f0746-15cd-41d6-bca2-c545356513e4 on atsqa4-133.qa.lab:31010 ]


Error: exception while executing query: Failure while executing query. (state=,code=0)

Same works with parquet files:

0: jdbc:drill:schema=dfs> create table test(a1, b1, c1) as select a1, b1, cast(c1 as boolean) from `t.json`;
+------------+---------------------------+
|  Fragment  | Number of records written |
+------------+---------------------------+
| 0_0        | 5                         |
+------------+---------------------------+
1 row selected (0.216 seconds)
0: jdbc:drill:schema=dfs> select * from test;
+------------+------------+------------+
|     a1     |     b1     |     c1     |
+------------+------------+------------+
| 0          | 0          | true       |
| 0          | 0          | false      |
| 0          | 0          | false      |
| 1          | 1          | true       |
| 1          | 1          | true       |
+------------+------------+------------+
5 rows selected (0.083 seconds)
0: jdbc:drill:schema=dfs> select  a1,
. . . . . . . . . . . . >         b1,
. . . . . . . . . . . . >         count(*)
. . . . . . . . . . . . > from    test
. . . . . . . . . . . . > where   c1 and 1=1
. . . . . . . . . . . . > group by
. . . . . . . . . . . . >         a1,
. . . . . . . . . . . . >         b1;
+------------+------------+------------+
|     a1     |     b1     |   EXPR$2   |
+------------+------------+------------+
| 0          | 0          | 1          |
| 1          | 1          | 2          |
+------------+------------+------------+
2 rows selected (0.295 seconds)

Important note: I can't reproduce the same failure without setting

 alter session set `planner.slice_target`= 1;