This is an automated email from the ASF dual-hosted git repository.
morningman 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 fe95b84c34 [fix](jsonb)fix CAST String to JSONB nullable problem
(#14626)
fe95b84c34 is described below
commit fe95b84c346c2e62dc8ebdd5a0b66a38b5f3013d
Author: Kang <[email protected]>
AuthorDate: Tue Nov 29 16:22:22 2022 +0800
[fix](jsonb)fix CAST String to JSONB nullable problem (#14626)
fix CAST String to SONB nullable problem in DEBUG mode.
---
be/src/vec/data_types/data_type_factory.hpp | 2 +-
be/src/vec/functions/function_cast.h | 3 +-
be/test/vec/function/function_jsonb_test.cpp | 49 +++-
.../data/jsonb_p0/test_jsonb_load_and_function.out | 324 +++++++++++++++++++++
.../jsonb_p0/test_jsonb_load_and_function.groovy | 43 +++
5 files changed, 409 insertions(+), 12 deletions(-)
diff --git a/be/src/vec/data_types/data_type_factory.hpp
b/be/src/vec/data_types/data_type_factory.hpp
index b164c432f1..696ab9ee54 100644
--- a/be/src/vec/data_types/data_type_factory.hpp
+++ b/be/src/vec/data_types/data_type_factory.hpp
@@ -79,7 +79,7 @@ public:
BeConsts::MAX_DECIMAL128_PRECISION,
0)},
{"Decimal128I",
std::make_shared<DataTypeDecimal<Decimal128I>>(
BeConsts::MAX_DECIMAL128_PRECISION, 0)},
- {"Json", std::make_shared<DataTypeJsonb>()},
+ {"Jsonb", std::make_shared<DataTypeJsonb>()},
{"BitMap", std::make_shared<DataTypeBitMap>()},
{"Hll", std::make_shared<DataTypeHLL>()},
};
diff --git a/be/src/vec/functions/function_cast.h
b/be/src/vec/functions/function_cast.h
index e15ef01e9f..90edd3906b 100644
--- a/be/src/vec/functions/function_cast.h
+++ b/be/src/vec/functions/function_cast.h
@@ -1730,8 +1730,7 @@ protected:
need_to_be_nullable |= arguments[0].type->is_nullable();
// 2. from_type is string, to_type is not string
need_to_be_nullable |= (arguments[0].type->get_type_id() ==
TypeIndex::String) &&
- (type->get_type_id() != TypeIndex::String) &&
- (type->get_type_id() != TypeIndex::JSONB);
+ (type->get_type_id() != TypeIndex::String);
// 3. from_type is not DateTime/Date, to_type is DateTime/Date
need_to_be_nullable |= (arguments[0].type->get_type_id() !=
TypeIndex::Date &&
arguments[0].type->get_type_id() !=
TypeIndex::DateTime) &&
diff --git a/be/test/vec/function/function_jsonb_test.cpp
b/be/test/vec/function/function_jsonb_test.cpp
index 46ef9655dd..7f1fd480e5 100644
--- a/be/test/vec/function/function_jsonb_test.cpp
+++ b/be/test/vec/function/function_jsonb_test.cpp
@@ -1394,33 +1394,64 @@ TEST(FunctionJsonbTEST, JsonbCastToOtherTest) {
}
TEST(FunctionJsonbTEST, JsonbCastFromOtherTest) {
+ // CAST Nullable(X) to Nullable(JSONB)
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::UInt8}, ConstedNotnull
{TypeIndex::String}},
- {{{BOOLEAN(1), STRING("Json")}, STRING("true")}});
+ {{{BOOLEAN(1), STRING("Jsonb")}, STRING("true")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::UInt8}, ConstedNotnull
{TypeIndex::String}},
- {{{BOOLEAN(0), STRING("Json")}, STRING("false")}});
+ {{{BOOLEAN(0), STRING("Jsonb")}, STRING("false")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::Int8}, ConstedNotnull
{TypeIndex::String}},
- {{{TINYINT(100), STRING("Json")}, STRING("100")}});
+ {{{TINYINT(100), STRING("Jsonb")}, STRING("100")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::Int16}, ConstedNotnull
{TypeIndex::String}},
- {{{SMALLINT(10000), STRING("Json")}, STRING("10000")}});
+ {{{SMALLINT(10000), STRING("Jsonb")}, STRING("10000")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::Int32}, ConstedNotnull
{TypeIndex::String}},
- {{{INT(1000000000), STRING("Json")}, STRING("1000000000")}});
+ {{{INT(1000000000), STRING("Jsonb")}, STRING("1000000000")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::Int64}, ConstedNotnull
{TypeIndex::String}},
- {{{BIGINT(1152921504606846976), STRING("Json")},
STRING("1152921504606846976")}});
+ {{{BIGINT(1152921504606846976), STRING("Jsonb")},
STRING("1152921504606846976")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::Float64}, ConstedNotnull
{TypeIndex::String}},
- {{{DOUBLE(6.18), STRING("Json")}, STRING("6.18")}});
+ {{{DOUBLE(6.18), STRING("Jsonb")}, STRING("6.18")}});
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::String}, ConstedNotnull
{TypeIndex::String}},
- {{{STRING(R"(abcd)"), STRING("Json")}, Null()}}); // should fail
+ {{{STRING(R"(abcd)"), STRING("Jsonb")}, Null()}}); // should fail
check_function<DataTypeJsonb, true>(
"CAST", {Nullable {TypeIndex::String}, ConstedNotnull
{TypeIndex::String}},
- {{{STRING(R"("abcd")"), STRING("Json")}, STRING(R"("abcd")")}});
+ {{{STRING(R"("abcd")"), STRING("Jsonb")}, STRING(R"("abcd")")}});
+
+ // CAST X to JSONB
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::UInt8}, ConstedNotnull
{TypeIndex::String}},
+ {{{BOOLEAN(1), STRING("Jsonb")}, STRING("true")}});
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::UInt8}, ConstedNotnull
{TypeIndex::String}},
+ {{{BOOLEAN(0), STRING("Jsonb")}, STRING("false")}});
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::Int8}, ConstedNotnull
{TypeIndex::String}},
+ {{{TINYINT(100), STRING("Jsonb")}, STRING("100")}});
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::Int16}, ConstedNotnull
{TypeIndex::String}},
+ {{{SMALLINT(10000), STRING("Jsonb")}, STRING("10000")}});
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::Int32}, ConstedNotnull
{TypeIndex::String}},
+ {{{INT(1000000000), STRING("Jsonb")}, STRING("1000000000")}});
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::Int64}, ConstedNotnull
{TypeIndex::String}},
+ {{{BIGINT(1152921504606846976), STRING("Jsonb")},
STRING("1152921504606846976")}});
+ check_function<DataTypeJsonb, false>(
+ "CAST", {Notnull {TypeIndex::Float64}, ConstedNotnull
{TypeIndex::String}},
+ {{{DOUBLE(6.18), STRING("Jsonb")}, STRING("6.18")}});
+ // String to JSONB should always be Nullable
+ check_function<DataTypeJsonb, true>(
+ "CAST", {Notnull {TypeIndex::String}, ConstedNotnull
{TypeIndex::String}},
+ {{{STRING(R"(abcd)"), STRING("Jsonb")}, Null()}}); // should fail
+ check_function<DataTypeJsonb, true>(
+ "CAST", {Notnull {TypeIndex::String}, ConstedNotnull
{TypeIndex::String}},
+ {{{STRING(R"("abcd")"), STRING("Jsonb")}, STRING(R"("abcd")")}});
}
} // namespace doris::vectorized
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 b2b68133f6..e7d9b65b2e 100644
--- a/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out
+++ b/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out
@@ -3442,3 +3442,327 @@
18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
26 {"k1":"v1","k2":200} \N
+-- !select --
+1 \N \N
+2 null \N
+3 true true
+4 false false
+5 100 \N
+6 10000 \N
+7 1000000000 \N
+8 1152921504606846976 \N
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 \N
+8 1152921504606846976 \N
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 \N
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 1152921504606846976
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100.0
+6 10000 10000.0
+7 1000000000 1.0E9
+8 1152921504606846976 1.15292150460684698E18
+9 6.18 6.18
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null null
+3 true true
+4 false false
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 1152921504606846976
+9 6.18 6.18
+10 "abcd" "abcd"
+11 {} {}
+12 {"k1":"v31","k2":300} {"k1":"v31","k2":300}
+13 [] []
+14 [123,456] [123,456]
+15 ["abc","def"] ["abc","def"]
+16 [null,true,false,100,6.18,"abc"] [null,true,false,100,6.18,"abc"]
+17 [{"k1":"v41","k2":400},1,"a",3.14]
[{"k1":"v41","k2":400},1,"a",3.14]
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]}
{"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]}
+26 {"k1":"v1","k2":200} {"k1":"v1","k2":200}
+
+-- !select --
+1 \N \N
+2 null \N
+3 true true
+4 false false
+5 100 \N
+6 10000 \N
+7 1000000000 \N
+8 1152921504606846976 \N
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 \N
+8 1152921504606846976 \N
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 \N
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 1152921504606846976
+9 6.18 \N
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null \N
+3 true \N
+4 false \N
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 1.15292150460685e+18
+9 6.18 6.18
+10 "abcd" \N
+11 {} \N
+12 {"k1":"v31","k2":300} \N
+13 [] \N
+14 [123,456] \N
+15 ["abc","def"] \N
+16 [null,true,false,100,6.18,"abc"] \N
+17 [{"k1":"v41","k2":400},1,"a",3.14] \N
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N
+26 {"k1":"v1","k2":200} \N
+
+-- !select --
+1 \N \N
+2 null null
+3 true true
+4 false false
+5 100 100
+6 10000 10000
+7 1000000000 1000000000
+8 1152921504606846976 1152921504606846976
+9 6.18 6.18
+10 "abcd" "abcd"
+11 {} {}
+12 {"k1":"v31","k2":300} {"k1":"v31","k2":300}
+13 [] []
+14 [123,456] [123,456]
+15 ["abc","def"] ["abc","def"]
+16 [null,true,false,100,6.18,"abc"] [null,true,false,100,6.18,"abc"]
+17 [{"k1":"v41","k2":400},1,"a",3.14]
[{"k1":"v41","k2":400},1,"a",3.14]
+18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]}
{"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]}
+26 {"k1":"v1","k2":200} {"k1":"v1","k2":200}
+
+-- !select --
+null
+
+-- !select --
+true
+
+-- !select --
+false
+
+-- !select --
+100
+
+-- !select --
+10000
+
+-- !select --
+1000000000
+
+-- !select --
+1152921504606846976
+
+-- !select --
+6.18
+
+-- !select --
+"abcd"
+
+-- !select --
+{}
+
+-- !select --
+{"k1":"v31","k2":300}
+
+-- !select --
+[]
+
+-- !select --
+[123,456]
+
+-- !select --
+["abc","def"]
+
+-- !select --
+[null,true,false,100,6.18,"abc"]
+
+-- !select --
+[{"k1":"v41","k2":400},1,"a",3.14]
+
+-- !select --
+{"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]}
+
+-- !select --
+\N
+
+-- !select --
+\N
+
+-- !select --
+\N
+
+-- !select --
+\N
+
+-- !select --
+\N
+
+-- !select --
+\N
+
+-- !select --
+\N
+
diff --git
a/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy
b/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy
index 871d1542dd..2535f375ef 100644
--- a/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy
+++ b/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy
@@ -308,4 +308,47 @@ suite("test_jsonb_load_and_function", "p0") {
qt_select "SELECT id, j, jsonb_type(j, '\$.a1[3]') FROM ${testTable} ORDER
BY id"
qt_select "SELECT id, j, jsonb_type(j, '\$.a1[4]') FROM ${testTable} ORDER
BY id"
qt_select "SELECT id, j, jsonb_type(j, '\$.a1[10]') FROM ${testTable}
ORDER BY id"
+
+
+ // CAST from JSONB
+ qt_select "SELECT id, j, CAST(j AS BOOLEAN) FROM ${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(j AS SMALLINT) FROM ${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(j AS INT) FROM ${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(j AS BIGINT) FROM ${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(j AS DOUBLE) FROM ${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(j AS STRING) FROM ${testTable} ORDER BY id"
+
+ // CAST to JSONB
+ qt_select "SELECT id, j, CAST(CAST(j AS BOOLEAN) AS JSONB) FROM
${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(CAST(j AS SMALLINT) AS JSONB) FROM
${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(CAST(j AS INT) AS JSONB) FROM ${testTable}
ORDER BY id"
+ qt_select "SELECT id, j, CAST(CAST(j AS BIGINT) AS JSONB) FROM
${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(CAST(j AS DOUBLE) AS JSONB) FROM
${testTable} ORDER BY id"
+ qt_select "SELECT id, j, CAST(CAST(j AS STRING) AS JSONB) FROM
${testTable} ORDER BY id"
+
+ qt_select """SELECT CAST('null' AS JSONB)"""
+ qt_select """SELECT CAST('true' AS JSONB)"""
+ qt_select """SELECT CAST('false' AS JSONB)"""
+ qt_select """SELECT CAST('100' AS JSONB)"""
+ qt_select """SELECT CAST('10000' AS JSONB)"""
+ qt_select """SELECT CAST('1000000000' AS JSONB)"""
+ qt_select """SELECT CAST('1152921504606846976' AS JSONB)"""
+ qt_select """SELECT CAST('6.18' AS JSONB)"""
+ qt_select """SELECT CAST('"abcd"' AS JSONB)"""
+ qt_select """SELECT CAST('{}' AS JSONB)"""
+ qt_select """SELECT CAST('{"k1":"v31", "k2": 300}' AS JSONB)"""
+ qt_select """SELECT CAST('[]' AS JSONB)"""
+ qt_select """SELECT CAST('[123, 456]' AS JSONB)"""
+ qt_select """SELECT CAST('["abc", "def"]' AS JSONB)"""
+ qt_select """SELECT CAST('[null, true, false, 100, 6.18, "abc"]' AS
JSONB)"""
+ qt_select """SELECT CAST('[{"k1":"v41", "k2": 400}, 1, "a", 3.14]' AS
JSONB)"""
+ qt_select """SELECT CAST('{"k1":"v31", "k2": 300, "a1": [{"k1":"v41",
"k2": 400}, 1, "a", 3.14]}' AS JSONB)"""
+ qt_select """SELECT CAST("''" AS JSONB)"""
+ qt_select """SELECT CAST("'abc'" AS JSONB)"""
+ qt_select """SELECT CAST('abc' AS JSONB)"""
+ qt_select """SELECT CAST('100x' AS JSONB)"""
+ qt_select """SELECT CAST('6.a8' AS JSONB)"""
+ qt_select """SELECT CAST('{x' AS JSONB)"""
+ qt_select """SELECT CAST('[123, abc]' AS JSONB)"""
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
