Generate the common SQL for the unparsing result of the unnest

[goldmedal]

Is your feature request related to a problem or challenge?

After #15090, we remove the wildacrd expression from the logical plan layer. The unparser will generate the real column name for the select item. The unparsing result (#15090 (comment)) of an unnest plan would like as below:

SELECT "UNNEST(make_array(Int64(1),Int64(2),Int64(3)))" FROM UNNEST([1, 2, 3])

It's only executable for DataFusion. Other databases won't use this kind of column name.

Postgres and DuckDB using unnest as the column name.

psql=# select * from unnest(array[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

psql=# select unnest from unnest(array[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

BigQuery

select * from unnest(array[1,2,3])
---
f0_
1
2
3

However, we can't select f0_ directly.

select f0_ from unnest(array[1,2,3])
---
Unrecognized name: f0_ at [1:8]

Describe the solution you'd like

I think maybe we can unparse the unnest table factor to SubqueryAlias with a default column alias. For example

SELECT "UNNEST(make_array(Int64(1),Int64(2),Int64(3)))" FROM UNNEST([1, 2, 3]) as unnest_alias("UNNEST(make_array(Int64(1),Int64(2),Int64(3)))")

This way can keep the column name created by DataFusion. The SQL can also be executed by other databases.

I think @blaginin's work #14781 pretty matches this approach (#14781 (comment)). Maybe it can solve this issue.

Describe alternatives you've considered

No response

Additional context

No response

[blaginin]

I think @blaginin's work #14781 pretty matches this approach (#14781 (comment)). Maybe it can solve this issue.

I feel like more work may need to be required, see https://github.com/apache/datafusion/pull/15159/files/1cb70d76a250b3057f6286e551e98e0961ee2bd2..ef4a79dd160c452f8f92c82ac151131865c2e965#diff-4f75cc3209ff42bccbf093ac48a9b59979e26cfa692676400ed2a1e9c6fd0cd2 for example