Modifying subquery nesting

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 two 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:

(nl_join 
    (nested 
        (nl_join 
            (i_scan i_c11_c12 t1) 
            (i_scan i_c22 t2) 
        ) 
        (subq 
            (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.

However, the following abstract plan makes the legal request to nest the subquery over the three-table join:

(nested
  (nl_join
    (i_scan i_c11_c12 t1)
    (i_scan i_c22 t2)
    (i_scan i_c32 t3)
  )
  (subq
    (t_scan (table t3 (in (subq 1))))
  )
)