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

Library import 241120-1113

1 files changed, 52 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/in/in_ansi_join.sql b/yql/essentials/tests/sql/suites/in/in_ansi_join.sql
new file mode 100644
index 0000000000..48bfb9d637
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/in/in_ansi_join.sql
@@ -0,0 +1,52 @@
+/* syntax version 1 */
+/* postgres can not */
+
+PRAGMA AnsiInForEmptyOrNullableItemsCollections;
+
+USE plato;
+
+-- Input: optkeys are 1-10,null
+-- Dict: keys are 1,5,7,9,11,null
+
+-- Either side can be: non-optional (P), optional without nulls (O), optional with nulls (N) - we test each of possible combination
+
+$lp = (SELECT Unwrap(optkey) AS optkey FROM Input WHERE optkey IS NOT NULL);
+$lo = (SELECT optkey FROM Input WHERE optkey IS NOT NULL);
+$ln = (SELECT optkey FROM Input);
+
+$rp = (SELECT Unwrap(key) AS key FROM Dict WHERE key IS NOT NULL);
+$ro = (SELECT key FROM Dict WHERE key IS NOT NULL);
+$rn = (SELECT key FROM Dict);
+
+
+-- Right is P
+SELECT optkey FROM $lp WHERE optkey NOT IN $rp ORDER BY optkey; -- [2,3,4,6,8,10]
+SELECT optkey FROM $lo WHERE optkey NOT IN $rp ORDER BY optkey; -- [2,3,4,6,8,10]
+SELECT optkey FROM $ln WHERE optkey NOT IN $rp ORDER BY optkey; -- [2,3,4,6,8,10]
+
+-- Right is O
+SELECT optkey FROM $lp WHERE optkey NOT IN $ro ORDER BY optkey; -- [2,3,4,6,8,10]
+SELECT optkey FROM $lo WHERE optkey NOT IN $ro ORDER BY optkey; -- [2,3,4,6,8,10]
+SELECT optkey FROM $ln WHERE optkey NOT IN $ro ORDER BY optkey; -- [2,3,4,6,8,10]
+
+-- Right is N
+SELECT optkey FROM $lp WHERE optkey NOT IN $rn ORDER BY optkey; -- []
+SELECT optkey FROM $lo WHERE optkey NOT IN $rn ORDER BY optkey; -- []
+SELECT optkey FROM $ln WHERE optkey NOT IN $rn ORDER BY optkey; -- []
+
+
+-- 2, 4, 6, null
+$extraDict = (
+    SELECT 2 AS key UNION ALL
+    SELECT 4 AS key UNION ALL
+    SELECT 6 AS key UNION ALL
+    SELECT NULL AS key
+);
+
+SELECT optkey FROM $lo WHERE optkey != 10 AND optkey NOT IN $ro AND optkey IN $extraDict AND optkey != 4 ORDER BY optkey; -- [2,6]
+SELECT optkey FROM $ln WHERE optkey != 10 AND optkey NOT IN $rn AND optkey IN $extraDict AND optkey != 4 ORDER BY optkey; -- []
+
+
+-- Empty dict
+SELECT optkey FROM $ln WHERE optkey NOT IN (SELECT * FROM $rn LIMIT 0) ORDER BY optkey; -- [1-10,null]
+