Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 30 insertions, 0 deletions

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