Merge branch 'rightlib' into mergelibs-241120-1113

13 files changed, 248 insertions, 0 deletions

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