[ https://issues.apache.org/jira/browse/DRILL-4924?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kunal Khatua reopened DRILL-4924: --------------------------------- Reopened to track re-basing to Calcite-1.8.0 > Can not use case expression within an IN predicate. > --------------------------------------------------- > > Key: DRILL-4924 > URL: https://issues.apache.org/jira/browse/DRILL-4924 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.9.0 > Reporter: Khurram Faraaz > Fix For: Future > > > Can not use case expression within an IN predicate. > Drill 1.9.0 git commit ID: f3c26e34 > Similar query works on Postgres 9.3 > {noformat} > postgres=# select * from t1 where c1 in ( select case when c1=2 then 30 else > c1 end); > c1 | c2 > ----+----- > 1 | 0 > 3 | 19 > -1 | 11 > 5 | 13 > 10 | 17 > 11 | -1 > 13 | 1 > 17 | 20 > 0 | 9 > 19 | 100 > (10 rows) > {noformat} > Drill 1.9.0 returns an error > {noformat} > 0: jdbc:drill:schema=dfs.tmp> select * from `emp_tbl` where id IN ( SELECT > CASE WHEN id=2 THEN 30 else id end ); > Error: PARSE ERROR: Encountered ")" at line 1, column 81. > Was expecting one of: > "FROM" ... > "," ... > "AS" ... > <IDENTIFIER> ... > <QUOTED_IDENTIFIER> ... > <BACK_QUOTED_IDENTIFIER> ... > <BRACKET_QUOTED_IDENTIFIER> ... > <UNICODE_QUOTED_IDENTIFIER> ... > "NOT" ... > "IN" ... > "BETWEEN" ... > "LIKE" ... > "SIMILAR" ... > "=" ... > ">" ... > "<" ... > "<=" ... > ">=" ... > "<>" ... > "+" ... > "-" ... > "*" ... > "/" ... > "||" ... > "AND" ... > "OR" ... > "IS" ... > "MEMBER" ... > "SUBMULTISET" ... > "MULTISET" ... > "[" ... > SQL Query select * from `emp_tbl` where id IN ( SELECT CASE WHEN id=2 THEN 30 > else id end ) > > ^ > [Error Id: e6c3f120-8776-476e-8df7-7ef30f6b7307 on centos-01.qa.lab:31010] > (state=,code=0) > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)