Skip to content

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

Closed

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.

Activity

  1. jatin510 commented on Nov 29, 2024

    @jatin510
    Contributor

    take

  2. goldmedal commented on Dec 5, 2024

    @goldmedal
    ContributorAuthor

    Hi @jatin510
    Have you started working on this issue? I noticed you’ve taken on other tasks, and I’m unsure of your priorities. If not, I’d like to take over since I need this feature for my downstream project.

  3. jatin510 commented on Dec 5, 2024

    @jatin510
    Contributor

    please go ahead @goldmedal

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

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