coalesce bug by using index
----------------------------
Key: CORE-5149
URL: http://tracker.firebirdsql.org/browse/CORE-5149
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 RC2
Environment: Windows 7 X64, Firebird 3 RC2 32 Bit
Reporter: Olaf Kluge
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)))
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785111&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
