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-issue-importer opened this issue Oct 24, 2007 · 13 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 24, 2007

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 26, 2007

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 29, 2007

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.1 RC1 [ 10201 ]

Fix Version: 2.0.4 [ 10211 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 29, 2007

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 29, 2007

Modified by: @pcisar

Link: This issue is related to QA163 [ QA163 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 3, 2008

Commented by: @pmakowski

Q/A test ok and qmtest made

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 3, 2008

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants