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 }
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4665
URL: http://tracker.firebirdsql.org/browse/CORE-4665
Project: Firebird Core
Issue Type: Bug
Reporter: 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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
GigeNET is offering a free month of service with a new server in Ashburn.
Choose from 2 high performing configs, both with 100TB of bandwidth.
Higher redundancy.Lower latency.Increased capacity.Completely compliant.
vanity: www.gigenet.com
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
