YQL-19206 Move contrib/ydb/library/yql/tests/sql/suites -> yql/essentials/tests/sql/suites

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

21 files changed, 374 insertions, 0 deletions

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