Submitted by: Olaf Kluge (okl)

Duplicates CORE5150
Is duplicated by CORE5150
Relate to CORE5351

Hello,

since FB3 I get wrong results. Here are a simple example:

SET SQL DIALECT 3;

CREATE TABLE T_MAIN (
TNR VARCHAR(16) NOT NULL,
MINB INTEGER
);

ALTER TABLE T_MAIN ADD CONSTRAINT PK_T_MAIN PRIMARY KEY (TNR);

SET SQL DIALECT 3;

CREATE TABLE T_STOCK (
TNR VARCHAR(16),
AMOUNT INTEGER
);

ALTER TABLE T_STOCK ADD CONSTRAINT FK_T_STOCK_1 FOREIGN KEY (TNR) REFERENCES T_MAIN (TNR) ON DELETE CASCADE ON UPDATE CASCADE;

Redords:

INSERT INTO T_MAIN (TNR, MINB)
VALUES ('AAA', 0);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('BBB', 10);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('CCC', 10);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('DDD', 10);

COMMIT WORK;

INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('AAA', 100);
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('BBB', 5);
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('CCC', 15);

COMMIT WORK;

The sql query

SELECT
a.tnr, a.minb, coalesce(b.amount,0) as m
FROM
t_main a
left join t_stock b on (a.tnr = b.tnr)
WHERE
((a.minb > coalesce(b.amount,0)))
ORDER BY
a.tnr

_________________________________-

I get ccc too. Why? Allthough I have a stock from 15.

If I delete the foreign key from table t_stock, CCC does not display! But with this foreign key it does not work, I get CCC but CCC has a stock greater the minimum amount (minb) and should not be displayed (like in firebird 2.5, 2.1, 2.0)

The query with the wrong results:

SELECT
a.tnr, a.minb, coalesce(b.amount,0) as m
FROM
t_main a
left join t_stock b on (a.tnr = b.tnr)
WHERE
((a.minb > coalesce(b.amount,0)))

A workaround by ignoring use of index:

SELECT
a.tnr, a.minb, coalesce(b.amount,0) as m
FROM
t_main a
left join t_stock b on (a.tnr = b.tnr || '')
WHERE
((a.minb > coalesce(b.amount,0)))

Commits: 1eb9ab0 7a6399f FirebirdSQL/fbt-repository@ebf61a2 FirebirdSQL/fbt-repository@a8940bc