[I] Unexpected set expression query planning error [datafusion]

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

gruuya opened a new issue, #20818:
URL: https://github.com/apache/datafusion/issues/20818

   ### Describe the bug
   
   When a query involving set expressions (UNION, EXCEPT, INTERSECT):
   
   1.  provides field names in the first SELECT
   2. but some other SELECTs don't, and
   3. these also contain more than one literal of the same value
   
   then the projection validation logic (`validate_unique_names`) will error out.
   
   ### To Reproduce
   
   ```sql
   > SELECT 1 c1, 0 c2, 0 c3 UNION ALL SELECT 2, 0, 0;
   Error during planning: Projections require unique expression names but the expression "Int64(0)" at position 1 and "Int64(0)" at position 2 have the same name. Consider aliasing ("AS") one of them.
   ```
   
   This is all the more frustrating since the field names for the all other SELECTs but the first are actually discarded in the final output, so they're effectively a throw-away input to abide by the projection validation logic
   ```sql
   > SELECT 1 c1, 0 c2, 0 c3 UNION ALL SELECT 2 as a, 0 as b, 0 as c;
   +----+----+----+
   | c1 | c2 | c3 |
   +----+----+----+
   | 2  | 0  | 0  |
   | 1  | 0  | 0  |
   +----+----+----+
   2 row(s) fetched.
   ```
   
   ### Expected behavior
   
   ```sql
   >  SELECT 1 c1, 0 c2, 0 c3 UNION ALL SELECT 2, 0, 0;
   +----+----+----+
   | c1 | c2 | c3 |
   +----+----+----+
   | 2  | 0  | 0  |
   | 1  | 0  | 0  |
   +----+----+----+
   2 row(s) fetched. 
   ```
   
   ### Additional context
   
   This works as expected in Postgres
   ```sql
   postgres@localhost:postgres> SELECT 1 c1, 0 c2, 0 c3 UNION ALL SELECT 2, 0, 0;
   +----+----+----+
   | c1 | c2 | c3 |
   |----+----+----|
   | 1  | 0  | 0  |
   | 2  | 0  | 0  |
   +----+----+----+
   SELECT 2
   ```
   and DuckDB
   ```sql
   D SELECT 1 c1, 0 c2, 0 c3 UNION ALL SELECT 2, 0, 0;
   ┌───────┬───────┬───────┐
   │  c1   │  c2   │  c3   │
   │ int32 │ int32 │ int32 │
   ├───────┼───────┼───────┤
   │     1 │     0 │     0 │
   │     2 │     0 │     0 │
   └───────┴───────┴───────┘
   ```


-- 
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@datafusion.apache.org.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Unexpected set expression query planning error [datafusion]

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

jonahgao closed issue #20818: Unexpected set expression query planning error
URL: https://github.com/apache/datafusion/issues/20818


-- 
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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org