support scalar subquery expression

[Fedomn]

Scalar subqueries are subqueries that return a single result. Scalar subqueries can either be correlated or uncorrelated.

Uncorrelated Scalar Subqueries

An uncorrelated subquery returns the same single value for all records in a query. That means the subquery is the same as a normal select expr.

SELECT  employee_id,
        age,
        (SELECT MAX(age) FROM employee) max_age
FROM    employee

• [x] #86
• [x] #91

Correlated Scalar Subqueries

Subqueries can be correlated, this means that the subquery contains references to the outer query. These outer references are typically used in filter clauses (SQL WHERE clause). For example:

SELECT   A.dep_id,
         A.employee_id,
         A.age,
         (SELECT  MAX(age) FROM employee B WHERE A.dep_id = B.dep_id) max_age
FROM     employee A

The where condition A.dep_id = B.dep_id is the correlated condition.

Correlated scalar subqueries are planned using LEFT OUTER joins. The previous example will be rewritten to:

SELECT  A.dep_id,
        A.employee_id,
        A.age,
        B.max_age
FROM    employee A
        LEFT OUTER JOIN (SELECT   dep_id,
                                  MAX(age) max_age
                         FROM     employee B
                         GROUP BY dep_id) B
         ON B.dep_id = A.dep_id