Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Regression: LEFT JOIN incorrectly pushes COALESCE into the inner stream causing wrong results [CORE5149] #5432

Closed
firebird-issue-importer opened this issue Mar 11, 2016 · 8 comments

Comments

@firebird-issue-importer

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: @dyemanov

Version: 3.0 RC1 [ 10584 ]

Version: 3.0 Beta 2 [ 10586 ]

Version: 3.0 Beta 1 [ 10332 ]

Version: 3.0 Alpha 2 [ 10560 ]

Version: 3.0 Alpha 1 [ 10331 ]

summary: coalesce bug by using index => Regression: LEFT JOIN incorrectly pushes COALESCE into the inner stream causing wrong results

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.0 [ 10740 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: Sean Leyne (seanleyne)

Link: This issue duplicates CORE5150 [ CORE5150 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 11, 2016

Modified by: @dyemanov

Link: This issue is duplicated by CORE5150 [ CORE5150 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 19, 2016

Modified by: @dyemanov

Link: This issue relate to CORE5351 [ CORE5351 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment