[DRILL-3664] CAST integer zero , one to boolean false , true

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.2.0
• Fix Version/s: 1.17.0
• Component/s: SQL Parser
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 18/Aug/15 17:37
• Updated: 08/Jul/19 13:35
• Resolved: 08/Jul/19 13:34

Description

We should be able to cast (zero) 0 to false and (one) 1 to true, currently we report a parse error when an explicit cast is used in query.

col7 is of type Boolean in the below input parquet file.

0: jdbc:drill:schema=dfs.tmp> select col7 from FEWRWSPQQ_101 where col7 IN (cast(0 as boolean),cast(1 as boolean));
Error: PARSE ERROR: From line 1, column 47 to line 1, column 64: Cast function cannot convert value of type INTEGER to type BOOLEAN


[Error Id: d751945f-8a0f-4369-ae9e-c42504f6d978 on centos-04.qa.lab:31010] (state=,code=0)

Without explicit cast we see SchemaChangeException.

0: jdbc:drill:schema=dfs.tmp> select col7 from FEWRWSPQQ_101 where col7 IN (0,1);
Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema.  Errors:
 
Error in expression at index -1.  Error: Missing function implementation: [castINT(BIT-OPTIONAL)].  Full expression: --UNKNOWN EXPRESSION--.
Error in expression at index -1.  Error: Missing function implementation: [castINT(BIT-OPTIONAL)].  Full expression: --UNKNOWN EXPRESSION--..

Fragment 0:0

[Error Id: ecf51dae-62c5-40d7-b0f5-3b9bf9fd3377 on centos-04.qa.lab:31010] (state=,code=0)

Postgres results for the same query.

postgres=# select col7 from FEWRWSPQQ_101 where col7 IN (cast(0 as boolean),cast(1 as boolean));
 col7 
------
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
 f
 t
(22 rows)