Skip to content
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

Closed
firebird-automations opened this issue Jul 30, 2013 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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)
returns (
aARG as integer,
aLEVEL as integer,
aID as 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 (http://show.id = 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 (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,
Alvaro Castiello de la Hidalga

=>

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)
returns (
aARG as integer,
aLEVEL as integer,
aID as 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 (http://show.id = 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 (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,
Alvaro Castiello de la Hidalga

environment: Windows 2008 server foundation => Windows 2008 server foundation x64 with Firebird-2.5.2.26540_0_Win32.exe installed

@firebird-automations
Copy link
Collaborator Author

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)
returns (
aARG as integer,
aLEVEL as integer,
aID as 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 (http://show.id = 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 (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,
Alvaro Castiello de la Hidalga

=>

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

@firebird-automations
Copy link
Collaborator Author

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

=>

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

The test is not reproducible. Surely we need reproducible tests!

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Case will be re-opened once reproduced details are provided.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Cannot Reproduce [ 5 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

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
checked the bug several times but neither of us made a backup copy of the database. We´ll try to reconstruct it and if we are able to will attach it to this Tracker

Regards

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant