A corner bug in union

[xudong963]

Describe the bug
When union some select constant1, contant2, ... similar queries, union can't produce the result.

To Reproduce

❯ SELECT 1 as col1, 'one' as col2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

Plan("UNION ALL schemas are expected to be the same")

❯ SELECT 1 , 'one'
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

Plan("UNION ALL schemas are expected to be the same")

Expected behavior
Let' see results from postgres:

postgres=# SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
 column1 | column2
---------+---------
       1 | one
       2 | two
       3 | three
(3 rows)

postgres=# SELECT 1 , 'one'
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
 ?column? | ?column?
----------+----------
        1 | one
        2 | two
        3 | three
(3 rows)

Additional context
I have done an easy analysis, the followings are my thought to solve the issue:

1. We should fix the default name when the expr in projection doesn't have a name. Let's see an example. select a from table1, a is the column from table1, so it naturally has a name a, but for select 1, 1 isn't a column, it's a Value expr, so its name will be Int64(1) in current datatfusion's implementation, the related code is https://github.com/apache/arrow-datafusion/blob/7be0e268a69ffecbf06823c98ca572733dddb29e/datafusion/src/physical_plan/planner.rs#L91. So the first thing to solve the issue is to rethink how to create default name. I think we can keep consistent with postgres, such as ?column?.
2. Currently, DF judges if the logical plans in union have the same schema by arrow schema's field name https://github.com/apache/arrow-datafusion/blob/7be0e268a69ffecbf06823c98ca572733dddb29e/datafusion-common/src/dfschema.rs#L251. After finishing 1, we can add a condition, if the col name is ?column?, we decide if logical plans have the same schema by arrow schema's filed datatype.

cc @alamb @houqp

[alamb]

Nice find @xudong963

Currently, DF judges if the logical plans in union have the same schema by arrow schema's field name

This sounds like the problem to me -- perhaps we need to add a special "does this schema match for the purposes of union" type check 🤔

Another thought I had would be to add LogicalPlan::Projection to map the names of union inputs to be compatible (assuming that the types are the same). Something like this perhaps:

Plan now;

Union --> results in error because `x` not the same as `a` and `y` not the same as `b`
    ... original input for (x,y)
    ... original input  for (a, b)

use a Projection to remap the names:

Union
  Projection(x as "column1", y as "column2")
    ... original input for (x,y)
  Projection(a as "column1", b as "column2")
    ... original input  for (a, b)

[houqp]

I agree, if this is only happening to union, realigning the column names for anonymous columns during union planning would be a better/simpler fix.

[Ted-Jiang]

@xudong963 @alamb in pg

postgres=# select 1 as a union all select 2 as b;
 a
---
 1
 2
(2 rows)

in DF
https://github.com/apache/arrow-datafusion/blob/0194a27d0f68108c20b12456e9dafadbb170085e/datafusion/src/logical_plan/builder.rs#L1049

then
https://github.com/apache/arrow-datafusion/blob/634252b44cec8dc904e48926d0aa54feeb4d48af/datafusion-common/src/dfschema.rs#L278-L282
we check field name, according to the below link should we change to check the filed type are compatible?

https://www.postgresql.org/docs/13/sql-select.html#:~:text=specified%20with%20DISTINCT.-,UNION%20Clause,a%20UNION%20result%20or%20for%20any%20input%20of%20a%20UNION.,-INTERSECT%20Clause

[alamb]

we check field name, according to the below link should we change to check the filed type are compatible?

@Ted-Jiang makes sense to me

[WinkerDu]

@xudong963 Sorry for late report to this issue since I was quite busy last week.
I have a raw fix #2108 :

1. except for expression Alias and Column in Projection under Union, other expressions should be aliased with column index like column0
2. change field names match check to field type compatible check.