[jira] [Created] (DRILL-5650) Drill pushed invalid SQL to SQL Server when NOT IN predicate used

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

N Campbell created DRILL-5650:
---------------------------------

             Summary: Drill pushed invalid SQL to SQL Server when NOT IN predicate used
                 Key: DRILL-5650
                 URL: https://issues.apache.org/jira/browse/DRILL-5650
             Project: Apache Drill
          Issue Type: Bug
          Components:  Server
    Affects Versions: 1.10.0
         Environment: Drill 1.10
SQL Server 2016
            Reporter: N Campbell


plugin to SQL Server via JDBC defined

Following statement sent to Drill which generates invalid SQL to SQL Server

select RNUM,TJOIN2.C1, TJOIN2.C2 from certsqlserver.dbo.TJOIN2 where not TJOIN2.C2 in ('ZZ','BB','EE')


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

SELECT *
FROM "dbo"."TJOIN2"
WHERE NOT "C2" = 'ZZ' OR (NOT "C2") = 'BB' OR (NOT "C2") = 'EE'


SQL Server DDL


create table "TJOIN2" ("RNUM" integer   not null , "C1" integer, "C2" char(2));
go
			

grant select on "TJOIN2" to public;
go
			
insert into "TJOIN2" ("RNUM", "C1", "C2") values ( 0, 10, 'BB');
go
			
insert into "TJOIN2" ("RNUM", "C1", "C2") values ( 1, 15, 'DD');
go
			
insert into "TJOIN2" ("RNUM", "C1", "C2") values ( 2, NULL, 'EE');
go
			
insert into "TJOIN2" ("RNUM", "C1", "C2") values ( 3, 10, 'FF');
go
			




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