You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Describe the bug
When union some select constant1, contant2, ... similar queries, union can't produce the result.
To Reproduce
❯ SELECT1as col1, 'one'as col2
UNION ALLSELECT2, 'two'UNION ALLSELECT3, 'three';
Plan("UNION ALL schemas are expected to be the same")
❯ SELECT1 , 'one'UNION ALLSELECT2, 'two'UNION ALLSELECT3, '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 column2UNION ALLSELECT2, 'two'UNION ALLSELECT3, 'three';
column1 | column2
---------+---------1 | one
2 | two
3 | three
(3 rows)
postgres=# SELECT 1 , 'one'UNION ALLSELECT2, 'two'UNION ALLSELECT3, '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:
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?.
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)
Activity
alamb commentedon Feb 14, 2022
Nice find @xudong963
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::Projectionto map the names of union inputs to be compatible (assuming that the types are the same). Something like this perhaps:Plan now;
use a
Projectionto remap the names:houqp commentedon Feb 22, 2022
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 commentedon Mar 25, 2022
@xudong963 @alamb in pg
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 commentedon Mar 25, 2022
@Ted-Jiang makes sense to me
WinkerDu commentedon Mar 28, 2022
@xudong963 Sorry for late report to this issue since I was quite busy last week.
I have a raw fix #2108 :
AliasandColumninProjectionunderUnion, other expressions should be aliased with column index likecolumn0