Bad optimization of some operations with views containing subqueries
--------------------------------------------------------------------
Key: CORE-5393
URL: http://tracker.firebirdsql.org/browse/CORE-5393
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.1, 3.0.0, 4.0 Initial
Reporter: Dmitry Yemanov
Bug is caused by internally created derived expressions being based on all view
streams, including streams burried inside subqueries, etc. This is causing
various optimization issues due to nested subqueries being non-computable at
the moment.
Test case may look a bit complicated as it exploits the fact that simple field
references become wrapped by derived expressions if located inside an explicit
cursor. But I suspect this issue may be visible in other cases too.
create table test (id int primary key, col int);
commit;
insert into test (id, col) values (1, 1);
insert into test (id, col) values (2, 2);
commit;
-- view must contain a subquery
create or alter view v_test (id1, id2, col1, col2, dummy)
as
select t1.id, t2.id, t1.col, t2.col, (select 1 from rdb$database)
from test t1 join test t2 on t1.col = t2.id;
-- trigger makes the view updatable
create trigger t_v_test
for v_test before update
as
begin
end;
set plan;
set term ^;
execute block
as
begin
for select id1 from v_test as cursor c do
begin
update v_test set col1 = 1
where id1 = c.id1;
-- where current of c;
update v_test set col1 = 1
where id1 = c.id1;
end
end^
set term ;^
PLAN (V_TEST RDB$DATABASE NATURAL)
PLAN JOIN (V_TEST T1 INDEX (RDB$PRIMARY1), V_TEST T2 INDEX (RDB$PRIMARY1))
PLAN (V_TEST RDB$DATABASE NATURAL)
PLAN JOIN (V_TEST T1 INDEX (RDB$PRIMARY1), V_TEST T2 INDEX (RDB$PRIMARY1))
PLAN JOIN (C V_TEST T1 NATURAL, C V_TEST T2 INDEX (RDB$PRIMARY1))
The only naturally-read join is cursor C, this is expected. Both updates
utilize the primary key index for table T1. So far so good.
set term ^;
execute block
as
begin
for select id1 from v_test as cursor c do
begin
update v_test set col1 = 1
-- where id1 = c.id1;
where current of c;
update v_test set col1 = 1
where id1 = c.id1;
end
end^
set term ;^
PLAN (C V_TEST RDB$DATABASE NATURAL)
PLAN (V_TEST RDB$DATABASE NATURAL)
PLAN JOIN (V_TEST T1 NATURAL, V_TEST T2 INDEX (RDB$PRIMARY1))
PLAN JOIN (C V_TEST T1 NATURAL, C V_TEST T2 INDEX (RDB$PRIMARY1))
The first update is not reported in the plan because it's based on the same
cursor as the select itself. However, the second update is unable to utilize
the primary key index for table T1 anymore.
In the production database, this issue is causing 100x degradation in execution
time.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
