Unnest query does not allow one expression being referenced multiple times

[duongcongtoai]

Describe the bug

Given this slt

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

select unnest(column1), unnest(column1) + 1 from unnest_table;

Datafusion throws error

External error: query failed: DataFusion error: Error during planning: Projections require unique expression names but the expression "unnest_table.column1 AS unnest(unnest_table.column1)" at position 0 and "unnest_table.column1 AS unnest(unnest_table.column1)" at position 1 have the same name. Consider aliasing ("AS") one of them.

To Reproduce

No response

Expected behavior

unnest(expr) can be referenced multiple times within select statement

Additional context

No response

[jonahgao]

This should be a duplicate of #6543

[duongcongtoai]

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

[duongcongtoai]

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

[duongcongtoai]

take

[duongcongtoai]

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)".

[jayzhan211]

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})")
}