Support more temporary table construct

### 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_

Review issue #9323 and compare the DuckDB temporary-table syntax shown in the issue with DataFusion's current behavior. Investigate the SQL parser and schema-handling logic mentioned in the proposal, then define support and validation coverage for the requested structures.

Written by the indexing model from the issue text.