UNION ALL optimization with constant false condition

[EPluribusUnum]

Hi *!

CREATE OR ALTER VIEW union_opt
AS
SELECT 'A' union_id
FROM rdb$database
UNION ALL
SELECT 'B'
FROM rdb$database

SELECT *
FROM union_opt
WHERE union_id = 'A'

The select does 2 non-index reads.
I think it should be only 1 non-index read, as the second select expressions conditon is fix false, no row-by-row evaluation is needed.

Thank you!

[mrotteveel]

It will need to get that row to be able to evaluate that it should be filtered out.

[EPluribusUnum]

The filtered column is a constant, so the column value is known at prepare time. The condition on the constant column also constant, so the condition can be evaluated at prepare time without needing to fetch any record from the table at exec time.

[dyemanov]

Plan and statistics after the fix:

Select Expression
    -> Filter
        -> Union
            -> Filter (preliminary)
                -> Table "RDB$DATABASE" as "UNION_OPT RDB$DATABASE" Full Scan
            -> Filter (preliminary)
                -> Table "RDB$DATABASE" as "UNION_OPT RDB$DATABASE" Full Scan

UNION_ID 
======== 
A        

Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  | Backout | Purge   | Expunge |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
RDB$DATABASE                    |        1|         |         |         |         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+

[dyemanov]

Related to #1708.