paf31 opened a new issue, #11773:
URL: https://github.com/apache/datafusion/issues/11773
### Describe the bug
I cannot use `UNNEST` inside a subquery:
```sql
> 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`:
```sql
> 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):
```sql
# 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:
https://github.com/apache/datafusion/blob/ae2ca6a0e21b77bba1ac40ea6ee059e47d0791e0/datafusion/optimizer/src/analyzer/subquery.rs#L233
Thanks!
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
