Submitted by: @dyemanov

Test case #⁠1:

select 0 i from rdb$database
union all
select 1 from rdb$database
union all
select 2 from rdb$database

Current plan:

Select Expression
-> Union
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan

Expected plan:

Select Expression
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan

Test case #⁠2:

select 0 i from rdb$database
union
select 1 from rdb$database
union
select 2 from rdb$database

Current plan:

Select Expression
-> Sort
-> Union
-> Sort
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan

Expected plan:

Select Expression
-> Sort
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan

The latter case is very important as it avoids redundant sorts. However, even the former case could benefit from the "plain" execution, as it saves one union context thus avoiding redundant record copying.

As a bonus, for the N streams union, we'll have only one context introduced instead of N-1 contexts. Of course, mixed type (both ALL and DISTINCT used for the streams) unions will behave like before:

select 0 i from rdb$database
union distinct
select 1 from rdb$database
union all
select 2 from rdb$database

Select Expression
-> Union
-> Sort
-> Union
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" Full Scan

Commits: 0a98281 FirebirdSQL/fbt-repository@b782566

====== Test Details ======

Test contains plan in EXPLAINED form, according to answer by dimitr, letter 22-mar-2015 16:07