Issue Details (XML | Word | Printable)

Key: CORE-1246
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Incorrect column values with outer joins and derived tables

Created: 06/May/07 04:46 PM   Updated: 05/May/13 10:57 PM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.1 Initial, 2.1 Alpha 1, 2.1 Beta 1, 2.0.2
Fix Version/s: 2.5 Beta 1

Time Tracking:
Not Specified

Issue Links:
Depend
 
Duplicate
 
Relate


 Description  « Hide
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

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
[ Permalink | « Hide ]
Adriano dos Santos Fernandes added a comment - 18/May/07 11:31 AM
Fix rolled-back.

[ Permalink | « Hide ]
Adriano dos Santos Fernandes added a comment - 07/Sep/08 04:36 PM
Jiri, this problem seems not exactly the one you sent to fb-devel.
The problem using views is still open and is CORE-1245.

[ Permalink | « Hide ]
Adriano dos Santos Fernandes added a comment - 07/Sep/08 04:37 PM
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;

[ Permalink | « Hide ]
Pavel Cisar added a comment - 20/Apr/11 09:40 AM
QA test added.


Powered by a free Atlassian JIRA open source license for Firebird (RDBMS). Try JIRA - bug tracking software for your team.
Atlassian JIRA the Professional Issue Tracker. (Enterprise Edition, Version: 3.13.5-#360) - Bug/feature request - Atlassian news - Contact Administrators