Issue Details (XML | Word | Printable)

Key: CORE-1549
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dmitry Yemanov
Votes: 0
Watchers: 0

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

Subquery-based predicates are not evaluated early in the join order

Created: 29/Oct/07 10:10 AM   Updated: 29/May/15 05:04 PM
Component/s: Engine
Affects Version/s: 2.0.0, 1.5.4, 2.0.1, 2.1 Alpha 1, 2.1 Beta 1, 2.0.2, 2.0.3, 2.1 Beta 2
Fix Version/s: 2.1 RC1, 2.0.4

Issue Links:

QA Status: Done successfully
Test Details: ::: NB ::: Test uses EXPLAINED form of plan. Letter from dimitr: 11-mar-2015 19:58.

 Description  « Hide
When you have a join and a WHERE clause containing an EXISTS predicate that refers to the first joined stream, that EXISTS predicate could be evaluated early (right after the first stream is fetched), but in fact it's evaluated later (after the second stream is fetched) thus not being able to filter the unnecessary rows out from the join in advance and reduce the execution time. This is a regression introduced in v1.5.

Test case:

create table TEST(ID integer not null);
alter table test add constraint PK_TEST primary key (ID);
create generator GEN_TEST;
commit work;

insert into TEST(id) values (gen_id(GEN_TEST, 1));
insert into TEST(id) values (gen_id(GEN_TEST, 1));
commit work;

insert into TEST (id) select gen_id(GEN_TEST, 1) from TEST where gen_id(GEN_TEST, 0) < 2000;
commit work;

-- Now try this statement:

select 1
from TEST T1 join TEST T2 on T2.ID >= T1.ID
where not exists (select * from TEST T where T.ID = T1.ID - 1)
  and not exists (select * from TEST T where T.ID = T2.ID + 1)

-- A few thousands of page fetches are okay, while 12 millions are definitely not.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Philippe Makowski added a comment - 03/Apr/08 10:02 AM
Q/A test ok and qmtest made