Issue Details (XML | Word | Printable)

Key: CORE-4976
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 3
Operations

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

Optimizer does not use index when selecting from "unioned" view and WHERE-clause has comparison with result of singletone subquery

Created: 24/Oct/15 09:34 AM   Updated: 24/Oct/15 06:46 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 

QA Status: No test


 Description  « Hide
Test-1.
#####

This sample will work FINE (it's shown here only for comparison with 2nd one):

create or alter view v_simple as
select 1 id from rdb$database;

recreate table thead(id int primary key using descending index thead_pk, x int);
recreate table tdata(x int, y int);
create index tdata_x on tdata(x);

create or alter view v_simple as ------------------ THIS IS _NOT_ "UNIONED"-VIEW, IT'S JUST SINGLE-TABLE PROJECTION
select * from tdata;

insert into thead(id, x) values(1, 1);
insert into tdata(x, y) select 1, rand()*10000 from rdb$types;
commit;

set statistics index thead_pk;
set statistics index tdata_x;

set planonly;
-- set echo on;

select v.*
from v_simple v
where
    v.x = 1; ------------------------------------------------------------------------------------ [ 1 ]

select v.*
from v_simple v
where
    v.x = (select h.x from thead h order by id desc rows 1); --------------------- [ 2 ]


Output:
#####

// for [ 1 ]:
PLAN (V TDATA INDEX (TDATA_X))

// for [ 2 ]:
PLAN (H ORDER THEAD_PK)
PLAN (V TDATA INDEX (TDATA_X))

So, index TDATA_X is used in both cases, regardless of expression form in WHERE-clause (i.e. search by literal or by result of single-tone subquery).


Test-2.
#####

This sample uses "UNIONED" view and optimizer will NOT use indexes of underlying tables if WHERE clause contains comparison with subquery.

create or alter view v_unioned as select 1 id from rdb$database;

recreate table thead_u(id int primary key using descending index thead_u_pk, x int);
recreate table tdata_1(x int, y int, z int);
recreate table tdata_2(x int, y int, z int);

create index tdata_1_x on tdata_1(x);
create index tdata_2_x on tdata_2(x);

--create index tdata_1_xy on tdata_1(x, y);
--create index tdata_2_xy on tdata_2(x, y);

create or alter view v_unioned as
select * from tdata_1
union all
select * from tdata_2;


insert into thead_u(id, x) values(1, 1);
insert into tdata_1(x, y) select 1, rand()*10000 from rdb$types;
insert into tdata_2(x, y) select 1, rand()*10000 from rdb$types;
commit;

set statistics index thead_u_pk;
set statistics index tdata_1_x;
set statistics index tdata_2_x;

set planonly;
--set echo on;

select v.*
from v_unioned v
where
    v.x = 1; ------------------------------------------------------------------------------- [ 1 ]


select v.*
from v_unioned v
where
    v.x = (select h.x from thead h order by id desc rows 1); ----------------- [ 2 ]


Output:
######

// for [ 1 ] - all OK:
PLAN (V TDATA_1 INDEX (TDATA_1_X), V TDATA_2 INDEX (TDATA_2_X))

// for [ 2 ] -- get NATURAL reads:
PLAN (H ORDER THEAD_PK)
PLAN (V TDATA_1 NATURAL, V TDATA_2 NATURAL)


PS.
Tested on WI-V2.5.5.26936, WI-V3.0.0.32114

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 24/Oct/15 06:46 PM
This seems to be (at least) related to the previously reported case, if not a duplicate of same.