Repository navigation
[dbolympics] Support Nested correlated subquery error with a depth exceeding 1 #15558
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
Activity
irenjj commentedon Apr 3, 2025
take
alamb commentedon Apr 17, 2025
I think support for this kind of query will require a more unified approach, such as the one described by @duongcongtoai in
alamb commentedon May 15, 2025
There is a related discussion in discord here: https://discord.com/channels/885562378132000778/1372147109679075328/1372395271442665492
alamb commentedon May 18, 2025
I recommend we continue the discussion on
[-]Nested correlated subquery error with a depth exceeding 1[/-][+]Support Nested correlated subquery error with a depth exceeding 1[/+]alamb commentedon Jan 14, 2026
I broke out supporting just planning (binding) the queries to a separate ticket
neilconway commentedon Apr 22, 2026
@irenjj Are you still working on this? I'm also looking at making improvements in this area.
duongcongtoai commentedon Apr 24, 2026
@neilconway at the moment datafusion cannot handle nested nested subqueries (even though the planning/binding stage already support this type of query as @alamb mentioned).
We actually created a POC of this but never manage to merge it into main branch.
I believe this is should be the latest (and humongous) PR that @irenjj and the team worked on: #19577
I'm breaking it down into separate phase, starting from this
neilconway commentedon Apr 24, 2026
@duongcongtoai I was thinking of adding support for multiple nesting levels to
OuterReferenceColumn(what Substrait callssteps_outor Postgres callsvarlevelsup), and then adjusting the existing decorrelation approach to decorrelate one level of subquery nesting at a time. I think this should be reasonably straightforward. I haven't had a chance to take a deep look at the depjoin stuff yet, but I wonder if this might be a useful complement to the depjoin work, even if we do eventually switch over to depjoin as the long-term approach to evaluating correlated subqueries at some point. I'm curious what you think, or if it would make more sense to just focus on depjoin / generalized decorrelation.duongcongtoai commentedon Apr 24, 2026
@neilconway i'm happy to take a look at your implementation.
I guess it can reuse some logic, if somehow you can reason out for a given outer_ref deep down the query plan trees, which node above "provides" the value for that expression/i.e the table provider for that expression, with some corner case like relation alias, column alias etc.
Another concern is that the generalized decorrelation follows the top-down decorrelation style (meaning the top queries are aware of which columns are referenced by either its 1-lv below subqueries or n-lv below subqueries), and the logical plan rewriting also happens from top to bottom.
[-]Support Nested correlated subquery error with a depth exceeding 1[/-][+][dbolympics] Support Nested correlated subquery error with a depth exceeding 1[/+]alamb commentedon Oct 24, 2026
Affects DB Olympics, see: #26661
alamb commentedon Oct 28, 2026
I think we should implement support support for this feature as part of the more general subquery decorrelation project described here
in my opinion WE SHOULD NOT try and extend the existing subquery rewrites to support this type of query as that risks introducing many more wrong results, see #5492 (comment)