Submitted by: @pavel-zotov

Relate to CORE4937

Attachments:
join-with-unioned-view-ddl.zip

Run preparing script from attached zip.
There is table 'master', several tables with name like 'detail_NNNN' and view 'vd_union' that is defined as UNION of these detail tables.

Each table 'detail_NNNN' has compound index.
Outer join from derived table based on 'master' (as driving data source) and view 'vd_union' will produce NL (index scans with full matching).
In 3.0 explained plan will be like this:

    \-\>  Nested Loop Join \(outer\)
        \-\> Table "MASTER" as "D D" Full Scan
        \-\> Filter
            \-\> Union
                \-\> Filter
                    \-\> Table "DETAIL\_1000" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D1000\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_1200" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D1200\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_2000" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D2000\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_2100" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D2100\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_3300" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D3300\_WSRS" Range Scan \(full match\)

Table 'master' from attached .zip contains such data that have matching only in ONE of detail tables - "detail_2100". NO other tables has any row that could be matched in 'master', so NO other tables except "detail_2100" should be scanned during execution of query with plan which is shown above.

Now run in 2.5 following.

TEST-1
#⁠#⁠#⁠#⁠#⁠#⁠

select count(*)
from (
select
d.dd_id,
d.ware_id,
2100 as snd_optype_id ------------------------ LITERAL HERE
from master d
) d
left join vd_union qd on
qd.ware_id = d.ware_id
and qd.snd_optype_id = d.snd_optype_id
and qd.rcv_optype_id is not distinct from 3300
and qd.snd_id = d.dd_id;

TRACE in 2.5:

PLAN JOIN (D D NATURAL(QD Q INDEX (D1000_WSRS))
PLAN (QD Q INDEX (D1200_WSRS))
PLAN (QD Q INDEX (D2000_WSRS))
PLAN (QD Q INDEX (D2100_WSRS))
PLAN (QD Q INDEX (D3300_WSRS)))
1 records fetched
6 ms, 1411 fetch(es)

Table Natural Index
****************************************************
MASTER 44
DETAIL_2100 440

-- so far so good. Engine scanned only ONE table and skipped all others.

TEST-2
#⁠#⁠#⁠#⁠#⁠#⁠

select count(*)
from (
select
d.dd_id,
d.ware_id,
iif(1 = 0, 3300, 2100) as snd_optype_id -------------------- EVALUATION EXPRESSION HERE
----- this was before: 2100 as snd_optype_id
from master d
) d
left join vd_union qd on
qd.ware_id = d.ware_id
and qd.snd_optype_id = d.snd_optype_id
and qd.rcv_optype_id is not distinct from 3300
and qd.snd_id = d.dd_id;

TRACE in 2.5:

PLAN JOIN (D D NATURAL(QD Q INDEX (D1000_WSRS))
PLAN (QD Q INDEX (D1200_WSRS))
PLAN (QD Q INDEX (D2000_WSRS))
PLAN (QD Q INDEX (D2100_WSRS))
PLAN (QD Q INDEX (D3300_WSRS)))
1 records fetched
6 ms, 1771 fetch(es)

Table Natural Index
****************************************************
MASTER 44
DETAIL_1000 20 <<<<<<<<<<<< ???
DETAIL_2000 30 <<<<<<<<<<<< ???
DETAIL_2100 440
DETAIL_3300 130 <<<<<<<<<<<< ???

One may see that engine did excessive scans. Tables DETAIL_1000, DETAIL_2000 and DETAIL_3300 were scanned despite that they do not contain data that could match join expression.

The only difference if test-1 & test-2 is evaluating expression inside driving DT ( "iif(1 = 0, 3300, 2100)" ).

NO such trouble in 3.0: its trace shows that engine avoids to do unnecessary scans regardless of any evaluation of columns that are involved into JOIN inside DT.

This is trace in 3.0 (for 2nd query):

  7 ms, 1411 fetch\(es\)

Table Natural Index
****************************************************
MASTER 44
DETAIL_2100 440

Can this improvement be backported to 2.5 ?

PS. Tested on: WI-V6.3.5.26926, WI-V6.3.0.32022

Commits: 718586f FirebirdSQL/fbt-repository@bbf9595