[DRILL-4924] Can not use case expression within an IN predicate.

• Type: Bug
• Status: Reopened
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.9.0
• Fix Version/s: Future
• Component/s: Query Planning & Optimization
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 03/Oct/16 14:35
• Updated: 09/Nov/16 00:42

Description

Can not use case expression within an IN predicate.
Drill 1.9.0 git commit ID: f3c26e34
Similar query works on Postgres 9.3

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)

Drill 1.9.0 returns an error

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)

Issue Links

• Blocked: CALCITE-1120 Support SELECT without FROM syntax (similar to MySQL)
• depends upon: CALCITE-1120 Support SELECT without FROM syntax (similar to MySQL)