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

Regression: could not execute query (select from view with nested view) [CORE5381] #5654

Closed
firebird-automations opened this issue Oct 21, 2016 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @abzalov

This example works in v2.5 but fails in v3.0.

Query:
select http://A.ID
from test_view A
inner join RDB$TYPES D1 on D1.rdb$type = http://A.ID
inner join RDB$RELATIONS D2 on D2.rdb$relation_id = http://A.ID
inner join RDB$DEPENDENCIES D3 on D3.rdb$dependent_type = http://A.ID
where http://A.ID = 1

Error:
Statement failed, SQLSTATE = HY000
request size limit exceeded

DDL:
create database 'localhost:c:\test.fdb' page_size 16384 user 'SYSDBA' password 'masterkey' default character set UTF8 collation UTF8;

connect 'localhost:c:\test.fdb' user 'SYSDBA' password 'masterkey';

create table t1(ID bigint not null primary key);
create table t2(ID bigint not null primary key);
create table t3(ID bigint not null primary key);
create table t4(ID bigint not null primary key);
create table t5(ID bigint not null primary key);
create table t6(ID bigint not null primary key);
create table t7(ID bigint not null primary key);
create table t8(ID bigint not null primary key);

set term ^;

create view inner_view(ID)
as
select http://t1.ID
from t1
inner join t8 B on http://B.ID = http://t1.ID
inner join t2 C on http://C.ID = http://t1.ID
left join t4 D on http://D.ID = http://t1.ID
inner join t5 E on http://E.ID = http://t1.ID
left join t6 F on http://F.ID = http://t1.ID

inner join RDB$TYPES G1 on G1.rdb$type = http://t1.ID
inner join RDB$RELATIONS G2 on G2.rdb$relation_id = http://t1.ID
inner join RDB$DEPENDENCIES G3 on G3.rdb$dependent_type = http://t1.ID
inner join RDB$COLLATIONS G4 on G4.rdb$collation_id = http://t1.ID
inner join RDB$FIELDS G5 on G5.rdb$field_type = http://t1.ID
inner join RDB$CHARACTER_SETS G6 on G6.rdb$character_set_id = http://t1.ID
^

create view test_view(ID)
as
select http://t1.ID
from t1
inner join inner_view on inner_view.ID = http://t1.ID
inner join t7 on http://t7.ID = http://t1.ID
left join t3 on http://t3.ID = http://t1.ID

inner join RDB$TYPES D1 on D1.rdb$type = http://t1.ID
inner join RDB$RELATIONS D2 on D2.rdb$relation_id = http://t1.ID
inner join RDB$DEPENDENCIES D3 on D3.rdb$dependent_type = http://t1.ID
inner join RDB$COLLATIONS D4 on D4.rdb$collation_id = http://t1.ID
inner join RDB$FIELDS D5 on D5.rdb$field_type = http://t1.ID
^

Commits: 5f6f5a3 ff3e7cc

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Issue is a side effect of better optimization logic, causing too many artificial conjunctions to be generated in such a border case (when all join conditions use the same ID) and resulting in too slow optimization and finally request size overflow.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

Fix Version: 3.0.2 [ 10785 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0.0 [ 10740 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Deferred

Test Details: Currently it's not possible to run such test every day on test-running host: building of plan with ~32000 predicates (dsql/Nodes.h:const int MAX_CONJUNCTS = 32000) takes too much time.
It's a good idea to run such query one time per week but fbt_run does not support such feature (skip some test depending on week day or day number since month start etc).
Discussed with dimitr, last letter 29-oct-2016 16:54

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Deferred => Done successfully

Test Details: Currently it's not possible to run such test every day on test-running host: building of plan with ~32000 predicates (dsql/Nodes.h:const int MAX_CONJUNCTS = 32000) takes too much time.
It's a good idea to run such query one time per week but fbt_run does not support such feature (skip some test depending on week day or day number since month start etc).
Discussed with dimitr, last letter 29-oct-2016 16:54

=>

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