[DRILL-5628] Drill pushing invalid SQL to ORACLE when not between predicate used

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.10.0
• Component/s: Server

Environment

Drill 1.10
ORACLE 12C2

• Assignee: Unassigned
• Reporter: N Campbell
• Created: 29/Jun/17 20:22
• Updated: 29/Jun/17 20:22

Description

plugin to ORACLE 12C2 via JDBC defined

following query sent to DRILL which fails in ORACLE

select RNUM,TJOIN2.C1, TJOIN2.C2 from certora.DBCERT.TJOIN2 where not TJOIN2.C2 between 'AA' and 'CC'

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

SELECT *
FROM "DBCERT"."TJOIN2"
WHERE NOT "C2" >= 'AA' AND (NOT "C2") <= 'CC'

ORACLE DDL

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