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
LEFT join on stored procedure behaves erratically (maybe also RIGHT ?) [CORE4150] #4477
Comments
Modified by: Alvaro Castiello (acastiello)description: I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply: create procedure show(pARG as integer) aID = 1; aID = 2; aID = null; now, a select * from show(17) is called and it returns now, if a table is created create table names ( data inserted: a select from the SP joining with this table then the null row of the SP is not included since it has no matching id in names but select * from show(17) LEFT join names on (http://show.id = http://names.id); sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour If the database is needed I can send it Best regards, => I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply: create procedure show(pARG as integer) aID = 1; aID = 2; aID = null; now, a select * from show(17) is called and it returns now, if a table is created create table names ( data inserted: a select from the SP joining with this table then the null row of the SP is not included since it has no matching id in names but select * from show(17) LEFT join names on (http://show.id = http://names.id); sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour If the database is needed I can send it or allow connection over the net. Win32 versions was installed since many of our customers still have 32bits windows editons Best regards, environment: Windows 2008 server foundation => Windows 2008 server foundation x64 with Firebird-2.5.2.26540_0_Win32.exe installed |
Modified by: Alvaro Castiello (acastiello)description: I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply: create procedure show(pARG as integer) aID = 1; aID = 2; aID = null; now, a select * from show(17) is called and it returns now, if a table is created create table names ( data inserted: a select from the SP joining with this table then the null row of the SP is not included since it has no matching id in names but select * from show(17) LEFT join names on (http://show.id = http://names.id); sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour If the database is needed I can send it or allow connection over the net. Win32 versions was installed since many of our customers still have 32bits windows editons Best regards, => I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply: create procedure show(pARG integer) aID = 1; aID = 2; aID = null; now, a select * from show(17) is called and it returns now, if a table is created create table names ( data inserted: a select from the SP joining with this table then the null row of the SP is not included since it has no matching id in names but select * from show(17) LEFT join names on (show.aid = http://names.id); sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. If tried, this simple example runs fine but in my real database I pick the show.aid AND-compared with other data (which should bring the orphan row) and the row is vanished I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour If the database is needed I can send it or allow connection over the net. Win32 versions was installed since many of our customers still have 32bits windows editons Best regards, |
Modified by: Alvaro Castiello (acastiello)description: I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply: create procedure show(pARG integer) aID = 1; aID = 2; aID = null; now, a select * from show(17) is called and it returns now, if a table is created create table names ( data inserted: a select from the SP joining with this table then the null row of the SP is not included since it has no matching id in names but select * from show(17) LEFT join names on (show.aid = http://names.id); sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. If tried, this simple example runs fine but in my real database I pick the show.aid AND-compared with other data (which should bring the orphan row) and the row is vanished I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour If the database is needed I can send it or allow connection over the net. Win32 versions was installed since many of our customers still have 32bits windows editons Best regards, => I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply: create procedure show(pARG integer) aID = 1; aID = 2; aID = null; now, a select * from show(17) is called and it returns now, if a table is created create table names ( data inserted: a select from the SP joining with this table then the null row of the SP is not included since it has no matching id in names but select * from show(17) LEFT join names on (show.aid = http://names.id); sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. If tried, this simple example runs fine but in my real database I pick the show.aid AND-compared with other data (which should bring the orphan row) and the row is vanished I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour If the database is needed I can send it or allow connection over the net. Win32 versions was installed since many of our customers still have 32bits windows editons Best regards, P.D. after digging deeper I found that the bug raises due to the fact that the join condition has an AND,. My actual SP returns foru values. Two irrelevant and two values anded match another table. When one of the IDs is null (and would cause an orphan row) it is excluded even with the LEFT clause |
Commented by: @asfernandes The test is not reproducible. Surely we need reproducible tests! |
Commented by: Sean Leyne (seanleyne) Case will be re-opened once reproduced details are provided. |
Modified by: Sean Leyne (seanleyne)status: Open [ 1 ] => Resolved [ 5 ] resolution: Cannot Reproduce [ 5 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: Alvaro Castiello (acastiello) Something changed in the database structure (added a table) and now the bug does not show anymore and unfortunately a coworker and I Regards |
Submitted by: Alvaro Castiello (acastiello)
I have a SP that takes one argument and returns three (one being the first argument itself, the second an irrelevant "level" and the third, the most important one, an ID of a table). The SP could be simply:
create procedure show(pARG integer)
returns (
aARG integer,
aLEVEL integer,
aID integer)
as
begin
aARG = :pARG;
aLEVEL = 0;
aID = 1;
suspend;
aID = 2;
suspend;
aID = null;
suspend;
end;
now, a select * from show(17) is called and it returns
17 0 1
17 0 2
17 0 null
now, if a table is created
create table names (
id integer not null primary key,
name varchar(80) not null
);
data inserted:
insert into names values (1,'me');
insert into names values (2,'you');
a select from the SP joining with this table
select * from show(17) join names on (show.aid = http://names.id);
then the null row of the SP is not included since it has no matching id in names
but
select * from show(17) LEFT join names on (show.aid = http://names.id);
sholud indeed include it but does not. The row is left...ed out! as if no LEFT clause had been included. If tried, this simple example runs fine but in my real database I pick the show.aid AND-compared with other data (which should bring the orphan row) and the row is vanished
I have not tested it with RIGHT (have no data to try) but maybe will have the same behaviour
If the database is needed I can send it or allow connection over the net.
Win32 versions was installed since many of our customers still have 32bits windows editons
Best regards,
Alvaro Castiello de la Hidalga
P.D. after digging deeper I found that the bug raises due to the fact that the join condition has an AND,. My actual SP returns foru values. Two irrelevant and two values anded match another table. When one of the IDs is null (and would cause an orphan row) it is excluded even with the LEFT clause
The text was updated successfully, but these errors were encountered: