diff options
| author | udovichenko-r <udovichenko-r@yandex-team.com> | 2024-11-19 14:58:38 +0300 |
|---|---|---|
| committer | udovichenko-r <udovichenko-r@yandex-team.com> | 2024-11-19 15:16:27 +0300 |
| commit | 24521403b1c44303e043ba540c09b1fe991c7474 (patch) | |
| tree | 341d1e7206bc7c143d04d2d96f05b6dc0655606d /yql/essentials/tests/sql/suites/json | |
| parent | 72b3cd51dc3fb9d16975d353ea82fd85701393cc (diff) | |
| download | ydb-24521403b1c44303e043ba540c09b1fe991c7474.tar.gz | |
YQL-19206 Move contrib/ydb/library/yql/tests/sql/suites -> yql/essentials/tests/sql/suites
commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00
Diffstat (limited to 'yql/essentials/tests/sql/suites/json')
62 files changed, 1082 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/json/combination/nested.sql b/yql/essentials/tests/sql/suites/json/combination/nested.sql new file mode 100644 index 0000000000..39c6f64daa --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/combination/nested.sql @@ -0,0 +1,55 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Check that Json2::Parse/Serialize calls fold +$json = CAST(@@{ + "a": { + "b": { + "c": { + "d": "Matreshka!" + } + } + } +}@@ as Json); + +SELECT + JSON_VALUE( + JSON_QUERY( + JSON_QUERY( + JSON_QUERY( + $json, + "strict $.a" + ), + "strict $.b" + ), + "strict $.c" + ), + "strict $.d" + ); + +SELECT + JSON_EXISTS( + JSON_QUERY( + JSON_QUERY( + JSON_QUERY( + $json, + "strict $.a" + ), + "strict $.b" + ), + "strict $.c" + ), + "strict $.d" + ); + +SELECT + JSON_QUERY( + JSON_QUERY( + JSON_QUERY( + $json, + "strict $.a" + ), + "strict $.b" + ), + "strict $.c" + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/combination/unwrapped.sql b/yql/essentials/tests/sql/suites/json/combination/unwrapped.sql new file mode 100644 index 0000000000..fe40242344 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/combination/unwrapped.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = Unwrap(CAST(@@{"x": 1}@@ as Json)); + +SELECT + JSON_EXISTS($json, "strict $.x"), + JSON_VALUE($json, "strict $.x"), + JSON_QUERY($json, "strict $"); diff --git a/yql/essentials/tests/sql/suites/json/default.cfg b/yql/essentials/tests/sql/suites/json/default.cfg new file mode 100644 index 0000000000..d47da430a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/default.cfg @@ -0,0 +1 @@ +udf json2_udf
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/example.txt b/yql/essentials/tests/sql/suites/json/example.txt new file mode 100644 index 0000000000..a92e25dc49 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/example.txt @@ -0,0 +1,6 @@ +{"K"=101;"J"="{ \"who\": \"Fred\", \"where\": \"General Products\", \"friends\": [ { \"name\": \"Lili\", \"rank\": 5 }, {\"name\": \"Hank\", \"rank\": 7} ] }"}; +{"K"=102;"J"="{ \"who\": \"Tom\", \"where\": \"MultiCorp\", \"friends\": [ { \"name\": \"Sharon\", \"rank\": 2}, {\"name\": \"Monty\", \"rank\": 3} ] }"}; +{"K"=103;"J"="{ \"who\": \"Jack\",\"friends\": [ { \"name\": \"Connie\" } ] }"}; +{"K"=104;"J"="{ \"who\": \"Joe\",\"friends\": [ { \"name\": \"Doris\" }, {\"rank\": 1} ] }"}; +{"K"=105;"J"="{ \"who\": \"Mabel\", \"where\": \"Black Label\",\"friends\": [ { \"name\": \"Buck\", \"rank\": 6} ] }"}; +{"K"=106;"J"="{ \"who\": \"Louise\", \"where\": \"Iana\" }"};
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/example.txt.attr b/yql/essentials/tests/sql/suites/json/example.txt.attr new file mode 100644 index 0000000000..b828b62de7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/example.txt.attr @@ -0,0 +1,23 @@ +{ + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "K"; + [ + "DataType"; + "Int64" + ] + ]; + [ + "J"; + [ + "DataType"; + "Json" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/json/json_exists/common_syntax.sql b/yql/essentials/tests/sql/suites/json/json_exists/common_syntax.sql new file mode 100644 index 0000000000..008fede78d --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/common_syntax.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Null handling +SELECT + JSON_EXISTS(NULL, "strict $.key"), + JSON_EXISTS(Nothing(Json?), "strict $.key"); + +-- Casual select +$json = CAST(@@{"key": 128}@@ as Json); +SELECT + JSON_EXISTS($json, "strict $.key"); diff --git a/yql/essentials/tests/sql/suites/json/json_exists/example.cfg b/yql/essentials/tests/sql/suites/json/json_exists/example.cfg new file mode 100644 index 0000000000..ae59265f21 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/example.cfg @@ -0,0 +1,3 @@ +in T example.txt +in Filter1 json_exists/filter_example1.txt +in Filter2 json_exists/filter_example2.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/example.sql b/yql/essentials/tests/sql/suites/json/json_exists/example.sql new file mode 100644 index 0000000000..7c903f4fe8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/example.sql @@ -0,0 +1,48 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +-- These examples are taken from [ISO/IEC TR 19075-6:2017] standard (https://www.iso.org/standard/67367.html) +SELECT T.K +FROM T +WHERE JSON_EXISTS (T.J, 'lax $.where'); + +SELECT T.K +FROM T +WHERE JSON_EXISTS (T.J, 'strict $.where'); + +SELECT T.K +FROM T +WHERE JSON_EXISTS(T.J, 'strict $.where' FALSE ON ERROR); + +SELECT T.K +FROM T +WHERE JSON_EXISTS (T.J, 'strict $.friends[*].rank'); + +-- NOTE: Table "T" was renamed to "Filter{index}" to combine several filter examples in 1 test +SELECT K +FROM Filter1 +WHERE JSON_EXISTS (Filter1.J, 'lax $ ? (@.pay/@.hours > 9)'); + +SELECT K +FROM Filter1 +WHERE JSON_EXISTS (Filter1.J, 'strict $ ? (@.pay/@.hours > 9)'); + +SELECT K +FROM Filter2 +WHERE JSON_EXISTS (Filter2.J, 'lax $ ? (@.pay/@.hours > 9)'); + +SELECT K +FROM Filter2 +WHERE JSON_EXISTS (Filter2.J, 'strict $ ? (@.pay/@.hours > 9)'); + +SELECT K +FROM Filter2 +WHERE JSON_EXISTS (Filter2.J, 'lax $ ? (@.hours > 9)'); + +SELECT K +FROM Filter2 +WHERE JSON_EXISTS (Filter2.J, 'strict $ ? (@.hours > 9)'); + +-- NOTE: Standard also provides several examples with is unknown predicate. Following their inimitable style +-- standard authors do not provide data for these examples so we do not include them here diff --git a/yql/essentials/tests/sql/suites/json/json_exists/filter_example1.txt b/yql/essentials/tests/sql/suites/json/json_exists/filter_example1.txt new file mode 100644 index 0000000000..b5172d31c5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/filter_example1.txt @@ -0,0 +1,2 @@ +{"K"=101;"J"="{ \"pay\": 100, \"hours\": 10 }"}; +{"K"=102;"J"="{ \"pay\": 100, \"hours\": \"ten\" }"};
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/filter_example1.txt.attr b/yql/essentials/tests/sql/suites/json/json_exists/filter_example1.txt.attr new file mode 100644 index 0000000000..b828b62de7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/filter_example1.txt.attr @@ -0,0 +1,23 @@ +{ + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "K"; + [ + "DataType"; + "Int64" + ] + ]; + [ + "J"; + [ + "DataType"; + "Json" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/json/json_exists/filter_example2.txt b/yql/essentials/tests/sql/suites/json/json_exists/filter_example2.txt new file mode 100644 index 0000000000..49e32a09bb --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/filter_example2.txt @@ -0,0 +1,2 @@ +{"K"=101;"J"="{ \"pay\": 100, \"hours\": 10 }"}; +{"K"=102;"J"="{ \"pay\": 100, \"horas\": 10 }"};
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/filter_example2.txt.attr b/yql/essentials/tests/sql/suites/json/json_exists/filter_example2.txt.attr new file mode 100644 index 0000000000..b828b62de7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/filter_example2.txt.attr @@ -0,0 +1,23 @@ +{ + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "K"; + [ + "DataType"; + "Int64" + ] + ]; + [ + "J"; + [ + "DataType"; + "Json" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/json/json_exists/on_error.sql b/yql/essentials/tests/sql/suites/json/json_exists/on_error.sql new file mode 100644 index 0000000000..9cb826d6f9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/on_error.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_EXISTS($json, "strict $.key"), -- defaults to FALSE ON ERROR + JSON_EXISTS($json, "strict $.key" FALSE ON ERROR), + JSON_EXISTS($json, "strict $.key" TRUE ON ERROR), + JSON_EXISTS($json, "strict $.key" UNKNOWN ON ERROR);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.cfg b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.sql b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.sql new file mode 100644 index 0000000000..4670b0aa58 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/on_error_exception.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_EXISTS($json, "strict $.key" ERROR ON ERROR);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/passing.sql b/yql/essentials/tests/sql/suites/json/json_exists/passing.sql new file mode 100644 index 0000000000..0a71d78170 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/passing.sql @@ -0,0 +1,102 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Check all supported types for variables +SELECT + -- Numeric types + JSON_EXISTS( + $json, + "strict $var1 + $var2 + $var3 + $var4 + $var5 + $var6 + $var7 + $var8 + $var9 + $var10" + PASSING + CAST(1 as Int8) as var1, + CAST(2 as Uint8) as var2, + CAST(3 as Int16) as var3, + CAST(4 as Uint16) as var4, + CAST(5 as Int32) as var5, + CAST(6 as Uint32) as var6, + CAST(7 as Int64) as var7, + CAST(8 as Uint64) as var8, + CAST(9 as Double) as var9, + CAST(10 as Float) as var10 + ), + -- Time types + JSON_EXISTS( + $json, + "strict $var" + PASSING + CAST(1582044622 as Datetime) as var + ), + JSON_EXISTS( + $json, + "strict $var" + PASSING + CAST(1582044622 as Timestamp) as var + ), + JSON_EXISTS( + $json, + "strict $var" + PASSING + CAST("2020-02-18" as Date) as var + ), + -- Utf8 + JSON_EXISTS( + $json, + "strict $var" + PASSING + CAST("привет" as Utf8) as var + ), + -- Bool + JSON_EXISTS( + $json, + "strict $var" + PASSING + true as var + ), + -- Json + JSON_EXISTS( + $json, + "strict $var" + PASSING + $json as var + ), + -- Nulls + JSON_EXISTS( + $json, + "strict $var" + PASSING + Nothing(Int64?) as var + ), + JSON_EXISTS( + $json, + "strict $var" + PASSING + NULL as var + ); + +-- Check various ways to pass variable name +SELECT + JSON_EXISTS( + $json, "strict $var1" + PASSING + 123 as var1 + ), + -- NOTE: VaR1 is not casted to upper-case VAR1 as standard expects + JSON_EXISTS( + $json, "strict $VaR1" + PASSING + 123 as VaR1 + ), + JSON_EXISTS( + $json, "strict $var1" + PASSING + 123 as "var1" + ), + JSON_EXISTS( + $json, "strict $VaR1" + PASSING + 123 as "VaR1" + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.cfg b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.sql b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.sql new file mode 100644 index 0000000000..540741eeba --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_exists/passing_exception.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Tuple type is not supported for variables +SELECT + JSON_EXISTS( + $json, "strict $var" + PASSING + AsTuple(1, 2) as var + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/common_syntax.sql b/yql/essentials/tests/sql/suites/json/json_query/common_syntax.sql new file mode 100644 index 0000000000..9edb14f795 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/common_syntax.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Null handling +SELECT + JSON_QUERY(NULL, "strict $.key"), + JSON_QUERY(Nothing(Json?), "strict $.key"); + +-- Casual select +$json = CAST(@@{"key": [1, 2, 3]}@@ as Json); +SELECT + JSON_QUERY($json, "strict $.key"); diff --git a/yql/essentials/tests/sql/suites/json/json_query/example.cfg b/yql/essentials/tests/sql/suites/json/json_query/example.cfg new file mode 100644 index 0000000000..d23c2d6847 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/example.cfg @@ -0,0 +1 @@ +in T example.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/example.sql b/yql/essentials/tests/sql/suites/json/json_query/example.sql new file mode 100644 index 0000000000..bdac8376bf --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/example.sql @@ -0,0 +1,47 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +-- These examples are taken from [ISO/IEC TR 19075-6:2017] standard (https://www.iso.org/standard/67367.html) +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who, + JSON_VALUE (T.J, 'lax $.where' NULL ON EMPTY) AS Nali, + JSON_QUERY (T.J, 'lax $.friends') AS Friends +FROM T +WHERE JSON_EXISTS (T.J, 'lax $.friends'); + +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who, + JSON_VALUE (T.J, 'lax $.where' NULL ON EMPTY) AS Nali, + JSON_QUERY (T.J, 'lax $.friends' NULL ON EMPTY) AS Friends +FROM T; + +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who, + JSON_VALUE (T.J, 'lax $.where' NULL ON EMPTY) AS Nali, + JSON_QUERY (T.J, 'lax $.friends.name' WITH ARRAY WRAPPER) AS FriendsNames +FROM T; + +-- In standard this example is demonstrated using PDF table, without any queries. +-- We represent it as set of queries, using NULL as error value. +-- Each SELECT is a row in source PDF table +$J2 = CAST(@@{ "a": "[1,2]", "b": [1,2], "c": "hi"}@@ as Json); +SELECT + JSON_VALUE($J2, "strict $.a"), + JSON_VALUE($J2, "strict $.b" NULL ON ERROR), + JSON_VALUE($J2, "strict $.c"); + +SELECT + JSON_QUERY($J2, "strict $.a" WITHOUT ARRAY WRAPPER NULL ON ERROR), + JSON_QUERY($J2, "strict $.b" WITHOUT ARRAY WRAPPER), + JSON_QUERY($J2, "strict $.c" WITHOUT ARRAY WRAPPER NULL ON ERROR); + +SELECT + JSON_QUERY($J2, "strict $.a" WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY($J2, "strict $.b" WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY($J2, "strict $.c" WITH UNCONDITIONAL ARRAY WRAPPER); + +SELECT + JSON_QUERY($J2, "strict $.a" WITH CONDITIONAL ARRAY WRAPPER), + JSON_QUERY($J2, "strict $.b" WITH CONDITIONAL ARRAY WRAPPER), + JSON_QUERY($J2, "strict $.c" WITH CONDITIONAL ARRAY WRAPPER); diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_empty.sql b/yql/essentials/tests/sql/suites/json/json_query/on_empty.sql new file mode 100644 index 0000000000..e6d8d4482b --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_empty.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause empty result in lax mode +$json = CAST("{}" as Json); +SELECT + JSON_QUERY($json, "lax $.key"), -- defaults to NULL ON EMPTY + JSON_QUERY($json, "lax $.key" NULL ON EMPTY), + JSON_QUERY($json, "lax $.key" EMPTY ARRAY ON EMPTY), + JSON_QUERY($json, "lax $.key" EMPTY OBJECT ON EMPTY);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.cfg b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.sql b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.sql new file mode 100644 index 0000000000..4298669f03 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_empty_exception.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause empty result in lax mode +$json = CAST("{}" as Json); +SELECT + JSON_QUERY($json, "lax $.key" ERROR ON EMPTY);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_error.sql b/yql/essentials/tests/sql/suites/json/json_query/on_error.sql new file mode 100644 index 0000000000..048db96610 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_error.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_QUERY($json, "strict $.key"), -- defaults to NULL ON ERROR + JSON_QUERY($json, "strict $.key" NULL ON ERROR), + JSON_QUERY($json, "strict $.key" EMPTY ARRAY ON ERROR), + JSON_QUERY($json, "strict $.key" EMPTY OBJECT ON ERROR);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.cfg b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.sql b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.sql new file mode 100644 index 0000000000..6b1a7e0701 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/on_error_exception.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_QUERY($json, "strict $.key" ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_query/passing.sql b/yql/essentials/tests/sql/suites/json/json_query/passing.sql new file mode 100644 index 0000000000..f1b09b1d27 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/passing.sql @@ -0,0 +1,114 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Check all supported types for variables +SELECT + -- Numeric types + JSON_QUERY( + $json, + "strict $var1 + $var2 + $var3 + $var4 + $var5 + $var6 + $var7 + $var8 + $var9 + $var10" + PASSING + CAST(1 as Int8) as var1, + CAST(2 as Uint8) as var2, + CAST(3 as Int16) as var3, + CAST(4 as Uint16) as var4, + CAST(5 as Int32) as var5, + CAST(6 as Uint32) as var6, + CAST(7 as Int64) as var7, + CAST(8 as Uint64) as var8, + CAST(9 as Double) as var9, + CAST(10 as Float) as var10 + WITH UNCONDITIONAL ARRAY WRAPPER + ), + -- Time types + JSON_QUERY( + $json, + "strict $var" + PASSING + CAST(1582044622 as Datetime) as var + WITH UNCONDITIONAL ARRAY WRAPPER + ), + JSON_QUERY( + $json, + "strict $var" + PASSING + CAST(1582044622 as Timestamp) as var + WITH UNCONDITIONAL ARRAY WRAPPER + ), + JSON_QUERY( + $json, + "strict $var" + PASSING + CAST("2020-02-18" as Date) as var + WITH UNCONDITIONAL ARRAY WRAPPER + ), + -- Utf8 + JSON_QUERY( + $json, + "strict $var" + PASSING + CAST("привет" as Utf8) as var + WITH UNCONDITIONAL ARRAY WRAPPER + ), + -- Bool + JSON_QUERY( + $json, + "strict $var" + PASSING + true as var + WITH UNCONDITIONAL ARRAY WRAPPER + ), + -- Json + JSON_QUERY( + $json, + "strict $var" + PASSING + $json as var + ), + -- Nulls + JSON_QUERY( + $json, + "strict $var" + PASSING + Nothing(Int64?) as var + WITH UNCONDITIONAL ARRAY WRAPPER + ), + JSON_QUERY( + $json, + "strict $var" + PASSING + NULL as var + WITH UNCONDITIONAL ARRAY WRAPPER + ); + +-- Check various ways to pass variable name +SELECT + JSON_QUERY( + $json, "strict $var1" + PASSING + 123 as var1 + WITH UNCONDITIONAL ARRAY WRAPPER + ), + -- NOTE: VaR1 is not casted to upper-case VAR1 as standard expects + JSON_QUERY( + $json, "strict $VaR1" + PASSING + 123 as VaR1 + WITH UNCONDITIONAL ARRAY WRAPPER + ), + JSON_QUERY( + $json, "strict $var1" + PASSING + 123 as "var1" + WITH UNCONDITIONAL ARRAY WRAPPER + ), + JSON_QUERY( + $json, "strict $VaR1" + PASSING + 123 as "VaR1" + WITH UNCONDITIONAL ARRAY WRAPPER + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/passing_exception.cfg b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/passing_exception.sql b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.sql new file mode 100644 index 0000000000..0be7f24fa1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/passing_exception.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Tuple type is not supported for variables +SELECT + JSON_QUERY( + $json, "strict $var" + PASSING + AsTuple(1, 2) as var + WITH UNCONDITIONAL ARRAY WRAPPER + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_query/wrapper.sql b/yql/essentials/tests/sql/suites/json/json_query/wrapper.sql new file mode 100644 index 0000000000..7ec077f53c --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_query/wrapper.sql @@ -0,0 +1,54 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "object": {"key": 123}, + "multi_object": [{"key": 123}, {"key": 456}, {"key": 789}], + "array": [1, 2, 3], + "multi_array": [[1, 2, 3], [4, 5, 6], [7, 8, 9]] +}@@ as Json); + +-- Check JSON_QUERY without wrapper +SELECT + JSON_QUERY($json, "strict $.array"), -- defaults to WITHOUT ARRAY WRAPPER + JSON_QUERY($json, "strict $.array" WITHOUT WRAPPER), + JSON_QUERY($json, "strict $.array" WITHOUT ARRAY WRAPPER), + JSON_QUERY($json, "strict $.object"), -- defaults to WITHOUT ARRAY WRAPPER + JSON_QUERY($json, "strict $.object" WITHOUT WRAPPER), + JSON_QUERY($json, "strict $.object" WITHOUT ARRAY WRAPPER); + +-- Check UNCONDITIONAL wrapper +SELECT + JSON_QUERY($json, "strict $.array" WITH WRAPPER), -- default to WITH UNCONDITIONAL WRAPPER + JSON_QUERY($json, "strict $.array" WITH ARRAY WRAPPER), + JSON_QUERY($json, "strict $.array" WITH UNCONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.array" WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.object" WITH WRAPPER), -- default to WITH UNCONDITIONAL WRAPPER + JSON_QUERY($json, "strict $.object" WITH ARRAY WRAPPER), + JSON_QUERY($json, "strict $.object" WITH UNCONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.object" WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.array[*]" WITH WRAPPER), -- default to WITH UNCONDITIONAL WRAPPER + JSON_QUERY($json, "strict $.array[*]" WITH ARRAY WRAPPER), + JSON_QUERY($json, "strict $.array[*]" WITH UNCONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.array[*]" WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.multi_array[*]" WITH WRAPPER), -- default to WITH UNCONDITIONAL WRAPPER + JSON_QUERY($json, "strict $.multi_array[*]" WITH ARRAY WRAPPER), + JSON_QUERY($json, "strict $.multi_array[*]" WITH UNCONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.multi_array[*]" WITH UNCONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.multi_object[*]" WITH WRAPPER), -- default to WITH UNCONDITIONAL WRAPPER + JSON_QUERY($json, "strict $.multi_object[*]" WITH ARRAY WRAPPER), + JSON_QUERY($json, "strict $.multi_object[*]" WITH UNCONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.multi_object[*]" WITH UNCONDITIONAL ARRAY WRAPPER); + +-- Check CONDITIONAL wrapper +SELECT + JSON_QUERY($json, "strict $.array" WITH CONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.array" WITH CONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.object" WITH CONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.object" WITH CONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.array[*]" WITH CONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.array[*]" WITH CONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.multi_array[*]" WITH CONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.multi_array[*]" WITH CONDITIONAL ARRAY WRAPPER), + JSON_QUERY($json, "strict $.multi_object[*]" WITH CONDITIONAL WRAPPER), + JSON_QUERY($json, "strict $.multi_object[*]" WITH CONDITIONAL ARRAY WRAPPER); diff --git a/yql/essentials/tests/sql/suites/json/json_value/common_syntax.sql b/yql/essentials/tests/sql/suites/json/json_value/common_syntax.sql new file mode 100644 index 0000000000..19d17798b8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/common_syntax.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Null handling +SELECT + JSON_VALUE(NULL, "strict $.key"), + JSON_VALUE(Nothing(Json?), "strict $.key"); + +-- Casual select +$json = CAST(@@{"key": 128}@@ as Json); +SELECT + JSON_VALUE($json, "strict $.key"); diff --git a/yql/essentials/tests/sql/suites/json/json_value/example.cfg b/yql/essentials/tests/sql/suites/json/json_value/example.cfg new file mode 100644 index 0000000000..d23c2d6847 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/example.cfg @@ -0,0 +1 @@ +in T example.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/example.sql b/yql/essentials/tests/sql/suites/json/json_value/example.sql new file mode 100644 index 0000000000..b62ed6648b --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/example.sql @@ -0,0 +1,55 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +-- These examples are taken from [ISO/IEC TR 19075-6:2017] standard (https://www.iso.org/standard/67367.html) +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who +FROM T; + +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who, + JSON_VALUE (T.J, 'lax $.where' + NULL ON EMPTY) AS Nali +FROM T; + +SELECT T.K, + JSON_VALUE (T.J, 'strict $.who') AS Who, + JSON_VALUE (T.J, 'strict $.where' + DEFAULT 'no where there' ON ERROR ) + AS Nali +FROM T; + +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who, + JSON_VALUE (T.J, 'lax $.where' NULL ON EMPTY) AS Nali, + JSON_VALUE (T.J, 'lax $.friends.name' NULL ON EMPTY + DEFAULT '*** error ***' ON ERROR) + AS Friend +FROM T; + +SELECT T.K, + JSON_VALUE (T.J, 'strict $.who') AS Who, + JSON_VALUE (T.J, 'strict $.where' NULL ON EMPTY NULL ON ERROR) AS Nali, + + -- NOTE: output for this particular column differs with standard. + -- For row with T.K = 106 and T.J = { "who": "Louise", "where": "Iana" } output is "*** error ***", not NULL. + -- This is because answer in standard (NULL) is not correct. Query "strict $.friends[*].name" is executed in strict mode + -- where all structural errors are "hard" errors. Row with T.K = 106 does not have "friends" key in T.J. This is structural error + -- and the result of JSON_VALUE must tolerate ON ERROR section which specifies to return "*** error ***" string. + -- + -- We can check this in PostgreSQL too (at the moment of writing PostgreSQL does not support JSON_VALUE function so we use jsonb_path_query): + -- postgres=# select * from jsonb_path_query('{ "who": "Louise", "where": "Iana" }', 'strict $.friends[*].name'); + -- ERROR: JSON object does not contain key "friends" + -- PostgreSQL shows us that hard error has happened, as expected. + JSON_VALUE (T.J, 'strict $.friends[*].name' NULL ON EMPTY + DEFAULT '*** error ***' ON ERROR) + AS Friend +FROM T; + +SELECT T.K, + JSON_VALUE (T.J, 'lax $.who') AS Who, + -- NOTE: In the original example INTEGER type was used. YQL does not have INTEGER type, Int64 was used instead + JSON_VALUE (T.J, 'lax $.friends[0].rank' RETURNING Int64 NULL ON EMPTY) + AS Rank +FROM T; diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty.sql b/yql/essentials/tests/sql/suites/json/json_value/on_empty.sql new file mode 100644 index 0000000000..e8f968274e --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty.sql @@ -0,0 +1,30 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause empty result in lax mode +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "lax $.key"), -- defaults to NULL ON EMPTY + JSON_VALUE($json, "lax $.key" NULL ON EMPTY), + JSON_VALUE($json, "lax $.key" DEFAULT "*** empty ***" ON EMPTY); + +-- Null as a default value +SELECT + JSON_VALUE($json, "lax $.key" RETURNING Uint16 DEFAULT NULL ON EMPTY); + +-- Check that default value is casted to the target type +SELECT + JSON_VALUE($json, "lax $.key" RETURNING Int16 DEFAULT "123" ON EMPTY), + JSON_VALUE($json, "lax $.key" RETURNING Int16 DEFAULT 123.456 ON EMPTY); + +-- Here JsonPath engine returns empty result and ON EMPTY section must be used. +-- But default value in ON EMPTY section is -123 and casting it to Uint16 will fail. +-- In this case ON ERROR section must be returned. +SELECT + JSON_VALUE( + $json, + "lax $.key" + RETURNING Uint16 + DEFAULT -123 ON EMPTY + DEFAULT 456 ON ERROR + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.sql new file mode 100644 index 0000000000..c646cdb91f --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_cast_default_exception.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "lax $.key" RETURNING Uint32 DEFAULT -2 ON EMPTY ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.sql new file mode 100644 index 0000000000..1ec55af7ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_empty_exception.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "lax $.key" ERROR ON EMPTY); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error.sql new file mode 100644 index 0000000000..520b7f85a9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error.sql @@ -0,0 +1,28 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Accessing absent object member will cause jsonpath error in strict mode +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "strict $.key"), -- defaults to NULL ON ERROR + JSON_VALUE($json, "strict $.key" NULL ON ERROR), + JSON_VALUE($json, "strict $.key" DEFAULT "*** error ***" ON ERROR); + +-- Null as a default value +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 DEFAULT NULL ON ERROR); + +-- Check that default value is casted to the target type +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Int16 DEFAULT "123" ON ERROR), + JSON_VALUE($json, "strict $.key" RETURNING Int16 DEFAULT 123.456 ON ERROR); + +-- Here values retrieved from JSON cannot be casted to the target type Int16. +-- ON ERROR default value must be used instead +$invalid_types_json = CAST(@@{ + "key": "string", + "another_key": -123 +}@@ as Json); +SELECT + JSON_VALUE($invalid_types_json, "strict $.key" RETURNING Int16 DEFAULT 456 ON ERROR), + JSON_VALUE($invalid_types_json, "strict $.another_key" RETURNING Uint16 DEFAULT 456 ON ERROR);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.sql new file mode 100644 index 0000000000..51f4591ad7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_default_exception.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Here JsonPath engine returns error result and ON ERROR section must be used. +-- But default value in ON ERROR section is -123 and casting it to Uint16 will fail. +-- In this case exception must be raised. +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 DEFAULT -123 ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.sql new file mode 100644 index 0000000000..722a06256c --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_udf_exception.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- In this case call to Json2::SqlValueNumber will fail because "string" +-- does not represent Number value +$json = CAST(@@{ + "key": "string" +}@@ as Json); +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.sql new file mode 100644 index 0000000000..5b97c45e49 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_cast_value_exception.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- In this case call to Json2::SqlValueNumber will be successfull, but cast +-- of -123 to Uint16 will fail +$json = CAST(@@{ + "key": -123 +}@@ as Json); +SELECT + JSON_VALUE($json, "strict $.key" RETURNING Uint16 ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.sql new file mode 100644 index 0000000000..8314007547 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/on_error_jsonpath_exception.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST("{}" as Json); +SELECT + JSON_VALUE($json, "strict $.key" ERROR ON ERROR); diff --git a/yql/essentials/tests/sql/suites/json/json_value/passing.sql b/yql/essentials/tests/sql/suites/json/json_value/passing.sql new file mode 100644 index 0000000000..30f963a5f2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/passing.sql @@ -0,0 +1,102 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Check all supported types for variables +SELECT + -- Numeric types + JSON_VALUE( + $json, + "strict $var1 + $var2 + $var3 + $var4 + $var5 + $var6 + $var7 + $var8 + $var9 + $var10" + PASSING + CAST(1 as Int8) as var1, + CAST(2 as Uint8) as var2, + CAST(3 as Int16) as var3, + CAST(4 as Uint16) as var4, + CAST(5 as Int32) as var5, + CAST(6 as Uint32) as var6, + CAST(7 as Int64) as var7, + CAST(8 as Uint64) as var8, + CAST(9 as Double) as var9, + CAST(10 as Float) as var10 + ), + -- Time types + JSON_VALUE( + $json, + "strict $var" + PASSING + CAST(1582044622 as Datetime) as var + ), + JSON_VALUE( + $json, + "strict $var" + PASSING + CAST(1582044622 as Timestamp) as var + ), + JSON_VALUE( + $json, + "strict $var" + PASSING + CAST("2020-02-18" as Date) as var + ), + -- Utf8 + JSON_VALUE( + $json, + "strict $var" + PASSING + CAST("привет" as Utf8) as var + ), + -- Bool + JSON_VALUE( + $json, + "strict $var" + PASSING + true as var + ), + -- Json + JSON_VALUE( + $json, + "strict $var.key" + PASSING + $json as var + ), + -- Nulls + JSON_VALUE( + $json, + "strict $var" + PASSING + Nothing(Int64?) as var + ), + JSON_VALUE( + $json, + "strict $var" + PASSING + NULL as var + ); + +-- Check various ways to pass variable name +SELECT + JSON_VALUE( + $json, "strict $var1" + PASSING + 123 as var1 + ), + -- NOTE: VaR1 is not casted to upper-case VAR1 as standard expects + JSON_VALUE( + $json, "strict $VaR1" + PASSING + 123 as VaR1 + ), + JSON_VALUE( + $json, "strict $var1" + PASSING + 123 as "var1" + ), + JSON_VALUE( + $json, "strict $VaR1" + PASSING + 123 as "VaR1" + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/passing_exception.cfg b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/passing_exception.sql b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.sql new file mode 100644 index 0000000000..7e94a73ccc --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/passing_exception.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = CAST(@@{ + "key": 123 +}@@ as Json); + +-- Tuple type is not supported for variables +SELECT + JSON_VALUE( + $json, "strict $var" + PASSING + AsTuple(1, 2) as var + );
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/json_value/returning.sql b/yql/essentials/tests/sql/suites/json/json_value/returning.sql new file mode 100644 index 0000000000..0903fb517a --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/json_value/returning.sql @@ -0,0 +1,78 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Basic cases +$bool_json = CAST(@@{ + "key": true +}@@ as Json); +SELECT + JSON_VALUE($bool_json, "strict $.key"), -- defaults to RETURNING Utf8 with cast to string value + JSON_VALUE($bool_json, "strict $.key" RETURNING Bool); + +$string_json = CAST(@@{ + "key": "string" +}@@ as Json); +SELECT + JSON_VALUE($string_json, "strict $.key"), -- defaults to RETURNING Utf8 with cast to string value + JSON_VALUE($string_json, "strict $.key" RETURNING Utf8); + +$int64_json = CAST(@@{ + "key": 123 +}@@ as Json); +SELECT + JSON_VALUE($int64_json, "strict $.key"), -- defaults to RETURNING Utf8 with cast to string value + JSON_VALUE($int64_json, "strict $.key" RETURNING Int64); + +$double_json = CAST(@@{ + "key": 123.456 +}@@ as Json); +SELECT + JSON_VALUE($double_json, "strict $.key"), -- defaults to RETURNING Utf8 with cast to string value + JSON_VALUE($double_json, "strict $.key" RETURNING Double); + +-- Casting of result value to different types +-- From integer to various numeric types +SELECT + JSON_VALUE($int64_json, "strict $.key" RETURNING Int8), + JSON_VALUE($int64_json, "strict $.key" RETURNING Uint8), + JSON_VALUE($int64_json, "strict $.key" RETURNING Int16), + JSON_VALUE($int64_json, "strict $.key" RETURNING Uint16), + JSON_VALUE($int64_json, "strict $.key" RETURNING Int32), + JSON_VALUE($int64_json, "strict $.key" RETURNING Uint32), + JSON_VALUE($int64_json, "strict $.key" RETURNING Int64), + JSON_VALUE($int64_json, "strict $.key" RETURNING Uint64), + JSON_VALUE($int64_json, "strict $.key" RETURNING Double), + JSON_VALUE($int64_json, "strict $.key" RETURNING Float); + +-- From double to Double and Float +SELECT + JSON_VALUE($double_json, "strict $.key" RETURNING Double), + JSON_VALUE($double_json, "strict $.key" RETURNING Float); + +-- From string to Utf8 and String +SELECT + JSON_VALUE($string_json, "strict $.key" RETURNING Utf8), + JSON_VALUE($string_json, "strict $.key" RETURNING String); + +-- From timestamp to Datetime, Timestamp and Date +$date_json = CAST(@@{ + "date": 18312, + "datetime": 1582223316, + "timestamp": 1582223316529631 +}@@ as Json); +SELECT + JSON_VALUE($date_json, "strict $.date" RETURNING Date), + JSON_VALUE($date_json, "strict $.datetime" RETURNING Datetime), + JSON_VALUE($date_json, "strict $.timestamp" RETURNING Timestamp); + +-- Null result form jsonpath +$_null_json = CAST(@@{ + "key": null +}@@ as Json); +SELECT + JSON_VALUE($date_json, "strict $.key" RETURNING Int16); + +-- Error case +-- Must return NULL because NULL ON ERROR is default +SELECT + JSON_VALUE($int64_json, "strict $.key" RETURNING String); diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/input.txt b/yql/essentials/tests/sql/suites/json/jsondocument/input.txt new file mode 100644 index 0000000000..0ded9367b7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/input.txt @@ -0,0 +1,2 @@ +{"Id"=123;"Profile"="{\"age\":30,\"name\":\"First name\"}"}; +{"Id"=456;"Profile"="{\"age\":52,\"name\":\"Second name\"}"};
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/input.txt.attr b/yql/essentials/tests/sql/suites/json/jsondocument/input.txt.attr new file mode 100644 index 0000000000..e61c1d4837 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/input.txt.attr @@ -0,0 +1,23 @@ +{ + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "Id"; + [ + "DataType"; + "Int64" + ] + ]; + [ + "Profile"; + [ + "DataType"; + "JsonDocument" + ] + ] + ] + ] + } +} diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/insert.cfg b/yql/essentials/tests/sql/suites/json/jsondocument/insert.cfg new file mode 100644 index 0000000000..25c3682008 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/insert.cfg @@ -0,0 +1,2 @@ +in Input jsondocument/input.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/insert.sql b/yql/essentials/tests/sql/suites/json/jsondocument/insert.sql new file mode 100644 index 0000000000..a22fbb8390 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/insert.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +/* multirun can not */ + +INSERT INTO plato.Output +SELECT * FROM plato.Input; diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/json_exists.sql b/yql/essentials/tests/sql/suites/json/jsondocument/json_exists.sql new file mode 100644 index 0000000000..9246bbf260 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/json_exists.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = JsonDocument(@@{"a": {"b": 1}, "c": 2}@@); + +SELECT + JSON_EXISTS($json, "$.a"), + JSON_EXISTS($json, "$.b"), + JSON_EXISTS($json, "$.c"), + JSON_EXISTS(CAST(NULL as JsonDocument), "$.a");
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/json_query.sql b/yql/essentials/tests/sql/suites/json/jsondocument/json_query.sql new file mode 100644 index 0000000000..24ac8dd2aa --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/json_query.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = JsonDocument(@@{"a": {"b": 1}, "c": 2}@@); + +SELECT + JSON_QUERY($json, "$.a"), + JSON_QUERY($json, "$.b"), + JSON_QUERY($json, "$.c"), + JSON_QUERY(CAST(NULL as JsonDocument), "$.a");
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/json_value.sql b/yql/essentials/tests/sql/suites/json/jsondocument/json_value.sql new file mode 100644 index 0000000000..ed807f569c --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/json_value.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ + +$json = JsonDocument(@@{"a": {"b": 1}, "c": 2}@@); + +SELECT + JSON_VALUE($json, "$.c"), + JSON_VALUE($json, "$.b"), + JSON_VALUE($json, "$.a"), + JSON_VALUE(CAST(NULL as JsonDocument), "$.a");
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/select.cfg b/yql/essentials/tests/sql/suites/json/jsondocument/select.cfg new file mode 100644 index 0000000000..442a3ce787 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/select.cfg @@ -0,0 +1 @@ +in Input jsondocument/input.txt diff --git a/yql/essentials/tests/sql/suites/json/jsondocument/select.sql b/yql/essentials/tests/sql/suites/json/jsondocument/select.sql new file mode 100644 index 0000000000..57361db5d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsondocument/select.sql @@ -0,0 +1,7 @@ +/* postgres can not */ + +SELECT + Id, + Profile +FROM + plato.Input;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/json/jsonpath/unicode.sql b/yql/essentials/tests/sql/suites/json/jsonpath/unicode.sql new file mode 100644 index 0000000000..bce76c0aff --- /dev/null +++ b/yql/essentials/tests/sql/suites/json/jsonpath/unicode.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +-- Check access to members with unicode keys +$json = CAST(@@{ + "привет": 123 +}@@ as Json); +SELECT + JSON_EXISTS($json, @@strict $."привет"@@), + JSON_VALUE($json, @@strict $."привет"@@); + +$nested_json = CAST(@@{ + "привет": [1, 2, 3] +}@@ as Json); +SELECT + JSON_QUERY($nested_json, @@strict $."привет"@@);
\ No newline at end of file |