[jira] [Created] (DRILL-4924) Can not use case expression within an IN predicate.

[Khurram Faraaz (JIRA)]

Khurram Faraaz created DRILL-4924:
-------------------------------------

             Summary: 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


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)