View/subselect with "union" does not use computed index [CORE4937]

[firebird-issue-importer]

Submitted by: Reginald Poyau (rpoyau)

Is related to CORE4927

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.

Commits: 6e50d9b FirebirdSQL/fbt-repository@395f5be

[firebird-issue-importer]

Commented by: @dyemanov

Please test the v2.5.5 snapshot build. As far as I see, it uses both indices in your samples. So this issue is supposedly fixed there.

[firebird-issue-importer]

Commented by: Reginald Poyau (rpoyau)

I have tested first test I submitted and it works with 2.5.5.26930-0 snapshot . however a more complicated computed column with index fails.

New test:
> alter table test1 add occurred2 computed by (CASE WHEN tms IS NOT NULL THEN 0 WHEN DATEDIFF(MINUTE FROM tms to CURRENT_TIMESTAMP) > 15 THEN 1 ELSE 0 END);
>create index test1_idx1 on test1 computed by (CASE WHEN tms IS NOT NULL THEN 0 WHEN DATEDIFF(MINUTE FROM tms to CURRENT_TIMESTAMP) > 15 THEN 1 ELSE 0 END);
> select * from test1 where occurred2 = 1;

PLAN (TEST1 INDEX (TEST1_IDX1))
>select * from (select id, occurred2 as occurred from test1 union select id, occurred from test2) where occurred = 1;

PLAN (TEST1 NATURAL)
PLAN (TEST2 INDEX (TEST2_IDX0))
>create view test_view2 as select id, occurred2 as occurred from test1 union select id, occurred from test2;
> select * from test_view2 where occurred = 1;

PLAN (TEST_VIEW2 TEST1 NATURAL)
PLAN (TEST_VIEW2 TEST2 INDEX (TEST2_IDX0))
>

[firebird-issue-importer]

Commented by: @dyemanov

This should also be fixed now, please check with the next (tomorrow's) snapshot build.

[firebird-issue-importer]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-issue-importer]

Modified by: @dyemanov

Link: This issue is related to CORE4927 [ CORE4927 ]

[firebird-issue-importer]

Commented by: Reginald Poyau (rpoyau)

I have confirmed that both tests now pass on snapshot build 2.5.5.26932-0.

[firebird-issue-importer]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.5 [ 10670 ]

[firebird-issue-importer]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

[firebird-issue-importer]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]