Commented by: @livius2

a think about this but is this really wrong query?

look at adapted same query but you explicitly tell optimizer how to build streams by derived table

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ orginal query #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

select *
from TA, TB
left join TC on TA.COL1 = TC.COL1
where TA.COL2 = TB.COL2

PLAN MERGE (SORT (TA NATURAL), SORT (JOIN (TB NATURAL, TC NATURAL)))

this work wrong - resultset is wrong TA.col1=1 <> TC.col1=4

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ addapted query #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
without indexes

select TX.TA_COL1, TX.TA_COL2, TX.TB_COL1, TX.TB_COL2, TC.COL1 AS TC_COL1, TC.COL2 AS TC_COL2
from (SELECT TA.COL1 AS TA_COL1, TA.COL2 AS TA_COL2, TB.COL1 AS TB_COL1, TB.COL2 AS TB_COL2 FROM TA, TB) TX
left join TC on TX.TA_COL1 = TC.COL1
where TX.TA_COL2 = TX.TB_COL2

PLAN JOIN (JOIN (TX TA NATURAL, TX TB NATURAL), TC NATURAL)

this query work ok for me - resultset look ok

Commented by: @livius2

with indexes on every column
first original query throw error "..not positioned.."

addapted query work ok with plan
PLAN JOIN (JOIN (TX TA NATURAL, TX TB INDEX (IDX_TB2)), TC INDEX (IDX_TC1))

Commented by: @dyemanov

As far as I understand, your "original" query is wrong but the "adapted" one is correct. This is from the SQL spec POV.

Commented by: @livius2

"orginal" is your query from description and you say that should be prohibited. my addapted query is "the same" only difference that i change TA, TB to derived tabe - may be optimizer should do this?

Commented by: @dyemanov

First of all, SQL is declarative. And if some declaration is defined to be invalid, I don't see any point in "fixing" it at the optimizer level, which is an implementation detail. Second, I don't see these two queries as equivalent, they're TA JOIN (TB LEFT JOIN TC) vs (TA JOIN TB) LEFT JOIN TC.

Commented by: @livius2

I do not say that this should to be "fixed"
i only think about this in this way:

select *
from (TA, TB) make derived table here if tables are referenced outside
left join TC on TA.COL1 = TC.COL1
where TA.COL2 = TB.COL2

but i do not see that any other db support this way
- i test this in:
-MSSQL
-Ingress
-PostgreSQL

ingress have very good info about failed execution "Relation 'ta' appearing in join_search condition is not involved in the join"
may be FB can have the same info :)

Commented by: @dyemanov

I've analyzed Oracle/MSSQL/Postgres before changing our behaviour and they all "agree" with the new rules. Your mistake is that your treat A, B join C as (A, B) join C while in fact this is A, (B join C). That said, I completely agree that a better error reporting would be great. I just wish it would be easier to accomplish :-)

Commented by: @livius2

i say that (A join B) join C becaouse of derived table. as you can saw in plan returned for addapted query. if you go in that path query work ok. but i do not see simple possibility to make this choice in optimizer.

Commented by: @pcisar

Test created.