Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3674

NOT IN construct behaves inconsistently

    Details

      Description

      As discussed in https://developer.jboss.org/message/939309#939309, an inconsistent behavior of the NOT IN clause occurs when NULL values are returned by the subquery.

      For example, let us suppose to have a table defined as:

      CREATE TABLE test1 (col1 character varying(10));
       
      insert into test1 values ('a');  
      insert into test1 values ('b');  
      insert into test1 values ('c');  
      insert into test1 values ('d'); 
      

      If I enumerate values in the NOT IN clause and one of them is a NULL value:

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

      the result is not empty (as it would be expected) but 'c' and 'd' are returned.

      Looking at the query plan, the NULL is wrongly removed from the list:

      ----------------------------------------------------------------------------  
      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')  
        
        
      ============================================================================  
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                fox123 Salvatore R
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: