SQL: Correlated subqueries

[philrz]

While SuperSQL currently supports non-correlated subqueries in plain SQL (#6013), correlated subqueries in plain SQL are not yet supported. In the interim, it is possible to write equivalent logic using pipe syntax.

Details

Repro is with super commit 6205ec1.

Consider the following SQL correlated subquery running as expected in Postgres.

$ psql postgres
psql (17.7 (Homebrew))
Type "help" for help.

postgres=# WITH employees AS (
  SELECT * FROM (VALUES
    (1, 'Alice', 'HR', 50000),
    (2, 'Bob', 'HR', 55000),
    (3, 'Cara', 'IT', 70000),
    (4, 'Dave', 'IT', 75000),
    (5, 'Eve', 'Sales', 60000)
  ) AS t(id, name, dept, salary)
)
SELECT e1.name,
       e1.dept,
       e1.salary,
       (
         SELECT AVG(e2.salary)
         FROM employees e2
         WHERE e2.dept = e1.dept
       ) AS avg_dept_salary
FROM employees e1;

 name  | dept  | salary |  avg_dept_salary   
-------+-------+--------+--------------------
 Alice | HR    |  50000 | 52500.000000000000
 Bob   | HR    |  55000 | 52500.000000000000
 Cara  | IT    |  70000 | 72500.000000000000
 Dave  | IT    |  75000 | 72500.000000000000
 Eve   | Sales |  60000 | 60000.000000000000
(5 rows)

If attempted in super, the query is rejected with an error message.

$ super -version
Version: 6205ec1a0

$ super -c "
WITH employees AS (
  SELECT * FROM (VALUES
    (1, 'Alice', 'HR', 50000),
    (2, 'Bob', 'HR', 55000),
    (3, 'Cara', 'IT', 70000),
    (4, 'Dave', 'IT', 75000),
    (5, 'Eve', 'Sales', 60000)
  ) AS t(id, name, dept, salary)
)
SELECT e1.name,
       e1.dept,
       e1.salary,
       (
         SELECT AVG(e2.salary)
         FROM employees e2
         WHERE e2.dept = e1.dept
       ) AS avg_dept_salary
FROM employees e1;"

correlated subqueries not currently supported at line 17, column 26:
         WHERE e2.dept = e1.dept
                         ~~~~~~~

In addition to disclosing this limitation, the docs also explain that it's possible to create a logically equivalent query using pipe syntax. For instance, to get the same result as from the plain SQL query above:

$ super -c "
let employees = (
  SELECT * FROM (VALUES
    (1, 'Alice', 'HR', 50000),
    (2, 'Bob', 'HR', 55000),
    (3, 'Cara', 'IT', 70000),
    (4, 'Dave', 'IT', 75000),
    (5, 'Eve', 'Sales', 60000)
  ) AS t(id, name, dept, salary)
)
from employees
| values {
  name,
  dept,
  salary, avg_dept_salary: (
    unnest {outer_dept:dept, data:[from employees]}
    | avg(data.salary) filter (data.dept==outer_dept)
  )
}"

{name:"Alice",dept:"HR",salary:50000,avg_dept_salary:52500.}
{name:"Bob",dept:"HR",salary:55000,avg_dept_salary:52500.}
{name:"Cara",dept:"IT",salary:70000,avg_dept_salary:72500.}
{name:"Dave",dept:"IT",salary:75000,avg_dept_salary:72500.}
{name:"Eve",dept:"Sales",salary:60000,avg_dept_salary:60000.}

This issue will track the addition of correlated subquery support for plain SQL as well.

[philrz]

There a note in the docs about this not yet being supported, so that should be removed when this issue is addressed.