This works with index (additional "and (not tb2.date_maj is null)
" in where...) :
select that returns wrong results :
select tb1.id1
from tb1
where
tb1.id1 not in
(select tb2.id1 from tb2
inner join tb3 on tb2.id2 = tb3.id2
where
tb2.date_maj >= '03/03/2005'
and tb2.date_maj <= '03/03/2005'
and (not tb2.date_maj is null)
)
OL

--- In firebird-support@yahoogroups.com, "olivier_lucaes"
<olivier.lucaes@l...> wrote:

>
> Hello there,
> I not sure if this is really a bug or a logic error from my side.

If

> anybody could reproduce...
> Here is the case:
> i have 3 tables (tb1, tb2, tb3)
> see definition, data and select that returns wrong result
> here below.
>
> Briefly, the records that contain a field with a null value
> that is checked in a "where" are excluded inside an
> INNER JOIN when the table involved in the join has an
> index...
>
> This bug happens only when an index is defined for TB3.
> I was not able to reproduce it until the time i have
> created the index like in my application. If index is set

inactive,

> the select returns all records...
>
> I'm running Firebird 1.5.2 on a Win XP sp2.
>
> SET SQL DIALECT 3;
>
> CREATE DATABASE 'C:\BUG.FDB'
> USER 'SYSDBA' PASSWORD 'masterkey'
> PAGE_SIZE 1024;
>
> /* Tables definitions */
>
> CREATE TABLE TB1 (
> ID1 INTEGER NOT NULL);
>
> CREATE TABLE TB2 (
> ID1 INTEGER NOT NULL,
> ID2 INTEGER NOT NULL,
> DATE_ADD DATE,
> DATE_MAJ DATE);
>
> CREATE TABLE TB3 (
> ID2 INTEGER NOT NULL,
> TXT CHAR (1) CHARACTER SET NONE NOT NULL
> COLLATE NONE);
>
> INSERT INTO TB1 (ID1) VALUES (1);
> INSERT INTO TB1 (ID1) VALUES (2);
> INSERT INTO TB1 (ID1) VALUES (3);
> INSERT INTO TB1 (ID1) VALUES (4);
> INSERT INTO TB1 (ID1) VALUES (5);
>
> COMMIT WORK;
>
> INSERT INTO TB2 (ID1, ID2, DATE_ADD, DATE_MAJ)
> VALUES (1, 1, '02/01/2005', '02/03/2005');
> INSERT INTO TB2 (ID1, ID2, DATE_ADD, DATE_MAJ)
> VALUES (5, 2, '02/01/2005', NULL);
> INSERT INTO TB2 (ID1, ID2, DATE_ADD, DATE_MAJ)
> VALUES (4, 3, '02/01/2005', '02/03/2005');
> INSERT INTO TB2 (ID1, ID2, DATE_ADD, DATE_MAJ)
> VALUES (2, 4, '02/01/2005', '02/03/2005');
> INSERT INTO TB2 (ID1, ID2, DATE_ADD, DATE_MAJ)
> VALUES (1, 5, '02/01/2005', NULL);
>
> COMMIT WORK;
>
> INSERT INTO TB3 (ID2, TXT) VALUES (1, 'A');
> INSERT INTO TB3 (ID2, TXT) VALUES (1, 'B');
> INSERT INTO TB3 (ID2, TXT) VALUES (2, 'C');
> INSERT INTO TB3 (ID2, TXT) VALUES (2, 'D');
> INSERT INTO TB3 (ID2, TXT) VALUES (3, 'E');
> INSERT INTO TB3 (ID2, TXT) VALUES (4, 'F');
>
> COMMIT WORK;
>
>
>
> /* Primary keys definition */
>
> ALTER TABLE TB1 ADD CONSTRAINT PK_TB1 PRIMARY
> KEY (ID1);
>
>
> /* Indices definition */
>
> CREATE UNIQUE INDEX IDX_TB2 ON TB2 (ID1, ID2);
> /* without this index it works fine ! */
> CREATE UNIQUE INDEX IDX_TB3 ON TB3 (ID2, TXT);
>
> CREATE UNIQUE INDEX PK_TB1 ON TB1 (ID1);
>
>
> select used as a sub select :
> (in original select there was some tb3 fields tested but
> i have reduced the select to its simpliest just to keep the bug
> active)
> select tb2.id1 from tb2
> inner join tb3 on tb2.id2 = tb3.id2
> where
> tb2.date_maj >= '03/03/2005'
> and tb2.date_maj <= '03/03/2005'
> ==> returns 'NULL', that's ok
>
> select that returns wrong results :
> select tb1.id1
> from tb1
> where
> tb1.id1 not in
> (select tb2.id1 from tb2
> inner join tb3 on tb2.id2 = tb3.id2
> where
> tb2.date_maj >= '03/03/2005'
> and tb2.date_maj <= '03/03/2005'
> )
> returns :
> 2
> 3
> 4
> plans :
> PLAN JOIN (TB2 INDEX (IDX_TB2),TB3 INDEX (IDX_TB3))
> PLAN (TB1 NATURAL):
> PLAN JOIN (TB2 INDEX (IDX_TB2),TB3 INDEX (IDX_TB3))
> PLAN (TB1 NATURAL)
>
> Adapted plan:
> PLAN JOIN (TB2 INDEX (IDX_TB2),TB3 INDEX (IDX_TB3))
> PLAN (TB1 NATURAL)
>
>
>
> when no index is defined for TB3, the same select
> returns :
> 1
> 2
> 3
> 4
> 5
> plans when tb3 index is inactive :
> PLAN JOIN (TB3 NATURAL,TB2 INDEX (IDX_TB2))
> PLAN (TB1 NATURAL):
> PLAN JOIN (TB3 NATURAL,TB2 INDEX (IDX_TB2))
> PLAN (TB1 NATURAL)
>
> Adapted plan:
> PLAN JOIN (TB3 NATURAL,TB2 INDEX (IDX_TB2))
> PLAN (TB1 NATURAL)
>
> Is this a bug ?
>
> Thanks in advance
>
> Olivier