Support more temporary table construct

[Lordworms]

Is your feature request related to a problem or challenge?

When I was doing #9323 , I found that duckDB supports this syntax

which does not implemented in datafusion

I think we should support such structures

Describe the solution you'd like

I have not really thought about it. A rough solution for this should be adding a new table schema in the default schema and when we emit the subquery, we add a new entry to the default schema and then do the following steps? I think we should deduce whether an expression could be evaluated as a temp table or not(In SQLparser or not?) and then add schema to the default schema book. This might include changing SQLparser and lots of existing logics

Describe alternatives you've considered

No response

Additional context

No response

[jayzhan211]

Duckdb does not support table for all the function too, like array_to_string. I think we can do it for range

[alamb]

I think this type of construct is called a TableFunction and we can define them in DataFUsion

This is how we implement the parquet_metadata function in datafusion-cli: https://arrow.apache.org/datafusion/user-guide/cli.html

SELECT path_in_schema, row_group_id, row_group_num_rows, stats_min, stats_max, total_compressed_size
FROM parquet_metadata('hits.parquet')
WHERE path_in_schema = '"WatchID"'
LIMIT 3;

+----------------+--------------+--------------------+---------------------+---------------------+-----------------------+
| path_in_schema | row_group_id | row_group_num_rows | stats_min           | stats_max           | total_compressed_size |
+----------------+--------------+--------------------+---------------------+---------------------+-----------------------+
| "WatchID"      | 0            | 450560             | 4611687214012840539 | 9223369186199968220 | 3883759               |
| "WatchID"      | 1            | 612174             | 4611689135232456464 | 9223371478009085789 | 5176803               |
| "WatchID"      | 2            | 344064             | 4611692774829951781 | 9223363791697310021 | 3031680               |
+----------------+--------------+--------------------+---------------------+---------------------+-----------------------+
3 rows in set. Query took 0.053 seconds.

Thus I think for this particular issue, we could implement a Table function rangeand register it via https://docs.rs/datafusion/latest/datafusion/execution/context/struct.SessionContext.html#method.register_udtf

[Lordworms]

I think this type of construct is called a TableFunction and we can define them in DataFUsion

This is how we implement the parquet_metadata function in datafusion-cli: https://arrow.apache.org/datafusion/user-guide/cli.html

SELECT path_in_schema, row_group_id, row_group_num_rows, stats_min, stats_max, total_compressed_size
FROM parquet_metadata('hits.parquet')
WHERE path_in_schema = '"WatchID"'
LIMIT 3;

+----------------+--------------+--------------------+---------------------+---------------------+-----------------------+
| path_in_schema | row_group_id | row_group_num_rows | stats_min           | stats_max           | total_compressed_size |
+----------------+--------------+--------------------+---------------------+---------------------+-----------------------+
| "WatchID"      | 0            | 450560             | 4611687214012840539 | 9223369186199968220 | 3883759               |
| "WatchID"      | 1            | 612174             | 4611689135232456464 | 9223371478009085789 | 5176803               |
| "WatchID"      | 2            | 344064             | 4611692774829951781 | 9223363791697310021 | 3031680               |
+----------------+--------------+--------------------+---------------------+---------------------+-----------------------+
3 rows in set. Query took 0.053 seconds.

Thus I think for this particular issue, we could implement a Table function rangeand register it via https://docs.rs/datafusion/latest/datafusion/execution/context/struct.SessionContext.html#method.register_udtf

Got it