3 Replies Latest reply on Sep 4, 2015 10:36 AM by shawkins

    NOT IN construct behaves inconsistently

    fox123

      Hi everybody,

       

      I am experiencing an inconsistent behavior of the NOT IN clause when NULL values are returned by the subquery.

       

      For example, I have the following two tables defined in a PostgreSQL datasource:

      CREATE TABLE test1
      (
        col1 character varying(10)
      );
      
      CREATE TABLE test2
      (
        col1 character varying(10)
      );
      
      

       

      containing the following rows, respectively:

       

      insert into test1 values ('a');
      insert into test1 values ('b');
      insert into test1 values ('c');
      insert into test1 values ('d');
      
      
      insert into test2 values ('a');
      insert into test2 values ('b');
      insert into test2 values (null);
      

       

      If I run this query in Teiid:

      select * from pg.test1 where col1 NOT IN (select col1 from pg.test2)
      

       

      the result is correctly empty as expected, since NULL values are returned by the subquery.

       

      But if I enumerate values in the NOT IN clause:

      select * from pg.test1 where col1 NOT IN ('a', NULL, 'b');
      

       

      the result is not empty and 'c' and 'd' are returned.

       

      It seems that in this case the NULL is wrongly removed from the list, as I can see in generated query plan:

       

      ----------------------------------------------------------------------------
      OPTIMIZATION COMPLETE:
      PROCESSOR PLAN:
      AccessNode(0) output=[pg.test1.col1] SELECT g_0.col1 FROM pg.test1 AS g_0 WHERE g_0.col1 NOT IN ('a', 'b')
      
      
      ============================================================================
      

       

      Can you please have a look at this problem? I could reproduce it in Teiid-8.12-Beta1.