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

JOIN on first record of ordered derived table returns wrong record [CORE1533] #1950

Closed
firebird-automations opened this issue Oct 24, 2007 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Thomas Beckmann (thbeckmann)

Is related to QA163

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

Commits: 4d7d563 10e2634

@firebird-automations
Copy link
Collaborator Author

Modified by: Thomas Beckmann (thbeckmann)

description: 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 you leave out 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;

commit;

create table X (
GID D_RGUID,
DAT D_DATE
);

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

=>

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 descending index IDX_X_2 on X (DAT);

commit;

create table X (
GID D_RGUID,
DAT D_DATE
);

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Thomas Beckmann (thbeckmann)

description: 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 descending index IDX_X_2 on X (DAT);

commit;

create table X (
GID D_RGUID,
DAT D_DATE
);

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

=>

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 index IDX_X on X (DAT);

commit;

create table X (
GID D_RGUID,
DAT D_DATE
);

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Frank Schlottmann-Goedde (fsg)

description: 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 index IDX_X on X (DAT);

commit;

create table X (
GID D_RGUID,
DAT D_DATE
);

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

=>

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

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 http://X2.ID, http://x1.ID,x2.dat from X as X2 left join (select first 1 http://X.ID from X order by X.DAT) X1 on http://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 http://X2.ID, http://x1.ID,x2.dat from X as X2 left join (select first 1 http://X.ID from X order by X.DAT) X1 on http://X1.ID=X2.ID;

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

drop database;
commit;

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.1 RC1 [ 10201 ]

Fix Version: 2.0.4 [ 10211 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Minor [ 4 ] => Major [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA163 [ QA163 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13337 ] => Firebird [ 14011 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A test ok and qmtest made

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@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
Projects
None yet
Development

No branches or pull requests

2 participants