This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 984f98f21aa [fix](function) JSON_EXTRACT_STRING should return NULL
instead of the string 'null' when encountering a NULL value (#51516)
984f98f21aa is described below
commit 984f98f21aaa1a1d2cf91fe302fc2e5323f5127f
Author: Jerry Hu <[email protected]>
AuthorDate: Sat Jun 7 21:14:39 2025 +0800
[fix](function) JSON_EXTRACT_STRING should return NULL instead of the
string 'null' when encountering a NULL value (#51516)
### What problem does this PR solve?
Previously, when JSON_EXTRACT_STRING encountered a NULL input value or
path, it returned the string "null" instead of a proper SQL NULL value.
This behavior could lead to incorrect query results and confusion in
downstream processing that expects NULL to be represented as an actual
null value rather than a literal string.
This commit adjusts the logic to ensure that JSON_EXTRACT_STRING returns
NULL in such cases, aligning with SQL semantics and improving
compatibility with systems that rely on strict null-handling behavior.
---
be/src/vec/functions/function_jsonb.cpp | 2 +-
be/test/vec/function/function_jsonb_test.cpp | 8 +++---
.../data/json_p0/test_json_load_and_function.out | Bin 261689 -> 261681 bytes
.../test_json_load_unique_key_and_function.out | Bin 87037 -> 87033 bytes
.../data/jsonb_p0/test_jsonb_load_and_function.out | Bin 189691 -> 189687 bytes
.../test_jsonb_load_unique_key_and_function.out | Bin 124398 -> 124394 bytes
.../data/nereids_function_p0/scalar_function/J.out | Bin 160937 -> 160933 bytes
.../json_functions/test_json_extract.out | Bin 0 -> 279 bytes
.../json_functions/test_json_extract.groovy | 31 +++++++++++++++++++++
9 files changed, 36 insertions(+), 5 deletions(-)
diff --git a/be/src/vec/functions/function_jsonb.cpp
b/be/src/vec/functions/function_jsonb.cpp
index 334bb6d76f9..4ebb87d8739 100644
--- a/be/src/vec/functions/function_jsonb.cpp
+++ b/be/src/vec/functions/function_jsonb.cpp
@@ -764,7 +764,7 @@ private:
std::string_view(str_value->getBlob(),
str_value->length()), i, res_data,
res_offsets);
} else if (value->isNull()) {
- StringOP::push_value_string("null", i, res_data, res_offsets);
+ StringOP::push_null_string(i, res_data, res_offsets, null_map);
} else if (value->isTrue()) {
StringOP::push_value_string("true", i, res_data, res_offsets);
} else if (value->isFalse()) {
diff --git a/be/test/vec/function/function_jsonb_test.cpp
b/be/test/vec/function/function_jsonb_test.cpp
index bb5cae7a0b6..0578ff685b8 100644
--- a/be/test/vec/function/function_jsonb_test.cpp
+++ b/be/test/vec/function/function_jsonb_test.cpp
@@ -668,7 +668,7 @@ TEST(FunctionJsonbTEST, JsonbExtractStringTest) {
// json_extract root
DataSet data_set = {
{{Null(), STRING("$")}, Null()},
- {{STRING("null"), STRING("$")}, STRING("null")},
+ {{STRING("null"), STRING("$")}, Null()},
{{STRING("true"), STRING("$")}, STRING("true")},
{{STRING("false"), STRING("$")}, STRING("false")},
{{STRING("100"), STRING("$")}, STRING("100")},
//int8
@@ -752,7 +752,7 @@ TEST(FunctionJsonbTEST, JsonbExtractStringTest) {
{{STRING(R"(["abc", "def"])"), STRING("$[1]")}, STRING("def")},
// string array
{{STRING(R"(["abc", "def"])"), STRING("$[2]")}, Null()},
// string array
{{STRING(R"([null, true, false, 100, 6.18, "abc"])"),
STRING("$[0]")},
- STRING("null")}, // multi type array
+ Null()}, // multi type array
{{STRING(R"([null, true, false, 100, 6.18, "abc"])"),
STRING("$[1]")},
STRING("true")}, // multi type array
{{STRING(R"([null, true, false, 100, 6.18, "abc"])"),
STRING("$[2]")},
@@ -1559,7 +1559,7 @@ TEST(FunctionJsonbTEST, GetJSONSTRINGTest) {
// get json from root
DataSet data_set = {
{{Null(), STRING("$")}, Null()},
- {{STRING("null"), STRING("$")}, STRING("null")},
+ {{STRING("null"), STRING("$")}, Null()},
{{STRING("true"), STRING("$")}, STRING("true")},
{{STRING("false"), STRING("$")}, STRING("false")},
{{STRING("100"), STRING("$")}, STRING("100")},
//int8
@@ -1643,7 +1643,7 @@ TEST(FunctionJsonbTEST, GetJSONSTRINGTest) {
{{STRING(R"(["abc", "def"])"), STRING("$[1]")}, STRING("def")},
// string array
{{STRING(R"(["abc", "def"])"), STRING("$[2]")}, Null()},
// string array
{{STRING(R"([null, true, false, 100, 6.18, "abc"])"),
STRING("$[0]")},
- STRING("null")}, // multi type array
+ Null()}, // multi type array
{{STRING(R"([null, true, false, 100, 6.18, "abc"])"),
STRING("$[1]")},
STRING("true")}, // multi type array
{{STRING(R"([null, true, false, 100, 6.18, "abc"])"),
STRING("$[2]")},
diff --git a/regression-test/data/json_p0/test_json_load_and_function.out
b/regression-test/data/json_p0/test_json_load_and_function.out
index aa8c6805696..de25adfef2e 100644
Binary files a/regression-test/data/json_p0/test_json_load_and_function.out and
b/regression-test/data/json_p0/test_json_load_and_function.out differ
diff --git
a/regression-test/data/json_p0/test_json_load_unique_key_and_function.out
b/regression-test/data/json_p0/test_json_load_unique_key_and_function.out
index a0a12b2c276..3a6a5ef057a 100644
Binary files
a/regression-test/data/json_p0/test_json_load_unique_key_and_function.out and
b/regression-test/data/json_p0/test_json_load_unique_key_and_function.out differ
diff --git a/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out
b/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out
index 83474e38b7f..dbd756dab9d 100644
Binary files a/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out
and b/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out differ
diff --git
a/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out
b/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out
index c699a010fc8..427b6426d68 100644
Binary files
a/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out and
b/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out
differ
diff --git a/regression-test/data/nereids_function_p0/scalar_function/J.out
b/regression-test/data/nereids_function_p0/scalar_function/J.out
index 41606ca51a7..64a76e24826 100644
Binary files a/regression-test/data/nereids_function_p0/scalar_function/J.out
and b/regression-test/data/nereids_function_p0/scalar_function/J.out differ
diff --git
a/regression-test/data/query_p0/sql_functions/json_functions/test_json_extract.out
b/regression-test/data/query_p0/sql_functions/json_functions/test_json_extract.out
new file mode 100644
index 00000000000..1fe0d2a2b7e
Binary files /dev/null and
b/regression-test/data/query_p0/sql_functions/json_functions/test_json_extract.out
differ
diff --git
a/regression-test/suites/query_p0/sql_functions/json_functions/test_json_extract.groovy
b/regression-test/suites/query_p0/sql_functions/json_functions/test_json_extract.groovy
new file mode 100644
index 00000000000..46a2009a6f8
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/json_functions/test_json_extract.groovy
@@ -0,0 +1,31 @@
+// 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_json_extract") {
+ qt_sql_string1 """ SELECT JSON_EXTRACT_STRING('{"k1":"v31","k2":300}',
'\$.k1'); """
+ qt_sql_string2 """ SELECT JSON_EXTRACT_STRING(null, '\$.k1'); """
+ qt_sql_string3 """ SELECT JSON_EXTRACT_STRING('{"k1":"v31","k2":300}',
NULL); """
+ qt_sql_string4 """ SELECT
JSON_EXTRACT_STRING('{"k1":"v31","k2":{"sub_key": 1234.56}}', '\$.k2.sub_key');
"""
+ qt_sql_string5 """ SELECT JSON_EXTRACT_STRING(json_array("abc", 123,
STR_TO_DATE('2025-06-05 14:47:01', '%Y-%m-%d %H:%i:%s')), '\$.[2]'); """
+ qt_sql_string6 """ SELECT JSON_EXTRACT_STRING('{"k1":"v31","k2": null}',
'\$.k2'); """
+ qt_sql_string7 """ SELECT JSON_EXTRACT_STRING('{"k1":"v31","k2":300}',
'\$.k3'); """
+
+ test {
+ sql """ SELECT JSON_EXTRACT_STRING('{"id": 123, "name": "doris"}',
'\$.'); """
+ exception "Invalid Json Path for value: \$."
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
