[
https://issues.apache.org/jira/browse/DRILL-4924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15542662#comment-15542662
]
Jinfeng Ni commented on DRILL-4924:
-----------------------------------
I think this has nothing to do with IN list or case expression. Rather, it's
caused by the fact that Drill / Calcite does not allow a SELECT statement
without FROM clause. Postgres allows that syntax. But that's not SQL standard.
Oracle has "select ... from DUAL", but not "select ..." without FROM.
If you try this "SELECT 100", you will see the same error from parser as what
you saw for your query.
> 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)
