You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "irenjj (via GitHub)" <gi...@apache.org> on 2025/04/03 10:55:08 UTC
[I] Nested correlated subquery error with a depth exceeding 1 [datafusion]
irenjj opened a new issue, #15558:
URL: https://github.com/apache/datafusion/issues/15558
### 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
_No response_
### Expected behavior
_No response_
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "duongcongtoai (via GitHub)" <gi...@apache.org>.
duongcongtoai commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-4314335665
@neilconway i'm happy to take a look at your implementation.
```
but I wonder if this might be a useful complement to the depjoin work
```
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-2775368757
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-2888977753
I recommend we continue the discussion on
- https://github.com/apache/datafusion/issues/5492
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "duongcongtoai (via GitHub)" <gi...@apache.org>.
duongcongtoai commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-4313853085
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: https://github.com/apache/datafusion/pull/19577
I'm breaking it down into separate phase, starting from [this](https://github.com/apache/datafusion/pull/21555)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-2813832115
I think support for this kind of query will require a more unified approach, such as the one described by @duongcongtoai in
- https://github.com/apache/datafusion/issues/14554#issuecomment-2798943345
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [dbolympics] Support Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-6384961870
Affects DB Olympics, see: https://github.com/apache/datafusion/issues/26661
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-3750980755
I broke out supporting just planning (binding) the queries to a separate ticket
- https://github.com/apache/datafusion/issues/19816
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-4314099270
@duongcongtoai I was thinking of adding support for multiple nesting levels to `OuterReferenceColumn` (what Substrait calls `steps_out` or Postgres calls `varlevelsup`), 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Support Nested correlated subquery error with a depth exceeding 1 [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on issue #15558:
URL: https://github.com/apache/datafusion/issues/15558#issuecomment-4298182511
@irenjj Are you still working on this? I'm also looking at making improvements in this area.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org