Issue Details (XML | Word | Printable)

Key: CORE-4665
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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 }

Created: 12/Jan/15 10:19 AM   Updated: 23/Sep/15 11:33 AM
Component/s: None
Affects Version/s: None
Fix Version/s: 3.0 Beta 2

QA Status: Done successfully


 Description  « Hide
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


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 12/Jan/15 10:50 AM
Looks like a regression after fixing CORE-1846.