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
Comments
Modified by: Thomas Beckmann (thbeckmann)description: The follwoing statement (which is a very simplified and reduced example of the problem) select returns the output GID GID1 and I would expect it to return GID GID1 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; create domain D_RGUID as char(36) character set NONE collate NONE; commit; create table X ( commit; insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16'); 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 returns the output GID GID1 and I would expect it to return GID GID1 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; create domain D_RGUID as char(36) character set NONE collate NONE; create descending index IDX_X_2 on X (DAT); commit; create table X ( commit; insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16'); exit; ----------- Maybe I missed something, but this looks like a bug to me... |
Modified by: Thomas Beckmann (thbeckmann)description: The follwoing statement (which is a very simplified and reduced example of the problem) select returns the output GID GID1 and I would expect it to return GID GID1 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; create domain D_RGUID as char(36) character set NONE collate NONE; create descending index IDX_X_2 on X (DAT); commit; create table X ( commit; insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16'); 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 returns the output GID GID1 and I would expect it to return GID GID1 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; create domain D_RGUID as char(36) character set NONE collate NONE; create index IDX_X on X (DAT); commit; create table X ( commit; insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16'); exit; ----------- Maybe I missed something, but this looks like a bug to me... |
Modified by: Frank Schlottmann-Goedde (fsg)description: The follwoing statement (which is a very simplified and reduced example of the problem) select returns the output GID GID1 and I would expect it to return GID GID1 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; create domain D_RGUID as char(36) character set NONE collate NONE; create index IDX_X on X (DAT); commit; create table X ( commit; insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16'); 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 returns the output GID GID1 and I would expect it to return GID GID1 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; create domain D_RGUID as char(36) character set NONE collate NONE; create table X ( commit; create index IDX_X on X (DAT); commit; insert into X (GID, DAT) values ('DFB08A0CCFCF-6F99-119D-73BE-14A88A83', '2006-05-16'); exit; ----------- Maybe I missed something, but this looks like a bug to me... |
Commented by: Frank Schlottmann-Goedde (fsg) A minimalistic testcase for this issue would be: create database 'derived_bug.fdb'; insert into X (ID, DAT) values (1, '2006-05-16'); /*without matching index on x.dat the result set is as expected: */ /*now let's create an index on x.dat */ /*wrong result set if there exists an index on x.dat */ /* if there is no matching index on dat, the result again is as expected: */ drop database; |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1 RC1 [ 10201 ] Fix Version: 2.0.4 [ 10211 ] |
Modified by: @dyemanovpriority: Minor [ 4 ] => Major [ 3 ] |
Modified by: @pcisarWorkflow: jira [ 13337 ] => Firebird [ 14011 ] |
Commented by: @pmakowski Q/A test ok and qmtest made |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovQA Status: No test => Done successfully |
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
The text was updated successfully, but these errors were encountered: