jonahgao commented on code in PR #11961:
URL: https://github.com/apache/datafusion/pull/11961#discussion_r1714938242
##########
datafusion/sqllogictest/test_files/type_coercion.slt:
##########
@@ -49,3 +49,179 @@ select interval '1 month' - '2023-05-01'::date;
# interval - timestamp
query error DataFusion error: Error during planning: Cannot coerce arithmetic
expression Interval\(MonthDayNano\) \- Timestamp\(Nanosecond, None\) to valid
types
SELECT interval '1 month' - '2023-05-01 12:30:00'::timestamp;
+
+
+####################################
+## Test type coercion with UNIONs ##
+####################################
+
+# Disable optimizer to test only the analyzer with type coercion
+statement ok
+set datafusion.optimizer.max_passes = 0;
+
+statement ok
+set datafusion.explain.logical_plan_only = true;
+
+# Create test table
+statement ok
+CREATE TABLE orders(
+ order_id INT UNSIGNED NOT NULL,
+ customer_id INT UNSIGNED NOT NULL,
+ o_item_id VARCHAR NOT NULL,
+ qty INT NOT NULL,
+ price DOUBLE NOT NULL,
+ delivered BOOLEAN NOT NULL
+);
+
+# union_different_num_columns_error() / UNION
+query error Error during planning: Union schemas have different number of
fields: query 1 has 1 fields whereas query 2 has 2 fields
+SELECT order_id FROM orders UNION SELECT customer_id, o_item_id FROM orders
+
+# union_different_num_columns_error() / UNION ALL
+query error Error during planning: Union schemas have different number of
fields: query 1 has 1 fields whereas query 2 has 2 fields
+SELECT order_id FROM orders UNION ALL SELECT customer_id, o_item_id FROM orders
+
+# union_with_different_column_names()
+query TT
+EXPLAIN SELECT order_id from orders UNION ALL SELECT customer_id FROM orders
+----
+logical_plan
+01)Union
+02)--Projection: orders.order_id
+03)----TableScan: orders
+04)--Projection: orders.customer_id AS order_id
+05)----TableScan: orders
+
+# union_values_with_no_alias()
+query TT
+EXPLAIN SELECT 1, 2 UNION ALL SELECT 3, 4
+----
+logical_plan
+01)Union
+02)--Projection: Int64(1) AS Int64(1), Int64(2) AS Int64(2)
+03)----EmptyRelation
+04)--Projection: Int64(3) AS Int64(1), Int64(4) AS Int64(2)
+05)----EmptyRelation
+
+# union_with_incompatible_data_type()
+query error Error during planning: UNION Column 'Int64\(1\)' \(type: Int64\)
is not compatible with other type: Interval\(MonthDayNano\)
+SELECT interval '1 year 1 day' UNION ALL SELECT 1
+
+# union_with_different_decimal_data_types()
+query TT
+EXPLAIN SELECT 1 a UNION ALL SELECT 1.1 a
+----
+logical_plan
+01)Union
+02)--Projection: CAST(Int64(1) AS Float64) AS a
+03)----EmptyRelation
+04)--Projection: Float64(1.1) AS a
+05)----EmptyRelation
+
+# union_with_null()
+query TT
+EXPLAIN SELECT NULL a UNION ALL SELECT 1.1 a
+----
+logical_plan
+01)Union
+02)--Projection: CAST(NULL AS Float64) AS a
+03)----EmptyRelation
+04)--Projection: Float64(1.1) AS a
+05)----EmptyRelation
+
+# union_with_float_and_string()
+query TT
+EXPLAIN SELECT 'a' a UNION ALL SELECT 1.1 a
+----
+logical_plan
+01)Union
+02)--Projection: Utf8("a") AS a
+03)----EmptyRelation
+04)--Projection: CAST(Float64(1.1) AS Utf8) AS a
+05)----EmptyRelation
+
+# union_with_multiply_cols()
+query TT
+EXPLAIN SELECT 'a' a, 1 b UNION ALL SELECT 1.1 a, 1.1 b
+----
+logical_plan
+01)Union
+02)--Projection: Utf8("a") AS a, CAST(Int64(1) AS Float64) AS b
+03)----EmptyRelation
+04)--Projection: CAST(Float64(1.1) AS Utf8) AS a, Float64(1.1) AS b
+05)----EmptyRelation
+
+# sorted_union_with_different_types_and_group_by()
+query TT
+EXPLAIN SELECT a FROM (select 1 a) x GROUP BY 1
+ UNION ALL
+(SELECT a FROM (select 1.1 a) x GROUP BY 1) ORDER BY 1
+----
+logical_plan
+01)Sort: x.a ASC NULLS LAST
+02)--Union
+03)----Projection: CAST(x.a AS Float64) AS a
+04)------Aggregate: groupBy=[[x.a]], aggr=[[]]
+05)--------SubqueryAlias: x
+06)----------Projection: Int64(1) AS a
+07)------------EmptyRelation
+08)----Projection: x.a
+09)------Aggregate: groupBy=[[x.a]], aggr=[[]]
+10)--------SubqueryAlias: x
+11)----------Projection: Float64(1.1) AS a
+12)------------EmptyRelation
+
+# union_with_binary_expr_and_cast()
+query TT
+EXPLAIN SELECT cast(0.0 + a as integer) FROM (select 1 a) x GROUP BY 1
+ UNION ALL
+(SELECT 2.1 + a FROM (select 1 a) x GROUP BY 1)
+----
+logical_plan
+01)Union
+02)--Projection: CAST(Float64(0) + x.a AS Float64) AS Float64(0) + x.a
+03)----Aggregate: groupBy=[[CAST(Float64(0) + CAST(x.a AS Float64) AS
Int32)]], aggr=[[]]
+04)------SubqueryAlias: x
+05)--------Projection: Int64(1) AS a
+06)----------EmptyRelation
+07)--Projection: Float64(2.1) + x.a AS Float64(0) + x.a
+08)----Aggregate: groupBy=[[Float64(2.1) + CAST(x.a AS Float64)]], aggr=[[]]
Review Comment:
This is the only difference I observed from the previous test results
because type coercion was performed, and `x.a` had an additional cast.
--
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-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
