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