Describe the bug Given this slt <div class="snippet-clipboard-

This should be a duplicate of <a class="issue-link js-issue-link" data-error-text="Fai

Although theoretically <a class="issue-link js-issue-link" data-error-text="Failed to

I also found a different error for the following queries: <div class="snippet-clip

`Unnest` query does not allow one expression being referenced multiple times about datafusion HOT 6 OPEN

duongcongtoai commented on August 23, 2024

`Unnest` query does not allow one expression being referenced multiple times

from datafusion.

Comments (6)

jonahgao commented on August 23, 2024 1

This should be a duplicate of #6543

from datafusion.

jayzhan211 commented on August 23, 2024 1

I think we can change expr from Debug to Display with this issue together. This todo is added in #11797

Expr::Unnest(Unnest { expr }) => {
    // TODO: use Display instead of Debug, there is non-unique expression name in projection issue.
    write!(f, "UNNEST({expr})")
}

from datafusion.

duongcongtoai commented on August 23, 2024

Although theoretically #6543 can solve this ticket, but i think the bug presented here comes from the internal implementation inside try_process_unnest function itself, and can be fixed within this function

CREATE TABLE unnest_table
AS VALUES
    ([1,2,3]);

query TT
explain select unnest(column1), unnest(column1) + 1 from unnest_table;
----
Current plan generated (which causes error)
logical_plan
01)Projection: unnest(unnest_table.column1), unnest(unnest_table.column1) + Int64(1)
02)--Unnest: lists[unnest(unnest_table.column1)] structs[]
03)----Projection: unnest_table.column1 AS unnest(unnest_table.column1), unnest_table.column1 AS unnest(unnest_table.column1)
04)------TableScan: unnest_table projection=[column1]

Better plan generated
logical_plan
01)Projection: unnest(unnest_table.column1), unnest(unnest_table.column1) + Int64(1)
02)--Unnest: lists[unnest(unnest_table.column1)] structs[]
03)----Projection: unnest_table.column1 AS unnest(unnest_table.column1)
04)------TableScan: unnest_table projection=[column1]

We also avoid unnecessary columns generated in the intermediate projection step

from datafusion.

duongcongtoai commented on August 23, 2024

datafusion/datafusion/sql/src/select.rs

Line 344 in 58f79e1

let plan = LogicalPlanBuilder::from(intermediate_plan)

At this line, before create new projection nodes, we can deduplicate intermediate unnest expr within inner_projection_exprs

from datafusion.

duongcongtoai commented on August 23, 2024

take

from datafusion.

duongcongtoai commented on August 23, 2024

I also found a different error for the following queries:

select unnest(column1) + unnest(column1) from unnest_table;
External error: query failed: DataFusion error: Schema error: No field named unnest_table.column1. Valid fields are "unnest(unnest_table.column1)".

from datafusion.

Recommend Projects