You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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)
--- 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)
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 }
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)
============ ========== ============ ============
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))
============ ========== ============ ============
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)))
============ ========== ============ ============
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)))
============ ========== ============ ============
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)
============ ========== ============ ============
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)))
============ ========== ============ ============
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
The text was updated successfully, but these errors were encountered: