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