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

Wrong result when use "where <field_C> STARTING WITH <:value> ORDER BY <field_N>' and field_C is leading part of compound index key: { field_C, field_N } [CORE4665] #1602

Closed
firebird-automations opened this issue Jan 12, 2015 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

DDL-1: ascending compound index

set bail on;
recreate table ttt (id int, unit varchar(10), y int, z int);
commit;
delete from ttt;

insert into ttt( id, unit, y, z) values (1, 'foo', 9999, 23636);
insert into ttt( id, unit, y, z) values (2, 'foo', 8888, 22520);
insert into ttt( id, unit, y, z) values (3, 'foo', 5555, 21822);
insert into ttt( id, unit, y, z) values (4, 'foo', 3333, 17682);

insert into ttt( id, unit, y, z) values (5, 'fooo', 1111, 22);
insert into ttt( id, unit, y, z) values (6, 'fooo', 111, 222);
insert into ttt( id, unit, y, z) values (7, 'fooo', 11, 2222);
insert into ttt( id, unit, y, z) values (8, 'fooo', 1, 22222);
commit;

create index ttt_unit_y on ttt( unit, y );
commit;
-----------------------------------------------------------

set width unit 10;
set plan on;
set echo on;

select id, t.unit, t.y, t.z
from ttt t
where t.unit starting with 'foo'
order by t.y;

select id, t.unit, t.y, t.z
from ttt t
where t.unit starting with 'foo'
order by t.y||'';
set echo off;
set plan off;

rollback;

Test-1:

1.1) LI-T3.0.0.31527

Output:
----------
select id, t.unit, t.y, t.z
from ttt t
where t.unit starting with 'foo'
order by t.y;

PLAN (T ORDER TTT_UNIT_Y) -- <<< WRONG! I'm asking to show records sorted by field 'Y' rather than by compound key (UNIT, Y)

      ID UNIT                  Y            Z

============ ========== ============ ============
4 foo 3333 17682
3 foo 5555 21822
2 foo 8888 22520
1 foo 9999 23636
8 fooo 1 22222
7 fooo 11 2222
6 fooo 111 222
5 fooo 1111 22

-- CHECK RIGHT RESULTS BELOW:
select id, t.unit, t.y, t.z
from ttt t
where t.unit starting with 'foo'
order by t.y||'';

PLAN SORT (T INDEX (TTT_UNIT_Y))

      ID UNIT                  Y            Z

============ ========== ============ ============
8 fooo 1 22222
7 fooo 11 2222
6 fooo 111 222
5 fooo 1111 22
4 foo 3333 17682
3 foo 5555 21822
2 foo 8888 22520
1 foo 9999 23636

1.2) LI-V2.5.4.26821

select id, t.unit, t.y, t.z
from ttt t
where t.unit starting with 'foo'
order by t.y;

PLAN SORT ((T INDEX (TTT_UNIT_Y)))

      ID UNIT                  Y            Z

============ ========== ============ ============
8 fooo 1 22222
7 fooo 11 2222
6 fooo 111 222
5 fooo 1111 22
4 foo 3333 17682
3 foo 5555 21822
2 foo 8888 22520
1 foo 9999 23636

select id, t.unit, t.y, t.z
from ttt t
where t.unit starting with 'foo'
order by t.y||'';

PLAN SORT ((T INDEX (TTT_UNIT_Y)))

      ID UNIT                  Y            Z

============ ========== ============ ============
8 fooo 1 22222
7 fooo 11 2222
6 fooo 111 222
5 fooo 1111 22
4 foo 3333 17682
3 foo 5555 21822
2 foo 8888 22520
1 foo 9999 23636

(both queries produces the same result)

DDL-2: descending compound index

--- the previous is the same as in DDL-1 ---
create descending index ttt_unit_y_desc on ttt( unit, y);
commit;
--- the rest is the same as in DDL-1 ---

2.1) LI-T3.0.0.31527
PLAN (T ORDER TTT_UNIT_Y_DESC)

      ID UNIT                  Y            Z

============ ========== ============ ============
5 fooo 1111 22
6 fooo 111 222
7 fooo 11 2222
8 fooo 1 22222
1 foo 9999 23636
2 foo 8888 22520
3 foo 5555 21822
4 foo 3333 17682

2.2) LI-V2.5.4.26821
PLAN SORT ((T INDEX (TTT_UNIT_Y_DESC)))

      ID UNIT                  Y            Z

============ ========== ============ ============
1 foo 9999 23636
2 foo 8888 22520
3 foo 5555 21822
4 foo 3333 17682
5 fooo 1111 22
6 fooo 111 222
7 fooo 11 2222
8 fooo 1 22222

Commits: 8508fea FirebirdSQL/fbt-repository@9a6a178

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

summary: Wrong result when use "where <field_C> STARTING WITH <:value> ORDER BY <field_N>' and field_N is leading part of compound index key: { field_C, field_N } => Wrong result when use "where <field_C> STARTING WITH <:value> ORDER BY <field_N>' and field_C is leading part of compound index key: { field_C, field_N }

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Looks like a regression after fixing CORE1846.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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