[DRILL-4937] AND of <INTEGER> AND <BOOLEAN> should fail

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.9.0
• Component/s: Execution - Data Types
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 10/Oct/16 10:59
• Updated: 10/Oct/16 10:59

Description

Drill 1.9.0 git commit id : 4edabe7a

Calcite returns an error

0: jdbc:calcite:model=target/test-classes/mod> select * from emps where 1 = ( case when empno=99 and empno in (1,6,99,100) then 1 when coalesce (empno,0) and empno in(empno) then 1 else 0 end);
Error: Error while executing SQL "select * from emps where 1 = ( case when empno=99 and empno in (1,6,99,100) then 1 when coalesce (empno,0) and empno in(empno) then 1 else 0 end)": From line 1, column 89 to line 1, column 126: Cannot apply 'AND' to arguments of type '<INTEGER> AND <BOOLEAN>'. Supported form(s): '<BOOLEAN> AND <BOOLEAN>' (state=,code=0)
0: jdbc:calcite:model=target/test-classes/mod>

Postgres 9.3 returns similar error

postgres=# select * from emp_tbl where 1 = ( case when id=99 and id in (1,6,99,100) then 1 when coalesce (id,0) and id in(id) then 1 else 0 end);
ERROR:  argument of AND must be type boolean, not type integer
LINE 1: ...ase when id=99 and id in (1,6,99,100) then 1 when coalesce (...

^
Drill 1.9.0 returns results for same query

0: jdbc:drill:schema=dfs.tmp> select * from `emp_tbl` where 1 = ( case when id=99 and id in (1,6,99,100) then 1 when coalesce (id,0) and id in(id) then 1 else 0 end);
+-----+-----------+------+--------+----------+
| id  |   name    | age  | state  |  salary  |
+-----+-----------+------+--------+----------+
| 1   | John Doe  | 51   | AZ     | 1000000  |
+-----+-----------+------+--------+----------+
1 row selected (0.148 seconds)