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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
