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-issue-importer opened this issue Oct 29, 2007 · 7 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 29, 2007

Modified by: @dyemanov

Fix Version: 2.1 RC1 [ 10201 ]

Fix Version: 2.0.4 [ 10211 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 29, 2007

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 29, 2007

Modified by: @pcisar

Link: This issue is related to QA164 [ QA164 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 3, 2008

Commented by: @pmakowski

Q/A test ok and qmtest made

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 3, 2008

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 29, 2015

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