This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 6bd93b119fa [pick](cast)Feature cast complexttype2 json (#38632)
6bd93b119fa is described below
commit 6bd93b119fa2546cd59b334f307acab4e63c4b3f
Author: amory <[email protected]>
AuthorDate: Thu Aug 1 09:18:15 2024 +0800
[pick](cast)Feature cast complexttype2 json (#38632)
## Proposed changes
backport: https://github.com/apache/doris/pull/36548
Issue Number: close #xxx
<!--Describe your changes.-->
---
.../main/java/org/apache/doris/catalog/Type.java | 3 +
.../java/org/apache/doris/analysis/CastExpr.java | 4 +
.../nereids/rules/expression/check/CheckCast.java | 22 ++++
.../doris/nereids/trees/expressions/Cast.java | 2 +-
.../query_p0/cast/test_complextype_to_json.out | 67 ++++++++++++
.../query_p0/cast/test_complextype_to_json.groovy | 119 +++++++++++++++++++++
6 files changed, 216 insertions(+), 1 deletion(-)
diff --git a/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java
b/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java
index 46cda54753e..f2eccaaf54a 100644
--- a/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java
+++ b/fe/fe-common/src/main/java/org/apache/doris/catalog/Type.java
@@ -833,6 +833,9 @@ public abstract class Type {
}
public static boolean canCastTo(Type sourceType, Type targetType) {
+ if (targetType.isJsonbType() && sourceType.isComplexType()) {
+ return true;
+ }
if (sourceType.isVariantType() && (targetType.isScalarType() ||
targetType.isArrayType())) {
// variant could cast to scalar types and array
return true;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
index 130b2d43ed0..6ca52e226aa 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
@@ -152,6 +152,10 @@ public class CastExpr extends Expr {
Type from = getActualArgTypes(collectChildReturnTypes())[0];
Type to = getActualType(type);
NullableMode nullableMode = TYPE_NULLABLE_MODE.get(Pair.of(from,
to));
+ // for complex type cast to jsonb we make ret is always nullable
+ if (from.isComplexType() && type.isJsonbType()) {
+ nullableMode = Function.NullableMode.ALWAYS_NULLABLE;
+ }
Preconditions.checkState(nullableMode != null,
"cannot find nullable node for cast from " + from + " to "
+ to);
fn = new Function(new FunctionName(getFnName(type)),
Lists.newArrayList(e.type), type,
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/check/CheckCast.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/check/CheckCast.java
index 69a9105d653..e76d7ef344d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/check/CheckCast.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/check/CheckCast.java
@@ -92,6 +92,9 @@ public class CheckCast implements
ExpressionPatternRuleFactory {
}
return true;
} else if (originalType instanceof JsonType || targetType instanceof
JsonType) {
+ if (originalType.isComplexType() &&
!checkMapKeyIsStringLikeForJson(originalType)) {
+ return false;
+ }
return true;
} else {
return checkPrimitiveType(originalType, targetType);
@@ -127,4 +130,23 @@ public class CheckCast implements
ExpressionPatternRuleFactory {
}
return true;
}
+
+ /**
+ * check if complexType type which contains map, make sure key is string
like for json
+ *
+ * @param complexType need to check
+ * @return true if complexType can cast to json
+ */
+ public static boolean checkMapKeyIsStringLikeForJson(DataType complexType)
{
+ if (complexType.isMapType()) {
+ return ((MapType) complexType).getKeyType().isStringLikeType();
+ } else if (complexType.isArrayType()) {
+ return checkMapKeyIsStringLikeForJson(((ArrayType)
complexType).getItemType());
+ } else if (complexType.isStructType()) {
+ for (StructField f : ((StructType) complexType).getFields()) {
+ return checkMapKeyIsStringLikeForJson(f.getDataType());
+ }
+ }
+ return true;
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Cast.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Cast.java
index 62bd3639b5a..124ed589d49 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Cast.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Cast.java
@@ -79,7 +79,7 @@ public class Cast extends Expression implements
UnaryExpression {
return true;
} else if (!childDataType.isTimeLikeType() &&
targetType.isTimeLikeType()) {
return true;
- } else if (childDataType.isJsonType()) {
+ } else if (childDataType.isJsonType() || targetType.isJsonType()) {
return true;
} else {
return child().nullable();
diff --git a/regression-test/data/query_p0/cast/test_complextype_to_json.out
b/regression-test/data/query_p0/cast/test_complextype_to_json.out
new file mode 100644
index 00000000000..7412966f767
--- /dev/null
+++ b/regression-test/data/query_p0/cast/test_complextype_to_json.out
@@ -0,0 +1,67 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+[[],[]]
+
+-- !select --
+{"k1":"v31","k2":"300"}
+
+-- !select --
+[]
+
+-- !select --
+[123,456]
+
+-- !select --
+["abc","def"]
+
+-- !select --
+[null,"1","0","100","6.18","abc"]
+
+-- !select --
+[{"k1":"v41","k2":"400"},{"k1":"v41","k2":"400"}]
+
+-- !select --
+["[['k1', 'k2'], ['v41', '400']]","1","a","3.14"]
+
+-- !select --
+{"k1":"v31","k2":"300","a1":"['[['k1', 'k2'], ['v41', '400']]', '1', 'a',
'3.14']"}
+
+-- !select --
+{"col1":"a","col2":1,"col3":"doris","col4":"aaaaa","col5":1.32}
+
+-- !sql_arr_agg_cast --
+1 ["LC","LB","alex"] ["V1_3","V1_2",null]
+2 ["LC","LB","LA"] ["V2_3","V2_2","V2_1"]
+3 ["LC",null,"LA"] ["V3_3",null,"V3_1"]
+4 ["LC","LB","LA"] ["V4_3","V4_2","V4_1"]
+5 [null,"LC","LB","LA"] ["V5_3","V5_3","V5_2","V5_1"]
+6 [null,"LC","LC","LC","LC"] ["V6_3",null,"V6_3",null,"V6_3"]
+7 [null,"LC","LC","LC","LC"] ["V7_3",null,"V7_3",null,"V7_3"]
+
+-- !sql_arr_agg_cast_json_object --
+{"id":1,"label":"[\\"LC\\",\\"LB\\",\\"alex\\"]","field":"[\\"V1_3\\",\\"V1_2\\",null]"}
+{"id":2,"label":"[\\"LC\\",\\"LB\\",\\"LA\\"]","field":"[\\"V2_3\\",\\"V2_2\\",\\"V2_1\\"]"}
+{"id":3,"label":"[\\"LC\\",null,\\"LA\\"]","field":"[\\"V3_3\\",null,\\"V3_1\\"]"}
+{"id":4,"label":"[\\"LC\\",\\"LB\\",\\"LA\\"]","field":"[\\"V4_3\\",\\"V4_2\\",\\"V4_1\\"]"}
+{"id":5,"label":"[null,\\"LC\\",\\"LB\\",\\"LA\\"]","field":"[\\"V5_3\\",\\"V5_3\\",\\"V5_2\\",\\"V5_1\\"]"}
+{"id":6,"label":"[null,\\"LC\\",\\"LC\\",\\"LC\\",\\"LC\\"]","field":"[\\"V6_3\\",null,\\"V6_3\\",null,\\"V6_3\\"]"}
+{"id":7,"label":"[null,\\"LC\\",\\"LC\\",\\"LC\\",\\"LC\\"]","field":"[\\"V7_3\\",null,\\"V7_3\\",null,\\"V7_3\\"]"}
+
+-- !sql_map_agg_cast --
+1 {"LC":"V1_3","LB":"V1_2","alex":null}
+2 {"LC":"V2_3","LB":"V2_2","LA":"V2_1"}
+3 {"LC":"V3_3","LA":"V3_1"}
+4 {"LC":"V4_3","LB":"V4_2","LA":"V4_1"}
+5 {"LC":"V5_3","LB":"V5_2","LA":"V5_1"}
+6 {"LC":null}
+7 {"LC":null}
+
+-- !sql_map_agg_cast_json_object --
+{"id":1,"map_label":"{\\"LC\\":\\"V1_3\\",\\"LB\\":\\"V1_2\\",\\"alex\\":null}"}
+{"id":2,"map_label":"{\\"LC\\":\\"V2_3\\",\\"LB\\":\\"V2_2\\",\\"LA\\":\\"V2_1\\"}"}
+{"id":3,"map_label":"{\\"LC\\":\\"V3_3\\",\\"LA\\":\\"V3_1\\"}"}
+{"id":4,"map_label":"{\\"LC\\":\\"V4_3\\",\\"LB\\":\\"V4_2\\",\\"LA\\":\\"V4_1\\"}"}
+{"id":5,"map_label":"{\\"LC\\":\\"V5_3\\",\\"LB\\":\\"V5_2\\",\\"LA\\":\\"V5_1\\"}"}
+{"id":6,"map_label":"{\\"LC\\":null}"}
+{"id":7,"map_label":"{\\"LC\\":null}"}
+
diff --git
a/regression-test/suites/query_p0/cast/test_complextype_to_json.groovy
b/regression-test/suites/query_p0/cast/test_complextype_to_json.groovy
new file mode 100644
index 00000000000..18d83d11124
--- /dev/null
+++ b/regression-test/suites/query_p0/cast/test_complextype_to_json.groovy
@@ -0,0 +1,119 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite('test_complextype_to_json', "query_p0") {
+ // do support in nereids
+ sql """ set experimental_enable_nereids_planner=true"""
+ sql """ set enable_fallback_to_original_planner=false; """
+
+ // literal cast
+ qt_select """SELECT CAST({} AS JSON)"""
+ qt_select """SELECT CAST({"k1":"v31", "k2": 300} AS JSON)"""
+ qt_select """SELECT CAST([] AS JSON)"""
+ qt_select """SELECT CAST([123, 456] AS JSON)"""
+ qt_select """SELECT CAST(["abc", "def"] AS JSON)"""
+ qt_select """SELECT CAST([null, true, false, 100, 6.18, "abc"] AS JSON)"""
+ qt_select """SELECT CAST([{"k1":"v41", "k2": 400}, {"k1":"v41", "k2":
400}] AS JSON)"""
+ qt_select """SELECT CAST([{"k1":"v41", "k2": 400}, 1, "a", 3.14] AS
JSON)"""
+ qt_select """SELECT CAST({"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2":
400}, 1, "a", 3.14]} AS JSON)"""
+ qt_select """SELECT CAST(struct('a', 1, 'doris', 'aaaaa', 1.32) AS JSON)"""
+ // invalid map key cast
+ test {
+ sql """SELECT CAST(map(1, 'a', 2, 'b') AS JSON)"""
+ exception "errCode = 2,"
+ }
+ test {
+ sql """SELECT CAST([{1:"v41", 2: 400}] AS JSON)"""
+ exception "errCode = 2,"
+ }
+
+
+ sql """ DROP TABLE IF EXISTS test_agg_to_json; """
+ sql """
+ CREATE TABLE `test_agg_to_json` (
+ `id` int(11) NOT NULL,
+ `label_name` varchar(32) default null,
+ `value_field` string default null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2",
+ "light_schema_change" = "true",
+ "disable_auto_compaction" = "false",
+ "enable_single_replica_compaction" = "false"
+ );
+ """
+
+ sql """
+ insert into `test_agg_to_json` values
+ (1, "alex",NULL),
+ (1, "LB", "V1_2"),
+ (1, "LC", "V1_3"),
+ (2, "LA", "V2_1"),
+ (2, "LB", "V2_2"),
+ (2, "LC", "V2_3"),
+ (3, "LA", "V3_1"),
+ (3, NULL, NULL),
+ (3, "LC", "V3_3"),
+ (4, "LA", "V4_1"),
+ (4, "LB", "V4_2"),
+ (4, "LC", "V4_3"),
+ (5, "LA", "V5_1"),
+ (5, "LB", "V5_2"),
+ (5, "LC", "V5_3"),
+ (5, NULL, "V5_3"),
+ (6, "LC", "V6_3"),
+ (6, "LC", NULL),
+ (6, "LC", "V6_3"),
+ (6, "LC", NULL),
+ (6, NULL, "V6_3"),
+ (7, "LC", "V7_3"),
+ (7, "LC", NULL),
+ (7, "LC", "V7_3"),
+ (7, "LC", NULL),
+ (7, NULL, "V7_3");
+ """
+
+ // array_agg result cast to json then combination to json_object
+ qt_sql_arr_agg_cast """ select t.id, cast(t.label_name as json),
cast(t.value_field as json) from (select id, array_agg(label_name) as
label_name, array_agg(value_field) as value_field from test_agg_to_json group
by id) t order by t.id; """
+ qt_sql_arr_agg_cast_json_object """ select json_object("id", t.id,
"label", cast(t.label_name as json), "field", cast(t.value_field as json)) from
(select id, array_agg(label_name) as label_name, array_agg(value_field) as
value_field from test_agg_to_json group by id) t order by t.id; """
+
+ // map_agg result cast to json then combination to json_object
+ qt_sql_map_agg_cast """
+ WITH `labels` as (
+ SELECT `id`, map_agg(`label_name`, `value_field`) m FROM
test_agg_to_json GROUP BY `id`
+ )
+ SELECT
+ id,
+ cast(m as json)
+ FROM `labels`
+ ORDER BY `id`;
+ """
+ qt_sql_map_agg_cast_json_object """
+ WITH `labels` as (
+ SELECT `id`, map_agg(`label_name`, `value_field`) m FROM
test_agg_to_json GROUP BY `id`
+ )
+ SELECT
+ json_object("id", id, "map_label", cast(m as json))
+ FROM `labels`
+ ORDER BY `id`;
+ """
+
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
