Issue Details (XML | Word | Printable)

Key: CORE-5070
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Ryszard Skotarski
Votes: 1
Watchers: 3
Operations

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

Compound index cannot be used for filtering in some ORDER/GROUP BY queries

Created: 08/Jan/16 11:25 AM   Updated: 09/Dec/18 01:56 PM
Component/s: Engine
Affects Version/s: 3.0 RC1
Fix Version/s: 4.0 Beta 1, 3.0.5

Environment: W10 x64 FB3 build 32268
Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
create table test1 (ia integer not null,id integer not null, it integer not null, dt date not null, primary key (ia,id));
create table test2 (ia integer not null, it integer not null, dt date not null, primary key (ia,dt,it));
alter table test1 add CONSTRAINT fk_test2 FOREIGN key (ia,dt,it) REFERENCES test2 (ia,dt,it)

select * from test1
where ia=1 and dt='01/01/2015' and it=1
order by id;
plan : PLAN (TEST1 ORDER RDB$PRIMARY249)
performance very poor
the same problem with :
select id from test1
where ia=1 and dt='01/01/2015' and it=1
group by id

where without order or group works fine

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov made changes - 10/Jan/16 10:38 AM
Field Original Value New Value
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 10/Jan/16 10:41 AM
Summary Wrong Plan Used Compound index cannot be used for filtering in some ORDER/GROUP BY queries
Affects Version/s 3.0 RC1 [ 10584 ]
Affects Version/s 3.0 Beta 2 [ 10586 ]
Dmitry Yemanov made changes - 12/Feb/17 04:24 PM
Link This issue relate to CORE-5481 [ CORE-5481 ]
Dmitry Yemanov made changes - 20/Nov/18 04:43 PM
Link This issue relate to CORE-5965 [ CORE-5965 ]
Dmitry Yemanov made changes - 20/Nov/18 06:20 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 4.0 Beta 1 [ 10750 ]
Fix Version/s 3.0.5 [ 10885 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 09/Dec/18 01:56 PM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status No test Done successfully
Pavel Zotov made changes - 09/Dec/18 01:56 PM
Status Resolved [ 5 ] Closed [ 6 ]