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

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

N Campbell created DRILL-5648:
---------------------------------

             Summary: Drill pushes invalid SQL to SQL Server when join includes like predicate
                 Key: DRILL-5648
                 URL: https://issues.apache.org/jira/browse/DRILL-5648
             Project: Apache Drill
          Issue Type: Bug
          Components:  Server
    Affects Versions: 1.10.0
         Environment: Drill 1.10
SQL Server 2016
            Reporter: N Campbell


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"



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