View/subselect with "union" does not use computed index
-------------------------------------------------------
Key: CORE-4937
URL: http://tracker.firebirdsql.org/browse/CORE-4937
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.4
Reporter: Reginald Poyau
How to reproduce:
1> run isql
> create table test1 (id integer not null primary key, tms timestamp default
> current_timestamp);
> create table test2 (id integer not null primary key, tms timestamp default
> current_timestamp);
> alter table test1 add occurred integer computed by (case when tms <
> current_timestamp then 1 else 0 end);
> create index test1_idx0 on test1 computed by (case when tms <
> current_timestamp then 1 else 0 end);
> alter table test2 add occurred integer;
> create index test2_idx0 on test2(occurred);
> insert into test1(id) values(1);
> insert into test2(id) values(1);
> commit;
> set plan;
select * from test1 where occurred = 1;
PLAN (TEST1 INDEX (TEST1_IDX0))
> select * from test2 where occurred = 1;
PLAN (TEST2 INDEX (TEST2_IDX0))
> select * from (select * from test1 union select * from test2) where occurred
> = 1;
PLAN (TEST1 NATURAL)
PLAN (TEST2 INDEX (TEST2_IDX0))
ID TMS OCCURRED
============ =========== ============
1 18-SEP-2015 1
> commit;
> create view test_view as select * from test1 union select * from test2;
> select * from test_view where occurred = 1;
PLAN (TEST_VIEW TEST1 NATURAL)
PLAN (TEST_VIEW TEST2 INDEX (TEST2_IDX0))
ID TMS OCCURRED
============ =========== ============
1 18-SEP-2015 1
>
Notice that subselect on union on test1 and test2 index TEST1_IDX0 did not
get used.
Notice that select test_view also did not use TEST1_IDX0 index.
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
