If you modify the attachment point for a subquery, you must choose a point at which all of the correlation columns are available.This query is correlated to both of the tables in the outer query:
select *
from t1, t2, t3
where c12 = 0
and c11 = c21
and c22 = c32
and 0 < (select c31 from t3 where c31 = t1.c11
and c32 = t2.c22)
This plan uses the join order t1, t2, t3, with the subquery nested over the t1-t2 join:
( g_join
( nested
( g_join
( i_scan i_c11_c12 t1 )
( i_scan i_c22 t2 )
)
( subq 1
( t_scan ( table t3 ( in (subq 1 ) ) ) )
)
)
( i_scan i_c32 t3 )
)
Since the subquery requires columns from both outer tables, it would be incorrect to nest it over the scan of t1 or the scan of t2; such errors are silently corrected during optimization.
