Nested correlated subquery error with a depth exceeding 1

[irenjj]

Describe the bug

SELECT e1.employee_name, e1.salary
FROM employees e1   <----------------------------┐
WHERE e1.salary > (                              |
    SELECT AVG(e2.salary)                        |
    FROM employees e2                            |
    WHERE e2.dept_id = e1.dept_id                |
    AND e2.salary > (                            |
        SELECT AVG(e3.salary)                    |
        FROM employees e3                        |
        WHERE e3.dept_id = e1.dept_id    --------┘
    )
);

Query execution fails for correlated subqueries with a depth exceeding 1, generation error as follows:

Schema error: No field named e1.dept_id. Did you mean 'e3.dept_id'?.

To Reproduce

CREATE TABLE employees (
    employee_id INTEGER,
    employee_name VARCHAR,
    dept_id INTEGER,
    salary DECIMAL
);

CREATE TABLE project_assignments (
    project_id INTEGER,
    employee_id INTEGER,
    priority INTEGER
);

SELECT e1.employee_name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2 
    WHERE e2.dept_id = e1.dept_id
);

SELECT e1.employee_name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2 
    WHERE e2.dept_id = e1.dept_id
    AND e2.salary > (
        SELECT AVG(e3.salary)
        FROM employees e3
        WHERE e3.dept_id = e1.dept_id
    )
);

Expected behavior

duckdb can run it well:

D SELECT e1.employee_name, e1.salary
  FROM employees e1
  WHERE e1.salary > (
      SELECT AVG(e2.salary)
      FROM employees e2 
      WHERE e2.dept_id = e1.dept_id
      AND e2.salary > (
          SELECT AVG(e3.salary)
          FROM employees e3
          WHERE e3.dept_id = e1.dept_id
      )
  );
┌───────────────┬───────────────┐
│ employee_name │    salary     │
│    varchar    │ decimal(18,3) │
├───────────────┴───────────────┤
│            0 rows             │
└───────────────────────────────┘

Additional context

No response

[irenjj]

take

[alamb]

I think support for this kind of query will require a more unified approach, such as the one described by @duongcongtoai in

• Decorrelate scalar subqueries with more complex filter expressions #14554 (comment)

[alamb]

There is a related discussion in discord here: https://discord.com/channels/885562378132000778/1372147109679075328/1372395271442665492

[alamb]

I recommend we continue the discussion on

• General framework to decorrelate the subqueries #5492