Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Subquery-based predicates are not evaluated early in the join order [CORE1549] #1966

Closed
firebird-automations opened this issue Oct 29, 2007 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @dyemanov

Is related to QA164

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 http://T2.ID >= http://T1.ID
where not exists (select * from TEST T where http://T.ID = http://T1.ID - 1)
and not exists (select * from TEST T where http://T.ID = http://T2.ID + 1)

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

Commits: 914ddbb e93648f

====== Test Details ======

::: NB ::: Test uses EXPLAINED form of plan. Letter from dimitr: 11-mar-2015 19:58.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1 RC1 [ 10201 ]

Fix Version: 2.0.4 [ 10211 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA164 [ QA164 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13357 ] => Firebird [ 14269 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A test ok and qmtest made

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment