Open
Description
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