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

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

Environment

Drill 1.10
SQL Server 2016

• Assignee: Unassigned
• Reporter: N Campbell
• Created: 01/Jul/17 13:54
• Updated: 01/Jul/17 13:54

Description

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