fix(optimizer)!: Mark UDTF child scopes as ScopeType.SUBQUERY

[VaggelisD]

BigQuery supports correlated join operations which allow join RHS UDTFs to reference columns from the LHS. Example:

WITH cte AS (
  SELECT 1 AS col
) 
SELECT * FROM cte 
LEFT JOIN UNNEST((
   SELECT ARRAY_AGG(DISTINCT x) AS agg FROM UNNEST([1]) AS x WHERE col = 1
));

However, UDTFs currently mark their nested scopes as ScopeType.DERIVED_TABLE which not only seems semantically incorrect but also blocks the parent/outer scope from collecting & qualifying the UNNEST external columns, as would happen in a typical correlated subquery.