Support scalar correlated subqueries

Why it matters

This feature enables ClickHouse to handle scalar correlated subqueries, which are subqueries that reference columns from outer queries, improving query expressiveness and compatibility with complex SQL patterns. It resolves previous limitations by allowing users to write more advanced filtering and projection logic, enhancing query flexibility and supporting a wider range of analytical workloads.

How to use it

Users can now write scalar correlated subqueries directly within the WHERE clause or in the projection list in simple cases without any special configuration. Example usage in the WHERE clause:

SELECT ...
FROM ...
WHERE column = (SELECT scalar_value FROM ... WHERE outer_table.column = inner_table.column)

No additional settings are required to enable this feature.