Re: AW: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

['Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]]

Hi,

Thank you for providing a script, this is much easier comparing.
I can now reproduce the error you get.


Please enter a ticket with this script into the tracker, so they can fix this 
issue.

Enter these both queries, so they can quickly find the bug:

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)))


Kind Regards,
Arno


 

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

['Checkmail' check_m...@satron.de [firebird-support]]

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