dynamobi/dynamobi — Issue #648: [FRG-224] NOT IN predicate evaluation produces wrong result if IN list contains null value
Author: dynamobi-build
Created: 2011-12-30 03:40:42+00:00
State: open
Labels: FRG, Sql2Rel

[reporter="rchen", created="Thu, 19 Oct 2006 15:34:05 -0500 (GMT-05:00)"]
set schema 'sales';

alter system set "calcVirtualMachine" = 'CALCVM_JAVA';

alter session implementation set jar sys_boot.sys_boot.luciddb_plugin;

create table emps1 (empno int);

insert into emps1 values (10);

select empno from emps where empno not in (10, null);

will not return correct result due to the three value logic required is not possible if IN/NOT IN is translated to logical operations directly. Using Join will work. Though there is a bug when creating ValueRel from a list containing null values.

Another related problem is the time(>25 sec) to optimize something like this:

explain plan for

select * from emps1

where empno not in (20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20);