FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.24k stars 213 forks source link

Incorrect subquery unnesting with complex dependencies (SubQueryConversion = true) #8252

Closed dyemanov closed 3 weeks ago

dyemanov commented 1 month ago

Example where the subquery is linked with the outer query inside both AND and OR conditions:

select first 5 *
from sales s
where exists (select 1 from customer c where s.cust_no = c.cust_no and (s.cust_no=c.cust_no or s.cust_no = c.cust_no));

The plan is:

Select Expression
    -> First N Records
        -> Nested Loop Join (inner)
            -> Table "SALES" as "S" Full Scan
            -> Filter
                -> Filter
                    -> Table "CUSTOMER" as "C" Access By ID
                        -> Bitmap Or
                            -> Bitmap
                                -> Index "RDB$PRIMARY22" Unique Scan
                            -> Bitmap
                                -> Index "RDB$PRIMARY22" Unique Scan

and we see an inner join while there should be a semi-join or at least a subquery as in pre-v5 versions.

pavel-zotov commented 2 weeks ago

QA note: additional test see in: tests/functional/tabloid/test_aae2ae32.py