JOIN on nested UNION leads in CROSS JOIN und Full Table Scan

[DanielBauten]

Initial situation

• Server version: WI-V-6.3.3.3.1683 Firebird 5.0 (Firebird 5.0.3 @ Windows 11)
• Client library: 5.0.3.1683
• Common table expression CTE_MAIN with a simple UNION of (as a test) 2 records
• Table K_S_D_NODE with a total of 6.560.660 records and a PRIMARY KEY on K_S_D_NODE.ID

Problem

• The following statement

  EXECUTE BLOCK
  RETURNS (
      "REC_COUNT" VARCHAR(1024))
  AS
  DECLARE VARIABLE CLT_IDENT TYPE OF DMN_IDENT DEFAULT 'SOME_CLIENT';
  DECLARE VARIABLE F_FULLIDENT TYPE OF DMN_FULLIDENT DEFAULT 'SOME_FILE';
  DECLARE VARIABLE C_NSF_NONE TYPE OF DMN_SMALLINT DEFAULT 0; -- 0: 'nsfNone'
  DECLARE VARIABLE C_NSF_SELECTEDONLY TYPE OF DMN_SMALLINT DEFAULT 1; -- 1: 'nsfSelectOnly'
  DECLARE VARIABLE C_NSF_DESELECTEDONLY TYPE OF DMN_SMALLINT DEFAULT 2; -- 2: 'nsfDeselectOnly'
  DECLARE VARIABLE C_NST_SELECTEDANDINHERIT TYPE OF DMN_SMALLINT DEFAULT 2; -- 2: 'nstSelectAndInherit'
  DECLARE VARIABLE C_NST_DESELECTEDANDINHERIT TYPE OF DMN_SMALLINT DEFAULT 4; -- 4: 'nstDeselectAndInherit'
  DECLARE VARIABLE F_ID TYPE OF DMN_REFID;
  DECLARE VARIABLE F_SREF TYPE OF DMN_REFID;
  begin
  
    select f.ID, f.DSREF
      from k_file f
      join k_client clt
        on clt.ID = f.CLTREF
      where clt.IDENT = UPPER(:clt_ident) AND
            f.PREFIX || '.' || f.IDENT = UPPER(:f_fullident)
      into :f_id, :f_sref;
  
    delete from tmp_s_d_flt;
    insert into tmp_s_d_flt (I_FLTREF, O_ID, O_DREF, O_TOTALORDER)
      select DISTINCT APTFRMREF as I_FLTREF, SNREF as O_ID, 0 as O_DREF, SNREF as O_TOTALORDER
        from get_s_d_slc_aptfrm(:f_id, :f_sref, :c_nsf_none)
      union
      select DISTINCT APTFRMREF as I_FLTREF, child.ID as O_ID, 0 as O_DREF, child.ID as O_TOTALORDER
        from get_s_d_slc_aptfrm(:f_id, :f_sref, :c_nsf_deselectedonly) slc
        join get_s_d_children(:f_sref, slc.SNREF) child
          on slc.NST = :c_nst_deselectedandinherit;
  
    for
  
      with cte_main as (
  
        select FIRST 1 slc.SNREF, slc.APTFRMREF
          from get_s_d_slc_aptfrm(:f_id, :f_sref, :c_nsf_selectedonly) slc
  
        union
  
        select FIRST 1 child.ID SNREF, slc.APTFRMREF
          from get_s_d_slc_aptfrm(:f_id, :f_sref, :c_nsf_selectedonly) slc
          join get_s_d_children(:f_sref, slc.SNREF) child
            on slc.NST = :c_nst_selectedandinherit
          where not exists (select * from tmp_s_d_flt where O_ID = child.ID and I_FLTREF = slc.APTFRMREF)
  
      )
  
      -- CTE_MAIN:   2 records (see above)
      -- K_S_D_NODE: 6.560.660 records
      select count(*) -- TEST A: incorrect result (via CROSS JOIN) '13.121.320'
        from cte_main main
        join k_s_d_node sn
          on sn.ID = main.SNREF
        into :"REC_COUNT"
  
  --    select count(*) -- TEST B: correct Result '2', but incorrect FULL TABLE SCAN on 'K_S_D_NODE'
  --      from cte_main main
  --      left join k_s_d_node sn
  --        on sn.ID = main.SNREF
  --      into :"REC_COUNT"
  
    do
  
      suspend;
  
  end
  

  leads in two problems
• Test A: an unexpected CROSS JOIN with a incorrect result of 13.121.320
• for details see TEST A - Explain Plan.txt
• Test B: a correct result of 2, but with an unexpected FULL TABLE SCAN on K_S_D_NODE
• for details see TEST B - Explain Plan.txt

Notes

1. the problem is still present when using

   select count(*)
     from cte_main main,
         k_s_d_node sn
     where sn.ID = main.SNREF
   

2. the problem is still present when using inline-select instead of common table expression
3. the problem is NOT present when CTE_MAIN only has the first select-statement or the second select-statement (e.g. no UNION)
4. the problem occurs on 5.0.3.1683, but it does NOT on 5.0.2.1613
5. possible cause Incorrect join order for JOIN LATERAL with UNION referencing the outer stream(s) via its select list #8628 or MIN/MAX aggregates may badly affect the join order in queries with mixed INNER/LEFT joins #8488

[arvanus]

Looks related to #8891