Subquery identification and attachment

Subqueries in the SQL query are matched against abstract plan subqueries using their underlying tables. As tables are unambiguously identified, so are the subqueries. For example:

select
   (select c11 from t1 where c12 = t3.c32), c31
from t3
where
   c32 > (select c22 from t2 where c21 = t3.c31)
plan
“(nested
    (nested
        (t_scan t3)
        (subq
             (i_scan i_c11_c12 t1)
   )
)
    (subq
        (i_scan i_c21 t2)
   )
)”

However, when table names are ambiguous, the identity of the subquery is needed to solve the table name ambiguity.

Subqueries are identified with numbers, in the order of their leading opened parenthesis “(“.

This example has two subqueries; both refer to table t1:

select 1
from t1
where 
   c11 not in (select c12 from t1)
   and c11 not in (select c13 from t1)

In the abstract plan, the subquery which projects out of c12 is named “1” and the subquery which projects out of c13 is named “2”.

(nested 
    (nested 
        (t_scan t1) 
        (subq
           (scalar_agg
                (i_scan i_c11_c12 (table t1 (in (subq 1))))
           )
        )
    ) 
    (subq
        (scalar_agg
            (i_scan i_c13 (table t1 (in (subq 2))))
        )
    )
)

In this query, the second subquery is nested in the first:

select * from t1 
where c11 not in
    (select c12 from t1 
     where c11 not in
     (select c13 from t1) 

In this case, the subquery that projects out of c12 is also named “1” and the subquery that projects out of c13 is also named “2”.

(nested
   (t_scan t1
   (subq
      (scalar_agg
         (nested
            (i_scan i_c12 (table t1 (in (subq 1))))
            (subq
               (scalar_agg
                  (i_scan i_c21 (table t1 (in (subq 2))))
                )
             )
          )
       )
   )
)