[jira] [Created] (DRILL-5632) Drill pushing invalid SQL to ORACLE when case expression with in predicate and subquery used

["N Campbell (JIRA)" <ji...@apache.org>]

N Campbell created DRILL-5632:
---------------------------------

             Summary: Drill pushing invalid SQL to ORACLE when case expression with in predicate and subquery used
                 Key: DRILL-5632
                 URL: https://issues.apache.org/jira/browse/DRILL-5632
             Project: Apache Drill
          Issue Type: Bug
          Components:  Server
    Affects Versions: 1.10.0
         Environment: Drill 1.10
ORACLE 12C2
            Reporter: N Campbell


plugin to ORACLE 12C2 using JDBC defined

following SQL statement sent to Drill

select TJOIN1.RNUM, TJOIN1.C1, case when 10 in ( select 1 from certora.DBCERT.TVERSION ) then 'yes' else 'no' end from certora.DBCERT.TJOIN1

DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. 

sql SELECT "t"."$f0" "RNUM", "t"."$f1" "C1", CASE WHEN TRUE THEN 'yes' ELSE 'no' END "EXPR$2"
FROM (SELECT "RNUM" "$f0", "C1" "$f1", 10 "$f3"
FROM "DBCERT"."TJOIN1") "t"
INNER JOIN (SELECT "EXPR$0"
FROM (SELECT 1 "EXPR$0"
FROM "DBCERT"."TVERSION") "t0"
GROUP BY "EXPR$0") "t1" ON "t"."$f3" = "t1"."EXPR$0"

ORACLE DDL

  CREATE TABLE "DBCERT"."TJOIN1" 
   (	"RNUM" NUMBER(*,0), 
	"C1" NUMBER(*,0), 
	"C2" NUMBER(*,0)
   ) ;
  GRANT SELECT ON "DBCERT"."TJOIN1" TO PUBLIC;



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)