Issue Details (XML | Word | Printable)

Key: CORE-1533
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Thomas Beckmann
Votes: 0
Watchers: 2
Operations

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

JOIN on first record of ordered derived table returns wrong record

Created: 24/Oct/07 01:59 AM   Updated: 03/Apr/08 10:01 AM
Component/s: Engine
Affects Version/s: 2.0.3
Fix Version/s: 2.1 RC1, 2.0.4

Time Tracking:
Not Specified

Environment: Win XP Suse Linux 10
Issue Links:
Relate
 


 Description  « Hide
The follwoing statement (which is a very simplified and reduced example of the problem)

  select
    X2.GID, x1.GID
  from X as X2
  left join (select first 1 X.GID from X order by X.DAT) X1 on X1.GID=X2.GID;

returns the output

 GID GID1
  DFB08A0CCFCF-6F99-119D-73BE-14A88A83 DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4
  DFB08A0CCFCF-6F99-119D-73BE-148A550E DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4
  DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4 DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4
  B59C7CF21100-D3F9-119D-2FF0-9BCCF8EE DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4

and I would expect it to return

  GID GID1
  DFB08A0CCFCF-6F99-119D-73BE-14A88A83 NULL
  DFB08A0CCFCF-6F99-119D-73BE-148A550E DFB08A0CCFCF-6F99-119D-73BE-148A550E
  DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4 NULL
  B59C7CF21100-D3F9-119D-2FF0-9BCCF8EE NULL

as it does, if there is no index on the order by clause in the derived table.

Here's a short script to generate table X and data for table X:

-------------------------------

set SQL DIALECT 3;
set names ISO8859_1;

create domain D_RGUID as char(36) character set NONE collate NONE;
create domain D_DATE as date;

create table X (
    GID D_RGUID,
    DAT D_DATE
);

commit;

create index IDX_X on X (DAT);

commit;


insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16');
insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-148A550E', '2004-11-16');
insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-34B0B0E4', '2007-01-01');
insert into X (GID, DAT) values ('B59C7CF21100-D3F9-119D-2FF0-9BCCF8EE', '2005-07-11');

exit;

-----------

Maybe I missed something, but this looks like a bug to me...



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Frank Schlottmann-Goedde added a comment - 24/Oct/07 09:09 AM
A minimalistic testcase for this issue would be:



create database 'derived_bug.fdb';
create table X (
    ID integer,
    DAT DATE
);

insert into X (ID, DAT) values (1, '2006-05-16');
insert into X (ID, DAT) values (2, '2004-11-16');
insert into X (ID, DAT) values (3, '2007-01-01');
insert into X (ID, DAT) values (4, '2005-07-11');
commit;

/*without matching index on x.dat the result set is as expected: */
select X2.ID, x1.ID,x2.dat from X as X2 left join (select first 1 X.ID from X order by X.DAT) X1 on X1.ID=X2.ID;

/*now let's create an index on x.dat */
create index IDX_X on X (DAT);
commit;

/*wrong result set if there exists an index on x.dat */
select X2.ID, x1.ID,x2.dat from X as X2 left join (select first 1 X.ID from X order by X.DAT) X1 on X1.ID=X2.ID;

/* if there is no matching index on dat, the result again is as expected: */
select X2.ID, x1.ID,x2.dat from X as X2 left join (select first 1 X.ID from X order by X.DAT desc) X1 on X1.ID=X2.ID;

drop database;
commit;



Philippe Makowski added a comment - 03/Apr/08 10:01 AM
Q/A test ok and qmtest made