Skip to content

Allow to unparse UNNEST plan back to a table function SQL text #13601

@goldmedal

Description

@goldmedal

Is your feature request related to a problem or challenge?

Given a SQL:

select * from unnest([1,2,3]) as t(c1)

DataFusion plans the unnest to Projection/Unnest/Projection pattern like

Projection: *
  SubqueryAlias: t
    Projection: UNNEST(make_array(Int64(1),Int64(2),Int64(3))) AS c1
      Projection: unnest_placeholder(make_array(Int64(1),Int64(2),Int64(3)),depth=1) AS UNNEST(make_array(Int64(1),Int64(2),Int64(3)))
        Unnest: lists[unnest_placeholder(make_array(Int64(1),Int64(2),Int64(3)))|depth=1] structs[]
          Projection: make_array(Int64(1), Int64(2), Int64(3)) AS unnest_placeholder(make_array(Int64(1),Int64(2),Int64(3)))
            EmptyRelation

If we try to unparse the plan back to the SQL, the result is

SELECT * FROM (SELECT UNNEST([1, 2, 3]) AS "UNNEST(make_array(Int64(1),Int64(2),Int64(3)))") AS t (c1)

However, some databases (e.g. BigQuery), only support using UNNEST as a table function.

Describe the solution you'd like

We can introduce a new option for the unparser Dialect

pub trait Dialect: Send + Sync {
    ...
    /// Allow to unparse the unnest plan as a table function.
    fn unnest_as_table_function(&self) -> bool {
        false
    }
    ...
}

If we found the pattern of plan like Projection/Unnest/Projection, we can try to unparse it to an ast::query::TableFactor::UNNEST.

Describe alternatives you've considered

No response

Additional context

We may need to ignore other instances of UNNEST usage, such as:

SELECT UNNEST(column1), UNNEST(column2) FROM (SELECT column1, column2 FROM xxx);

This issue aims to enable a roundtrip for the UNNEST table function.

Metadata

Metadata

Assignees

Labels

enhancementNew feature or request

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions