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

[firebird-automations]

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]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

[firebird-automations]

Modified by: @asfernandes

Version: 2.1 Beta 1 [ 10141 ]

Fix Version: 2.1 Beta 1 [ 10141 ] =>

assignee: Adriano dos Santos Fernandes [ asfernandes ] =>

[firebird-automations]

Commented by: @asfernandes

Fix rolled-back.

[firebird-automations]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ] =>

[firebird-automations]

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]

Modified by: @asfernandes

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

resolution: Fixed [ 1 ] =>

[firebird-automations]

Modified by: @asfernandes

Fix Version: 3.0.0 [ 10048 ]

Fix Version: 2.1 Beta 1 [ 10141 ] =>

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @asfernandes

Link: This issue is duplicated by CORE1808 [ CORE1808 ]

[firebird-automations]

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]

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]

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]

Modified by: @asfernandes

Link: This issue is duplicated by CORE2537 [ CORE2537 ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue block progress on CORE3354 [ CORE3354 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA327 [ QA327 ]

[firebird-automations]

Commented by: @pcisar

QA test added.

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @asfernandes

Link: This issue relate to CORE4083 [ CORE4083 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully