Issue Details (XML | Word | Printable)

Key: CORE-5381
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Rashid Abzalov
Votes: 0
Watchers: 2
Operations

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

Regression: could not execute query (select from view with nested view)

Created: 20/Oct/16 10:33 PM   Updated: 16/Oct/19 02:20 PM
Component/s: Engine
Affects Version/s: 3.0.0, 3.0.1
Fix Version/s: 3.0.2, 4.0 Alpha 1

Environment: Windows, Firebird-3.0.1.32609_0_x64

QA Status: Done successfully


 Description  « Hide
This example works in v2.5 but fails in v3.0.

Query:
select A.ID
  from test_view A
  inner join RDB$TYPES D1 on D1.rdb$type = A.ID
  inner join RDB$RELATIONS D2 on D2.rdb$relation_id = A.ID
  inner join RDB$DEPENDENCIES D3 on D3.rdb$dependent_type = A.ID
where 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 t1.ID
  from t1
  inner join t8 B on B.ID = t1.ID
  inner join t2 C on C.ID = t1.ID
  left join t4 D on D.ID = t1.ID
  inner join t5 E on E.ID = t1.ID
  left join t6 F on F.ID = t1.ID

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

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

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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 25/Oct/16 02:27 PM
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.