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

Incorrect column values with outer joins and derived tables [CORE1246] #1670

Closed
firebird-automations opened this issue May 6, 2007 · 20 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is duplicated by CORE1808
Is duplicated by CORE2537
Block progress on CORE3354
Is related to QA327
Relate to CORE4083

Metadata:
CREATE TABLE T1 (N INTEGER);
CREATE TABLE T2 (N INTEGER);

Data:
insert into t1 values (1);
insert into t1 values (2);
insert into t2 values (2);

This query:
select *
from (select 1 n from rdb$database) t1
full join (select 2 n from rdb$database) t2
on (t2.n = t1.n)
;

Should return:
N N
============ ============
<null> 2
1 <null>

And not:
N N
============ ============
1 2
1 2

Commits: f32516e 53f18ba 774635c b550749 f8c2d59 595a185 3d246af d27a1bd

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 2.1 Beta 1 [ 10141 ]

Fix Version: 2.1 Beta 1 [ 10141 ] =>

assignee: Adriano dos Santos Fernandes [ asfernandes ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Fix rolled-back.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 1 [ 10141 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 3.0.0 [ 10048 ]

Fix Version: 2.1 Beta 1 [ 10141 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12030 ] => Firebird [ 15523 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE1808 [ CORE1808 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Jiri, this problem seems not exactly the one you sent to fb-devel.
The problem using views is still open and is CORE1245.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

Fix Version: 3.0.0 [ 10048 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Various test cases:

create table t1 (n integer);
create table t2 (n integer);

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);

insert into t2 values (1);
insert into t2 values (3);
insert into t2 values (4);
insert into t2 values (5);

commit;

-- shoud be tested with and without indexes
create index t1_n on t1 (n);
create index t2_n on t2 (n);

select 1 a, b
from rdb$database t1
left join (
select 2 b
from rdb$database t2
) t2
on 1 = 1;

select 1 a, b
from rdb$database t1
left join (
select 2 b
from rdb$database t2
) t2
on 1 = 0;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
left join (
select 2 b
from rdb$database t2
) t2
on t2.b = t1.a;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
right join (
select 2 b
from rdb$database t2
) t2
on t2.b = t1.a;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
left join (
select 1 b
from rdb$database t1
union all
select 3 b
from rdb$database t1
union all
select 4 b
from rdb$database t1
union all
select 5 b
from rdb$database t1
) t2
on t2.b = t1.a;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
right join (
select 1 b
from rdb$database t1
union all
select 3 b
from rdb$database t1
union all
select 4 b
from rdb$database t1
union all
select 5 b
from rdb$database t1
) t2
on t2.b = t1.a;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
inner join (
select 1 b
from rdb$database t1
union all
select 3 b
from rdb$database t1
union all
select 4 b
from rdb$database t1
union all
select 5 b
from rdb$database t1
) t2
on t2.b = t1.a;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
full join (
select 1 b
from rdb$database t1
union all
select 3 b
from rdb$database t1
union all
select 4 b
from rdb$database t1
union all
select 5 b
from rdb$database t1
) t2
on t2.b = t1.a;

select a, b
from (
select 1 a
from rdb$database t1
union all
select 2 a
from rdb$database t1
union all
select 3 a
from rdb$database t1
) t1
cross join (
select 1 b
from rdb$database t1
union all
select 3 b
from rdb$database t1
union all
select 4 b
from rdb$database t1
union all
select 5 b
from rdb$database t1
) t2;

select *
from (
select n, 1 x
from t1
) t1
left join (
select n, 2 x
from t2
) t2
on t2.n = t1.n;

select *
from (
select n, 1 x
from t1
) t1
right join (
select n, 2 x
from t2
) t2
on t2.n = t1.n;

select *
from (
select n, 1 x
from t1
) t1
inner join (
select n, 2 x
from t2
) t2
on t2.n = t1.n;

select *
from (
select n, 1 x
from t1
) t1
full join (
select n, 2 x
from t2
) t2
on t2.n = t1.n;

select *
from (
select n, 1 x
from t1
) t1
cross join (
select n, 2 x
from t2
) t2;

create view v1 (n1, x1, n2, x2) as
select *
from (
select n, 1 x
from t1
) t1
full join (
select n, 2 x
from t2
) t2
on t2.n = t1.n;

select * from v1;

select *
from (
select n, 1 x
from t1
) t1
left join (
select n, 2 x
from t2
) t2
on t2.x = t1.x;

select *
from (
select n, 1 x
from t1
) t1
right join (
select n, 2 x
from t2
) t2
on t2.x = t1.x;

select *
from (
select n, 1 x
from t1
) t1
inner join (
select n, 2 x
from t2
) t2
on t2.x = t1.x;

select *
from (
select n, 1 x
from t1
) t1
full join (
select n, 2 x
from t2
) t2
on t2.x = t1.x;

select *
from (
select n, 1 x
from t1
) t1
cross join (
select n, 2 x
from t2
) t2;

select *
from (
select n, 1 x
from t1
group by n
) t1
full join (
select n, 2 x
from t2
group by n
) t2
on t2.n = t1.n;

select *
from (
select distinct n, 1 x
from t1
) t1
full join (
select distinct n, 2 x
from t2
) t2
on t2.n = t1.n;

select *
from (
with z as (
select n, 1 x
from t1
)
select * from z
) t1
full join (
with z as (
select n, 2 x
from t2
)
select * from z
) t2
on t2.n = t1.n;

select *
from (
select t1.n, t1.x
from (
select n, 1 x
from t1
) t1
full join (
select n, 2 x
from t2
) t2
on t2.x = t1.x
) t1
full join (
select t2.n, t2.x
from (
select n, 1 x
from t1
) t1
full join (
select n, 2 x
from t2
) t2
on t2.x = t1.x
) t2
on t2.n = t1.n;

select *
from t1
left join (
select t3.n, 1 x
from t2
left join t1
on t2.n = t1.n
right join t2 t3
on t3.n = t2.n
) t2
on t2.n = t1.n;

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE2537 [ CORE2537 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue block progress on CORE3354 [ CORE3354 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA327 [ QA327 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE4083 [ CORE4083 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

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