This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new f510800ca2 Add more tests showing coercing behavior with literals
(#14270)
f510800ca2 is described below
commit f510800ca2abe797fd947c26e20185526edd0a71
Author: Andrew Lamb <and...@nerdnetworks.org>
AuthorDate: Sun Jan 26 06:47:01 2025 -0500
Add more tests showing coercing behavior with literals (#14270)
* Add more tests showing coercing behavior with literals
* remove unecessary change
* Add tests for comparisons
---
datafusion/sqllogictest/test_files/operator.slt | 250 ++++++++++++++++++++++++
1 file changed, 250 insertions(+)
diff --git a/datafusion/sqllogictest/test_files/operator.slt
b/datafusion/sqllogictest/test_files/operator.slt
index ac977a0c51..83bd1d7ee3 100644
--- a/datafusion/sqllogictest/test_files/operator.slt
+++ b/datafusion/sqllogictest/test_files/operator.slt
@@ -33,6 +33,8 @@ SELECT
arrow_cast(1.25, 'Decimal128(5, 2)') as decimal
;
+############### Addition ###############
+
# Plus with the same operand type, expect the same output type
# except for decimal which is promoted to the highest precision
query TTTTTTTTTTT
@@ -52,6 +54,43 @@ from numeric_types;
----
Int8 Int16 Int32 Int64 UInt8 UInt16 UInt32 UInt64 Float32 Float64
Decimal128(6, 2)
+# Plus with literal integer
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 + 2),
+ arrow_typeof(int16 + 2),
+ arrow_typeof(int32 + 2),
+ arrow_typeof(int64 + 2),
+ arrow_typeof(uint8 + 2),
+ arrow_typeof(uint16 + 2),
+ arrow_typeof(uint32 + 2),
+ arrow_typeof(uint64 + 2),
+ arrow_typeof(float32 + 2),
+ arrow_typeof(float64 + 2),
+ arrow_typeof(decimal + 2)
+from numeric_types;
+----
+Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Float32 Float64 Decimal128(23,
2)
+
+# Plus with literal decimal
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 + 2.0),
+ arrow_typeof(int16 + 2.0),
+ arrow_typeof(int32 + 2.0),
+ arrow_typeof(int64 + 2.0),
+ arrow_typeof(uint8 + 2.0),
+ arrow_typeof(uint16 + 2.0),
+ arrow_typeof(uint32 + 2.0),
+ arrow_typeof(uint64 + 2.0),
+ arrow_typeof(float32 + 2.0),
+ arrow_typeof(float64 + 2.0),
+ arrow_typeof(decimal + 2.0)
+from numeric_types;
+----
+Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64
Float64 Float64
+
+############### Subtraction ###############
# Minus with the same operand type, expect the same output type
# except for decimal which is promoted to the highest precision
@@ -72,6 +111,44 @@ from numeric_types;
----
Int8 Int16 Int32 Int64 UInt8 UInt16 UInt32 UInt64 Float32 Float64
Decimal128(6, 2)
+# Minus with literal integer
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 - 2),
+ arrow_typeof(int16 - 2),
+ arrow_typeof(int32 - 2),
+ arrow_typeof(int64 - 2),
+ arrow_typeof(uint8 - 2),
+ arrow_typeof(uint16 - 2),
+ arrow_typeof(uint32 - 2),
+ arrow_typeof(uint64 - 2),
+ arrow_typeof(float32 - 2),
+ arrow_typeof(float64 - 2),
+ arrow_typeof(decimal - 2)
+from numeric_types;
+----
+Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Float32 Float64 Decimal128(23,
2)
+
+# Minus with literal decimal
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 - 2.0),
+ arrow_typeof(int16 - 2.0),
+ arrow_typeof(int32 - 2.0),
+ arrow_typeof(int64 - 2.0),
+ arrow_typeof(uint8 - 2.0),
+ arrow_typeof(uint16 - 2.0),
+ arrow_typeof(uint32 - 2.0),
+ arrow_typeof(uint64 - 2.0),
+ arrow_typeof(float32 - 2.0),
+ arrow_typeof(float64 - 2.0),
+ arrow_typeof(decimal - 2.0)
+from numeric_types;
+----
+Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64
Float64 Float64
+
+############### Multiplication ###############
+
# Multiply with the same operand type, expect the same output type
# except for decimal which is promoted to the highest precision
query TTTTTTTTTTT
@@ -91,6 +168,45 @@ from numeric_types;
----
Int8 Int16 Int32 Int64 UInt8 UInt16 UInt32 UInt64 Float32 Float64
Decimal128(11, 4)
+# Multiply with literal integer
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 * 2),
+ arrow_typeof(int16 * 2),
+ arrow_typeof(int32 * 2),
+ arrow_typeof(int64 * 2),
+ arrow_typeof(uint8 * 2),
+ arrow_typeof(uint16 * 2),
+ arrow_typeof(uint32 * 2),
+ arrow_typeof(uint64 * 2),
+ arrow_typeof(float32 * 2),
+ arrow_typeof(float64 * 2),
+ arrow_typeof(decimal * 2)
+from numeric_types;
+----
+Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Float32 Float64 Decimal128(26,
2)
+
+# Multiply with literal decimal
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 * 2.0),
+ arrow_typeof(int16 * 2.0),
+ arrow_typeof(int32 * 2.0),
+ arrow_typeof(int64 * 2.0),
+ arrow_typeof(uint8 * 2.0),
+ arrow_typeof(uint16 * 2.0),
+ arrow_typeof(uint32 * 2.0),
+ arrow_typeof(uint64 * 2.0),
+ arrow_typeof(float32 * 2.0),
+ arrow_typeof(float64 * 2.0),
+ arrow_typeof(decimal * 2.0)
+from numeric_types;
+----
+Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64
Float64 Float64
+
+############### Division ###############
+
+
# Divide with the same operand type, expect the same output type
# except for decimal which is promoted to the highest precision
query TTTTTTTTTTT
@@ -110,5 +226,139 @@ from numeric_types;
----
Int8 Int16 Int32 Int64 UInt8 UInt16 UInt32 UInt64 Float32 Float64
Decimal128(11, 6)
+# Divide with literal integer
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 / 2),
+ arrow_typeof(int16 / 2),
+ arrow_typeof(int32 / 2),
+ arrow_typeof(int64 / 2),
+ arrow_typeof(uint8 / 2),
+ arrow_typeof(uint16 / 2),
+ arrow_typeof(uint32 / 2),
+ arrow_typeof(uint64 / 2),
+ arrow_typeof(float32 / 2),
+ arrow_typeof(float64 / 2),
+ arrow_typeof(decimal / 2)
+from numeric_types;
+----
+Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Float32 Float64 Decimal128(9,
6)
+
+# Divide with literal decimal
+query TTTTTTTTTTT
+select
+ arrow_typeof(int8 / 2.0),
+ arrow_typeof(int16 / 2.0),
+ arrow_typeof(int32 / 2.0),
+ arrow_typeof(int64 / 2.0),
+ arrow_typeof(uint8 / 2.0),
+ arrow_typeof(uint16 / 2.0),
+ arrow_typeof(uint32 / 2.0),
+ arrow_typeof(uint64 / 2.0),
+ arrow_typeof(float32 / 2.0),
+ arrow_typeof(float64 / 2.0),
+ arrow_typeof(decimal / 2.0)
+from numeric_types;
+----
+Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64 Float64
Float64 Float64
+
+###############
+# Test for comparison with constants uses efficient types
+# Expect the physical plans to compare with constants of the same type
+# should have no casts of the column to a different type
+
+statement ok
+set datafusion.explain.physical_plan_only = true;
+
+############### Less Than ###############
+
+## < positive integer (expect no casts)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 < 5 AND uint64 < 5 AND float64 < 5 AND decimal < 5;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: int64@3 < 5 AND uint64@7 < 5 AND float64@9 < 5 AND decimal@10
< Some(500),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+## < negative integer (expect no casts)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 < -5 AND uint64 < -5 AND float64 < -5 AND decimal < -5;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: int64@3 < -5 AND CAST(uint64@7 AS Decimal128(20, 0)) <
Some(-5),20,0 AND float64@9 < -5 AND decimal@10 < Some(-500),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+## < decimal (expect casts for integers to float)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 < 5.1 AND uint64 < 5.1 AND float64 < 5.1 AND decimal < 5.1;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: CAST(int64@3 AS Float64) < 5.1 AND CAST(uint64@7 AS Float64)
< 5.1 AND float64@9 < 5.1 AND decimal@10 < Some(510),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+## < negative decimal (expect casts for integers to float)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 < -5.1 AND uint64 < -5.1 AND float64 < -5.1 AND decimal < -5.1;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: CAST(int64@3 AS Float64) < -5.1 AND CAST(uint64@7 AS Float64)
< -5.1 AND float64@9 < -5.1 AND decimal@10 < Some(-510),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+
+############### Equality ###############
+
+## = positive integer (expect no casts)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 = 5 AND uint64 = 5 AND float64 = 5 AND decimal = 5;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: int64@3 = 5 AND uint64@7 = 5 AND float64@9 = 5 AND decimal@10
= Some(500),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+## = negative integer (expect no casts)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 = -5 AND uint64 = -5 AND float64 = -5 AND decimal = -5;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: int64@3 = -5 AND CAST(uint64@7 AS Decimal128(20, 0)) =
Some(-5),20,0 AND float64@9 = -5 AND decimal@10 = Some(-500),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+## = decimal (expect casts for integers to float)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 = 5.1 AND uint64 = 5.1 AND float64 = 5.1 AND decimal = 5.1;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: CAST(int64@3 AS Float64) = 5.1 AND CAST(uint64@7 AS Float64)
= 5.1 AND float64@9 = 5.1 AND decimal@10 = Some(510),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+## = negative decimal (expect casts for integers to float)
+query TT
+EXPLAIN SELECT * FROM numeric_types
+WHERE int64 = -5.1 AND uint64 = -5.1 AND float64 = -5.1 AND decimal = -5.1;
+----
+physical_plan
+01)CoalesceBatchesExec: target_batch_size=8192
+02)--FilterExec: CAST(int64@3 AS Float64) = -5.1 AND CAST(uint64@7 AS Float64)
= -5.1 AND float64@9 = -5.1 AND decimal@10 = Some(-510),5,2
+03)----MemoryExec: partitions=1, partition_sizes=[1]
+
+
+statement ok
+set datafusion.explain.physical_plan_only = false;
+
+
statement ok
drop table numeric_types
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@datafusion.apache.org
For additional commands, e-mail: commits-h...@datafusion.apache.org
