[GitHub] [arrow-datafusion] alamb commented on issue #6492: Mismatch in MemTable (Select Into with aggregate window functions having no alias)

["alamb (via GitHub)" <gi...@apache.org>]

alamb commented on issue #6492:
URL: https://github.com/apache/arrow-datafusion/issues/6492#issuecomment-1572539079

   Hi @berkaysynnada  -- 
   
   Here is a small reproducer:
   ```sql
   ❯ create table foo (x int) as values (1);
   0 rows in set. Query took 0.001 seconds.
   ❯ explain select first_value(x) OVER () INTO bar from foo ;
   +--------------+------------------------------------------------------------------------------------------------------------+
   | plan_type    | plan                                                                                                       |
   +--------------+------------------------------------------------------------------------------------------------------------+
   | logical_plan | CreateMemoryTable: Bare { table: "bar" }                                                                   |
   |              |   Projection: FIRST_VALUE(foo.x) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING                  |
   |              |     WindowAggr: windowExpr=[[FIRST_VALUE(foo.x) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING]] |
   |              |       TableScan: foo projection=[x]                                                                        |
   +--------------+------------------------------------------------------------------------------------------------------------+
   1 row in set. Query took 0.001 seconds.
   ❯  select first_value(x) OVER () INTO bar from foo ;
   Error during planning: Mismatch between schema and batches
   ```
   
   
   I wonder if you can solve this with an explicit alias rather than changing how the display names work.
   
   So when the `CreateMemoryTable` is planned, add explicit aliases (maybe select "first_value(x) OVER () INTO bar from foo" ;
   ```
   +--------------+------------------------------------------------------------------------------------------------------------+
   | plan_type    | plan                                                                                                       |
   +--------------+------------------------------------------------------------------------------------------------------------+
   | logical_plan | CreateMemoryTable: Bare { table: "bar" }                                                                   |
   |              |   Projection: FIRST_VALUE(foo.x) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING                  
    AS "first_value(x)"   <---- add this alias??|
   |              |     WindowAggr: windowExpr=[[FIRST_VALUE(foo.x) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING]] |
   |              |       TableScan: foo projection=[x]                                                                        |
   +--------------+------------------------------------------------------------------------------------------------------------+
   ```
   
   For the record, here is what postgres does:
   
   ```shell
   postgres=# select first_value(x) OVER () INTO bar from foo ;
   SELECT 1
   postgres=# select * from bar;
    first_value
   -------------
              1
   (1 row)
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org