Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

13 files changed, 280 insertions, 0 deletions

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);