You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "goldmedal (via GitHub)" <gi...@apache.org> on 2024/12/05 09:20:52 UTC
[I] Unnest relation can't accept a field from its join table [datafusion]
goldmedal opened a new issue, #13659:
URL: https://github.com/apache/datafusion/issues/13659
### Describe the bug
There is a common usage of SQL to operate the nested data that is allowed by Postgres and DuckDB but DataFusion can't execute.
## Postgres
```
postgres=# create table unnest_test (c1 int[]);
CREATE TABLE
postgres=# insert into unnest_test values (ARRAY [1,2,3]);
INSERT 0 1
postgres=# select * from unnest_test u, unnest(u.c1);
c1 | unnest
---------+--------
{1,2,3} | 1
{1,2,3} | 2
{1,2,3} | 3
(3 rows)
```
## DuckDB
```
D create table t1(c1 int[]);
D insert into t1 values ([1,2,3]);
D select * from t1, unnest(t1.c1);
┌───────────┬───────┐
│ c1 │ c1 │
│ int32[] │ int32 │
├───────────┼───────┤
│ [1, 2, 3] │ 1 │
│ [1, 2, 3] │ 2 │
│ [1, 2, 3] │ 3 │
└───────────┴───────┘
```
### To Reproduce
It can be reproduced by the following SQL (I just added them in the sqlogicitests `unnest.slt` and ran the test)
```
statement ok
create table t1(c1 array<int>);
statement ok
insert into t1 values ([1,2,3]);
query ?I
select * from t1, unnest(t1.c1)
----
[1, 2, 3] 1
[1, 2, 3] 2
[1, 2, 3] 3
```
Then, it will throw the error:
```
Running "unnest.slt"
External error: query failed: DataFusion error: Schema error: No field named t1.c1.
[SQL] select * from t1, unnest(t1.c1)
```
### Expected behavior
The test mentioned above should pass.
### 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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2541722186
Postgres appears to simply plan this type of query as a NestedLoops join:
```sql
Nested Loop (cost=0.00..295.60 rows=13600 width=36)
Output: u.c1, unnest.unnest
-> Seq Scan on public.unnest_test u (cost=0.00..23.60 rows=1360 width=32)
Output: u.c1
-> Function Scan on pg_catalog.unnest (cost=0.00..0.10 rows=10 width=4)
Output: unnest.unnest
Function Call: unnest(u.c1)
```
🤔
I think the postgres join operator re-evaluates the right input (`pg_catalog.unnest`) on each input row to the join which is how the lateral joins can refer to the outer query
The current DataFusion join operators don't run the input over and over again -- they normally run each input and then combine them (they don't restart inputs)
So in a query like
```sql
select * from t1, unnest(t1.c1)
```
I think we would need a (new) type of JoinExec that actually does run the outer query (aka `unnest(t1.c1)` for each input row of `t1`) 🤔
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2544634311
> After some more research, I think this ticket is now a dupe of #10048
Agreed. The logical plan part has been finished. Let's close this issue and move to #10048.
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2541731017
After some more research, I think this ticket is now a dupe of https://github.com/apache/datafusion/issues/10048
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "rkrishn7 (via GitHub)" <gi...@apache.org>.
rkrishn7 commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2526423117
Hello! I'm happy to take on the logical planning piece of this.
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "jonahgao (via GitHub)" <gi...@apache.org>.
jonahgao commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2520459565
This should be a LATERAL JOIN, with the LATERAL keyword omitted. We do not support it yet.
As stated in the PostgreSQL [documentation](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL).
> Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional;
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2541724950
@goldmedal also pointed out this may be related to supporting table functions that take columns as arguments (DataFusion only support taking constants at the moment)
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal commented on issue #13659:
URL: https://github.com/apache/datafusion/issues/13659#issuecomment-2520565398
> This should be a LATERAL JOIN, with the LATERAL keyword omitted. We do not support it yet. #10048
>
> As stated in the PostgreSQL [documentation](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL).
>
> > Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional;
Thanks for the information. Indeed, this is an implicit LATERAL join. I tried to use explicit LATERAL for it (I think they are equal 🤔 ) like
```
select * from t1, LATERAL (select unnest(t1.c1))
```
and get the error:
```
Error: This feature is not implemented: Physical plan does not support logical expression OuterReferenceColumn(List(Field { name: "item", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), Column { relation: Some(Bare { table: "t1" }), name: "c1" })
backtrace: 0: std::backtrace_rs::backtrace::libunwind::trace
at /rustc/90b35a6239c3d8bdabc530a6a0816f7ff89a0aaf/library/std/src/../../backtrace/src/backtrace/libunwind.rs:116:5
1: std::backtrace_rs::backtrace::trace_unsynchronized
at /rustc/90b35a6239c3d8bdabc530a6a0816f7ff89a0aaf/library/std/src/../../backtrace/src/backtrace/mod.rs:66:5
2: std::backtrace::Backtrace::create
at /rustc/90b35a6239c3d8bdabc530a6a0816f7ff89a0aaf/library/std/src/backtrace.rs:331:13
3: std::backtrace::Backtrace::capture
at /rustc/90b35a6239c3d8bdabc530a6a0816f7ff89a0aaf/library/std/src/backtrace.rs:296:9
4: datafusion_common::error::DataFusionError::get_back_trace
at ./datafusion/common/src/error.rs:398:30
5: datafusion_physical_expr::planner::create_physical_expr
at ./datafusion/physical-expr/src/planner.rs:365:13
6: datafusion_physical_expr::planner::create_physical_expr
at ./datafusion/physical-expr/src/planner.rs:115:16
```
It seems that the physical plan isn't ready 🤔
I think there are two issues:
- Plan the implicit LATERAL for unnest
- Support the physical plan for LATERAL JOIN
--
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] Unnest relation can't accept a field from its join table [datafusion]
Posted by "goldmedal (via GitHub)" <gi...@apache.org>.
goldmedal closed issue #13659: Unnest relation can't accept a field from its join table
URL: https://github.com/apache/datafusion/issues/13659
--
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