Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-5650

Drill pushed invalid SQL to SQL Server when NOT IN predicate used

    XMLWordPrintableJSON

    Details

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

      Drill 1.10
      SQL Server 2016

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              the6campbells N Campbell
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: