Re: AW: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
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?
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
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. Thanks Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 9. März 2016 09:04 An: firebird-support@yahoogroups.com Betreff: Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2? Hello, > sorry, outlook corrects me in German. At the moment, I have no sulution > for the problem. Anything ideas? Query: SELECT a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ FROM tteile a left join tlager_sum b on (a.teilenr = b.teilenr) WHERE ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) ORDER BY a.teilenr Result: TEILENR BEZEICHNUNG MINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm 280 420 1 I would say your TLAGER_SUM contains a record with: TEILENR MENGE PrSt110x44x3 420 So results are correct? If not give sample records for tteile and tlager_sum with DDL Kind Regards, Arno Brinkman ABVisie
Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
On 2016-03-09 7:57, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > Hello again, > > sorry, outlook corrects me in German. At the moment, I have no > sulution for the problem. Anything ideas? I think you should create a sample database to demonstrate this problem, and create a ticket in the tracker with all the details. Mark
AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hello again, sorry, outlook corrects me in German. At the moment, I have no sulution for the problem. Anything ideas? Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 8. März 2016 16:39 An: firebird-support@yahoogroups.com Betreff: AW: [firebird-support] coalesce bug in fb 3.0 rc2? Hell Mark, I would get all material, where the minimum inventory amount iss maller the the inventory amount. Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum amount it should be. Now, the simplified query gets all records where the amount is to small and with the „where a.minb > coalesce(b.menge)) I get also the records, where is no inventory amount present. If I let the code: select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) where ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) order by a.teilenr I get m = 0 for records, who has a inventory amount greater than the minimal inventory amount. If I change the where ((a.minb > coalesce(b.menge,0)) to where ((a.minb > b.menge).. the records who has a greater inventory amount than the minimal one will be not displayed, but also not these who has no inventory amount (null in tlager_sum) In firebird 2.5 and earlier versions it works fine in both situations. The result of the unchanged code: TEILENRBEZEICHNUNGMINBMTYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 0 1 Inventory amount = 0 The result without coalesce: The record is no present Without the condition >.. TEILENRBEZEICHNUNGMINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 420 1 I get 420 as inventory amount, 280 should it be as minimal inventory amount, aka minb What can be the problem? Von: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 8. März 2016 16:26 An: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2? On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de <mailto:check_m...@satron.de> [firebird-support] wrote: > Hello, > > in the further version of firebird I can execute the following and > get > the real value of m (exists in tlager_sum) > > select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, > a.typ > > from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) > > where ((a.minb > coalesce(b.menge,0)) > > and (a.typ = 1)) order by a.teilenr > > In any case when it is no amount in the table tlager_sum (null), it > should seen as 0 (not present) and if the minimum amount iss higher.. > > But, since fb 3 I get 0, why? The tlager_sum has for many records a > mass. If I change to > > a.minb > b.menge > > it works for these records, but not for any with no amount. > > What can be the issue? It is not clear to me what your problem is. Could you describe it in more detail and provide a sample dataset, expected results and actual results? Mark
AW: [firebird-support] coalesce bug in fb 3.0 rc2?
Hell Mark, I would get all material, where the minimum inventory amount iss maller the the inventory amount. Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum amount it should be. Now, the simplified query gets all records where the amount is to small and with the „where a.minb > coalesce(b.menge)) I get also the records, where is no inventory amount present. If I let the code: select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) where ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) order by a.teilenr I get m = 0 for records, who has a inventory amount greater than the minimal inventory amount. If I change the where ((a.minb > coalesce(b.menge,0)) to where ((a.minb > b.menge).. the records who has a greater inventory amount than the minimal one will be not displayed, but also not these who has no inventory amount (null in tlager_sum) In firebird 2.5 and earlier versions it works fine in both situations. The result of the unchanged code: TEILENRBEZEICHNUNGMINBMTYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 0 1 Inventory amount = 0 The result without coalesce: The record is no present Without the condition >.. TEILENRBEZEICHNUNGMINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm280 420 1 I get 420 as inventory amount, 280 should it be as minimal inventory amount, aka minb What can be the problem? Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 8. März 2016 16:26 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2? On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de <mailto:check_m...@satron.de> [firebird-support] wrote: > Hello, > > in the further version of firebird I can execute the following and > get > the real value of m (exists in tlager_sum) > > select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, > a.typ > > from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) > > where ((a.minb > coalesce(b.menge,0)) > > and (a.typ = 1)) order by a.teilenr > > In any case when it is no amount in the table tlager_sum (null), it > should seen as 0 (not present) and if the minimum amount iss higher.. > > But, since fb 3 I get 0, why? The tlager_sum has for many records a > mass. If I change to > > a.minb > b.menge > > it works for these records, but not for any with no amount. > > What can be the issue? It is not clear to me what your problem is. Could you describe it in more detail and provide a sample dataset, expected results and actual results? Mark
Re: [firebird-support] coalesce bug in fb 3.0 rc2?
On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > Hello, > > in the further version of firebird I can execute the following and > get > the real value of m (exists in tlager_sum) > > select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, > a.typ > > from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) > > where ((a.minb > coalesce(b.menge,0)) > > and (a.typ = 1)) order by a.teilenr > > In any case when it is no amount in the table tlager_sum (null), it > should seen as 0 (not present) and if the minimum amount iss higher.. > > But, since fb 3 I get 0, why? The tlager_sum has for many records a > mass. If I change to > > a.minb > b.menge > > it works for these records, but not for any with no amount. > > What can be the issue? It is not clear to me what your problem is. Could you describe it in more detail and provide a sample dataset, expected results and actual results? Mark
[firebird-support] coalesce bug in fb 3.0 rc2?
Hello, in the further version of firebird I can execute the following and get the real value of m (exists in tlager_sum) select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ from tteile a left join tlager_sum b on (a.teilenr = b.teilenr) where ((a.minb > coalesce(b.menge,0)) and (a.typ = 1)) order by a.teilenr In any case when it is no amount in the table tlager_sum (null), it should seen as 0 (not present) and if the minimum amount iss higher.. But, since fb 3 I get 0, why? The tlager_sum has for many records a mass. If I change to a.minb > b.menge it works for these records, but not for any with no amount. What can be the issue? Thanks Best regards Olaf
