Skip to content

Unnest relation can't accept a field from its join table #13659

New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Closed
goldmedal opened this issue Dec 5, 2024 · 7 comments
Closed

Unnest relation can't accept a field from its join table #13659

goldmedal opened this issue Dec 5, 2024 · 7 comments
Labels
bug Something isn't working

Comments

@goldmedal
Copy link
Contributor

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

@goldmedal goldmedal added the bug Something isn't working label Dec 5, 2024
@jonahgao
Copy link
Member

jonahgao commented Dec 5, 2024

This should be a LATERAL JOIN, with the LATERAL keyword omitted. We do not support it yet. #10048

As stated in the PostgreSQL documentation.

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional;

@goldmedal
Copy link
Contributor Author

This should be a LATERAL JOIN, with the LATERAL keyword omitted. We do not support it yet. #10048

As stated in the PostgreSQL documentation.

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

@rkrishn7
Copy link
Contributor

rkrishn7 commented Dec 8, 2024

Hello! I'm happy to take on the logical planning piece of this.

@alamb
Copy link
Contributor

alamb commented Dec 13, 2024

Update: moved to #10048 (comment)

@alamb
Copy link
Contributor

alamb commented Dec 13, 2024

@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)

@alamb
Copy link
Contributor

alamb commented Dec 13, 2024

After some more research, I think this ticket is now a dupe of #10048

@goldmedal
Copy link
Contributor Author

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.

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

4 participants