Subject AW: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Author Checkmail

Hello,

 

I have created an 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)

 

Thanks

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Mittwoch, 9. März 2016 13:25
An: firebird-support@yahoogroups.com
Betreff: Re: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

 

 

Hello,

> your are right. But in FB 3, it will be handled different.
> I need the left join tlager_sum, because I’d like to have all records too
> there no records in tlager_sum.
> But in this case I get now in fb 3 the tteile.teilenr clean without the
> amaount of tlager_sum (the a.minb is greater than the coalesce(b.menge,0)
> and in this case, I get no referenced Record from tlager_sum), the one
> from tteile now in every case (left join).
> Finally, I would get all records with a.minb > b.menge and all records,
> where a.minb is present an there is no record from the teilenr in
> tlager_sum.

Please, give some DDL with example data.
I've tried to reproduce your issue on FB3.0 RC2, but i could not reproduce
it.

Kind Regards,
Arno Brinkman