[DRILL-5648] Drill pushes invalid SQL to SQL Server when join includes like predicate

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

Environment

Drill 1.10
SQL Server 2016

• Assignee: Unassigned
• Reporter: N Campbell
• Created: 01/Jul/17 13:33
• Updated: 02/Jul/17 12:27

Description

plugin defined to SQL Server 2016 via JDBC

Following statement sent to Drill which causes Drill to generate an invalid statement.

select TJOIN1.RNUM, TJOIN1.C1, TJOIN1.C2, TJOIN2.C2 as C2J2 from certsqlserver.dbo.TJOIN1 inner join certsqlserver.dbo.TJOIN2 on ( TJOIN1.C1 = TJOIN2.C1 and TJOIN2.C2 like 'A%' )

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

SELECT "C1", "C2", "C2" LIKE 'A%' AS "$f3"

DDL for SQL Server

create table "TJOIN1" ("RNUM" integer not null , "C1" integer, "C2" integer);
go

grant select on "TJOIN1" to public;
go

insert into "TJOIN1" ("RNUM", "C1", "C2") values ( 0, 10, 15);
go

insert into "TJOIN1" ("RNUM", "C1", "C2") values ( 1, 20, 25);
go

insert into "TJOIN1" ("RNUM", "C1", "C2") values ( 2, NULL, 50);
go

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
FROM "dbo"."TJOIN2"