This is an automated email from the ASF dual-hosted git repository.
seawinde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new d4fbfb9da91 [fix](typeCoercion) remove checkLegalityBeforeTypeCoercion
in sum and avg (#59602)
d4fbfb9da91 is described below
commit d4fbfb9da918e2b9a3f037c8608868e24018949a
Author: seawinde <[email protected]>
AuthorDate: Fri Mar 20 16:17:08 2026 +0800
[fix](typeCoercion) remove checkLegalityBeforeTypeCoercion in sum and avg
(#59602)
### What problem does this PR solve?
When executing the following SQL query:
`SELECT SUM(JSON_EXTRACT(c_json, '$.age')) FROM json_test;`
an error occurs:
`sum requires a numeric, boolean or string parameter.`
This happens because JSON_EXTRACT returns a value of JSON type, which is
not directly accepted by the SUM aggregate function—even when the
extracted value is numerically valid (e.g., 30).
The table and data are defined as:
```sql
CREATE TABLE json_test (
id INT NOT NULL,
c_json JSON NULL
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
INSERT INTO json_test (id, c_json) VALUES
(1, '{"name": "Alice", " age": 30}'),
(2, '{"product": "laptop", "price": 5999.99}'),
(3, NULL);
```
This PR fixes the issue by allowing the SUM function to accept arguments
of JSON type, provided the underlying JSON value is numeric. The JSON
value is automatically cast to DOUBLE during aggregation, consistent
with Doris's type coercion rules.
After this change, the query above will execute successfully and return
the expected numeric result (e.g., 30).
---
.../trees/expressions/functions/agg/Avg.java | 10 --------
.../trees/expressions/functions/agg/Sum.java | 11 ---------
.../analysis/CheckExpressionLegalityTest.java | 2 +-
.../nereids/trees/expressions/GetDataTypeTest.java | 8 +++----
.../doris/nereids/util/TypeCoercionUtilsTest.java | 27 ++++++++++++++++++++++
.../data/datatype_p0/json/json_cast.out | 14 +++++++----
.../suites/datatype_p0/json/json_cast.groovy | 2 ++
.../test_timestamptz_storage_negative_case.groovy | 4 ++--
8 files changed, 46 insertions(+), 32 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Avg.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Avg.java
index 978b90043d7..4440c1c106e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Avg.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Avg.java
@@ -19,7 +19,6 @@ package
org.apache.doris.nereids.trees.expressions.functions.agg;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.catalog.ScalarType;
-import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NeedSessionVarGuard;
import org.apache.doris.nereids.trees.expressions.functions.ComputePrecision;
@@ -85,15 +84,6 @@ public class Avg extends NullableAggregateFunction
super(functionParams);
}
- @Override
- public void checkLegalityBeforeTypeCoercion() {
- DataType argType = child().getDataType();
- if (!argType.isNumericType() && !argType.isBooleanType()
- && !argType.isNullType() && !argType.isStringLikeType()) {
- throw new AnalysisException("avg requires a numeric, boolean or
string parameter: " + this.toSql());
- }
- }
-
@Override
public FunctionSignature computePrecision(FunctionSignature signature) {
DataType argumentType = getArgumentType(0);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Sum.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Sum.java
index de33ec9ae6d..681bc2b6281 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Sum.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/agg/Sum.java
@@ -18,7 +18,6 @@
package org.apache.doris.nereids.trees.expressions.functions.agg;
import org.apache.doris.catalog.FunctionSignature;
-import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NeedSessionVarGuard;
import
org.apache.doris.nereids.trees.expressions.functions.ComputePrecisionForSum;
@@ -29,7 +28,6 @@ import
org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.BigIntType;
import org.apache.doris.nereids.types.BooleanType;
-import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.DecimalV2Type;
import org.apache.doris.nereids.types.DecimalV3Type;
import org.apache.doris.nereids.types.DoubleType;
@@ -97,15 +95,6 @@ public class Sum extends NullableAggregateFunction
return new MultiDistinctSum(false, alwaysNullable, child());
}
- @Override
- public void checkLegalityBeforeTypeCoercion() {
- DataType argType = child().getDataType();
- if (!argType.isNumericType() && !argType.isBooleanType()
- && !argType.isNullType() && !argType.isStringLikeType()) {
- throw new AnalysisException("sum requires a numeric, boolean or
string parameter: " + this.toSql());
- }
- }
-
/**
* withDistinctAndChildren.
*/
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/CheckExpressionLegalityTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/CheckExpressionLegalityTest.java
index 34beb21f440..51ef79d2874 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/CheckExpressionLegalityTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/CheckExpressionLegalityTest.java
@@ -35,7 +35,7 @@ public class CheckExpressionLegalityTest implements
MemoPatternMatchSupported {
public void testAvg() {
ConnectContext connectContext = MemoTestUtils.createConnectContext();
ExceptionChecker.expectThrowsWithMsg(
- AnalysisException.class, "avg requires a numeric", () -> {
+ AnalysisException.class, "Can not find the compatibility
function signature", () -> {
PlanChecker.from(connectContext)
.analyze("select avg(id) from (select to_bitmap(1)
id) tbl");
});
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/GetDataTypeTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/GetDataTypeTest.java
index e95b0cd4b4d..b2bbbf2a725 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/GetDataTypeTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/GetDataTypeTest.java
@@ -84,8 +84,8 @@ public class GetDataTypeTest {
Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Sum(charLiteral)));
Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Sum(varcharLiteral)));
Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Sum(stringLiteral)));
- Assertions.assertThrows(RuntimeException.class, () ->
checkAndGetDataType(new Sum(dateLiteral)));
- Assertions.assertThrows(RuntimeException.class, () ->
checkAndGetDataType(new Sum(dateTimeLiteral)));
+ Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Sum(dateLiteral)));
+ Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Sum(dateTimeLiteral)));
}
@Test
@@ -125,8 +125,8 @@ public class GetDataTypeTest {
Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Avg(charLiteral)));
Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Avg(varcharLiteral)));
Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Avg(stringLiteral)));
- Assertions.assertThrows(RuntimeException.class, () ->
checkAndGetDataType(new Avg(dateLiteral)));
- Assertions.assertThrows(RuntimeException.class, () ->
checkAndGetDataType(new Avg(dateTimeLiteral)));
+ Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Avg(dateLiteral)));
+ Assertions.assertEquals(DoubleType.INSTANCE, checkAndGetDataType(new
Avg(dateTimeLiteral)));
}
private DataType checkAndGetDataType(Expression expression) {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
index 5931331096b..7aa41181374 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
@@ -27,6 +27,9 @@ import org.apache.doris.nereids.trees.expressions.InPredicate;
import org.apache.doris.nereids.trees.expressions.Multiply;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.Subtract;
+import org.apache.doris.nereids.trees.expressions.functions.BoundFunction;
+import org.apache.doris.nereids.trees.expressions.functions.agg.Avg;
+import org.apache.doris.nereids.trees.expressions.functions.agg.Sum;
import org.apache.doris.nereids.trees.expressions.literal.CharLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DateTimeLiteral;
@@ -285,6 +288,30 @@ public class TypeCoercionUtilsTest {
TypeCoercionUtils.characterLiteralTypeCoercion("2020-02-02",
DateTimeType.INSTANCE).get().getDataType());
}
+ @Test
+ public void testProcessBoundFunction() {
+ SlotReference jsonCol = new SlotReference("c_json", JsonType.INSTANCE);
+ BoundFunction sum = new Sum(jsonCol);
+ Assertions.assertDoesNotThrow(() ->
TypeCoercionUtils.processBoundFunction(sum));
+
+ Expression coerced = TypeCoercionUtils.processBoundFunction(sum);
+ Expression coercedArg = ((BoundFunction) coerced).child(0);
+ Assertions.assertTrue(
+ coercedArg.getDataType().equals(DoubleType.INSTANCE) ||
coercedArg.getDataType().isNumericType(),
+ "The argument of SUM should be of a numeric type after type
coercion."
+ );
+
+ BoundFunction avg = new Avg(jsonCol);
+ Assertions.assertDoesNotThrow(() ->
TypeCoercionUtils.processBoundFunction(avg));
+
+ coerced = TypeCoercionUtils.processBoundFunction(sum);
+ coercedArg = ((BoundFunction) coerced).child(0);
+ Assertions.assertTrue(
+ coercedArg.getDataType().equals(DoubleType.INSTANCE) ||
coercedArg.getDataType().isNumericType(),
+ "The argument of AVG should be of a numeric type after type
coercion."
+ );
+ }
+
@Test
public void testGetNumResultType() {
// Numeric type
diff --git a/regression-test/data/datatype_p0/json/json_cast.out
b/regression-test/data/datatype_p0/json/json_cast.out
index 11a2d2d23e0..6a1ce7bae9a 100644
--- a/regression-test/data/datatype_p0/json/json_cast.out
+++ b/regression-test/data/datatype_p0/json/json_cast.out
@@ -60,22 +60,22 @@ true
0
-- !sql20 --
-1.0
+1
-- !sql20 --
-0.0
+0
-- !sql21 --
true
-- !sql22 --
-1024.0
+1024
-- !sql23 --
1024.0
-- !sql24 --
-1024.0
+1024
-- !sql24 --
[1,2,3]
@@ -86,6 +86,12 @@ true
-- !sql26 --
["2020-01-01"]
+-- !sql26 --
+30
+
+-- !sql26 --
+30
+
-- !sql27 --
1.2345678901234567e+49
diff --git a/regression-test/suites/datatype_p0/json/json_cast.groovy
b/regression-test/suites/datatype_p0/json/json_cast.groovy
index 6fa21877aa0..40c665940a2 100644
--- a/regression-test/suites/datatype_p0/json/json_cast.groovy
+++ b/regression-test/suites/datatype_p0/json/json_cast.groovy
@@ -50,6 +50,8 @@ suite("test_json_type_cast", "p0") {
qt_sql24 "SELECT CAST(CAST(CAST('[1, 2, 3]' AS TEXT) AS JSON) as TEXT)"
qt_sql25 "SELECT CAST(CAST(CAST('[1, 2, 3]' AS TEXT) AS JSON) as TEXT)"
qt_sql26 """SELECT CAST(CAST(CAST('["2020-01-01"]' AS TEXT) AS JSON) as
TEXT)"""
+ qt_sql26 """select SUM(JSON_EXTRACT('{"name": "Alice", "age": 30}',
'\$.age')); """
+ qt_sql26 """select AVG(JSON_EXTRACT('{"name": "Alice", "age": 30}',
'\$.age')); """
testFoldConst("select cast('18446744073709551616' as json)")
testFoldConst("select cast('[1323132,3.13,18446744073709551616]' as json)")
diff --git
a/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_storage_negative_case.groovy
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_storage_negative_case.groovy
index 2cc15fa01d3..e84952b04b5 100644
---
a/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_storage_negative_case.groovy
+++
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_storage_negative_case.groovy
@@ -69,11 +69,11 @@ suite("test_timestamptz_storage_negative_case") {
// sum
test {
sql """ select sum(ts_tz) from
timestamptz_storage_agg_key_negative_case; """
- exception "sum requires"
+ exception "Can not find the compatibility function signature"
}
// avg
test {
sql """ select avg(ts_tz) from
timestamptz_storage_agg_key_negative_case; """
- exception "avg requires"
+ exception "Can not find the compatibility function signature"
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
