Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-4924

Can not use case expression within an IN predicate.

    XMLWordPrintableJSON

    Details

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                khfaraaz Khurram Faraaz
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: