Repository navigation
Support correlated UNNEST in subquery #11773
Description
Describe the bug
I cannot use UNNEST inside a subquery:
> SELECT id, (SELECT * FROM UNNEST(arr) LIMIT 1) FROM
(
SELECT 1 id, array [1, 2] arr
UNION
SELECT 2 id, array [] arr
) rows;
check_analyzed_plan
caused by
Error during planning: Unsupported operator in the subquery plan.Nor can I use UNNEST in a subquery on the right hand side of EXISTS:
> SELECT id FROM
(
SELECT 1 id, array [1, 2] arr
UNION
SELECT 2 id, array [] arr
) rows
WHERE EXISTS (SELECT 1 FROM UNNEST(arr));
check_analyzed_plan
caused by
Error during planning: Unsupported operator in the subquery plan.To Reproduce
The queries above can be used in datafusion-cli.
Expected behavior
The queries above should ideally work like they do in Postgres (with some type annotations):
# SELECT id, (SELECT * FROM UNNEST(arr) LIMIT 1) FROM (SELECT 1 id, array [1, 2] arr UNION SELECT 2 id, array [] :: integer[] arr) rows;
id | unnest
----+--------
1 | 1
2 |
(2 rows)
# SELECT id FROM (SELECT 1 id, array [1, 2] arr UNION SELECT 2 id, array [] :: integer[] arr) rows WHERE EXISTS (SELECT 1 FROM UNNEST(arr));
id
----
1
(1 row)Additional context
Sorry if this is already tracked. It seems like there is a missing case in this function, and if this is a simple fix then I'd be happy to try to make a PR:
Thanks!
Reacted by << and Jonah Gao
Activity
jonahgao commentedon Aug 21, 2024
After fixing that function, it will give the following error.
Similar to
select (select * from values(a) limit 1) from (values(1) t(a)).DataFusion's current support for subqueries is limited, so fixing it might require more work.
findepi commentedon Feb 4, 2025
This currently compiles, but doesn't run
This error looks like same as /similar to #13190
I guess the original problem was fixed in #13523
This still doesn't work.
Supporting unnest decorelation (e.g. like in Trino) would indeed be cool.
[-]UNNEST cannot appear inside a subquery[/-][+]Support correlated UNNEST in subquery[/+]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)