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

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

30 files changed, 282 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations.sql b/yql/essentials/tests/sql/suites/window/full/aggregations.sql
new file mode 100644
index 0000000000..b413db5f7c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations.sql
@@ -0,0 +1,16 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    value,
+    SUM(unwrap(cast(subkey as uint32))) over w1 as sum1,
+    COUNT(*)                            over w1 as count1,
+    ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1,
+
+    SUM(cast(subkey as uint32))         over w2 as sum2,
+    AGGREGATE_LIST(subkey)              over w2 as agglist2,
+FROM (SELECT * FROM plato.Input WHERE key = '1')
+WINDOW
+    w1 as (),
+    w2 as (PARTITION BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_compact.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_compact.sql b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.sql
new file mode 100644
index 0000000000..1c18473298
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.sql
@@ -0,0 +1,16 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    value,
+    SUM(unwrap(cast(subkey as uint32))) over w1 as sum1,
+    COUNT(*)                            over w1 as count1,
+    ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1,
+
+    SUM(cast(subkey as uint32))         over w2 as sum2,
+    AGGREGATE_LIST(subkey)              over w2 as agglist2,
+FROM (SELECT * FROM plato.Input WHERE key = '1')
+WINDOW
+    w1 as (PARTITION COMPACT BY ()),
+    w2 as (PARTITION COMPACT BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.sql
new file mode 100644
index 0000000000..413e1a0ddd
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.sql
@@ -0,0 +1,15 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    value,
+    SUM(unwrap(cast(subkey as uint32))) over w1 as sum1,
+    LEAD(value || value, 3)             over w1 as dvalue_lead1,
+
+    SUM(cast(subkey as uint32))         over w2 as sum2,
+    LAG(cast(value as uint32))          over w2 as value_lag2,
+FROM (SELECT * FROM plato.Input WHERE key = '1')
+WINDOW
+    w1 as (ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+    w2 as (PARTITION BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.sql b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.sql
new file mode 100644
index 0000000000..c68f9946a1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.sql
@@ -0,0 +1,15 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    value,
+    SUM(unwrap(cast(subkey as uint32))) over w1 as sum1,
+    LEAD(value || value, 3)             over w1 as dvalue_lead1,
+
+    SUM(cast(subkey as uint32))         over w2 as sum2,
+    LAG(cast(value as uint32))          over w2 as value_lag2,
+FROM (SELECT * FROM plato.Input WHERE key = '1')
+WINDOW
+    w1 as (PARTITION COMPACT BY ()  ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+    w2 as (PARTITION COMPACT BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag.cfg b/yql/essentials/tests/sql/suites/window/full/leadlag.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/leadlag.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag.sql b/yql/essentials/tests/sql/suites/window/full/leadlag.sql
new file mode 100644
index 0000000000..1fccdfd276
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/leadlag.sql
@@ -0,0 +1,17 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$in = SELECT
+    value,
+    SUM(unwrap(cast(subkey as uint32))) over w1 as sum1,
+    LEAD(value || value, 3)             over w1 as dvalue_lead1,
+
+    SUM(cast(subkey as uint32))         over w2 as sum2,
+    LAG(cast(value as uint32))          over w2 as value_lag2,
+FROM (SELECT * FROM plato.Input WHERE key = '1')
+WINDOW
+    w1 as (ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+    w2 as (PARTITION BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+;
+
+SELECT value, dvalue_lead1, value_lag2 FROM $in ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag_compact.cfg b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag_compact.sql b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.sql
new file mode 100644
index 0000000000..45a8a6232f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.sql
@@ -0,0 +1,17 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$in = SELECT
+    value,
+    SUM(unwrap(cast(subkey as uint32))) over w1 as sum1,
+    LEAD(value || value, 3)             over w1 as dvalue_lead1,
+
+    SUM(cast(subkey as uint32))         over w2 as sum2,
+    LAG(cast(value as uint32))          over w2 as value_lag2,
+FROM (SELECT * FROM plato.Input WHERE key = '1')
+WINDOW
+    w1 as (PARTITION /*+ COMPACT() */ BY ()  ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+    w2 as (PARTITION COMPACT BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+;
+
+SELECT value, dvalue_lead1, value_lag2 FROM $in ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.cfg b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.sql b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.sql
new file mode 100644
index 0000000000..019551ef7e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.sql
@@ -0,0 +1,23 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$input = (
+SELECT * FROM plato.Input WHERE key = '1'
+UNION ALL
+SELECT NULL AS key, "9" as subkey, "000" as value
+UNION ALL
+SELECT NULL AS key, "9" as subkey, "001" as value
+);
+
+
+SELECT
+    key,
+    subkey,
+    value,
+
+    AGGREGATE_LIST(value)   over w1 as agglist1,
+
+FROM $input
+WINDOW
+    w1 as (PARTITION BY key, subkey ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.cfg b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.sql b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.sql
new file mode 100644
index 0000000000..81c3fe43ff
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.sql
@@ -0,0 +1,23 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$input = (
+SELECT * FROM plato.Input WHERE key = '1'
+UNION ALL
+SELECT NULL AS key, "9" as subkey, "000" as value
+UNION ALL
+SELECT NULL AS key, "9" as subkey, "001" as value
+);
+
+
+SELECT
+    key,
+    subkey,
+    value,
+
+    AGGREGATE_LIST(value)   over w1 as agglist1,
+
+FROM $input
+WINDOW
+    w1 as (PARTITION BY (key, subkey) as pkey ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.cfg b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.cfg
new file mode 100644
index 0000000000..bcf4082c5c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.cfg
@@ -0,0 +1 @@
+in Input input_optkey2.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.sql b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.sql
new file mode 100644
index 0000000000..1b3778fc96
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    key,
+    subkey,
+    value,
+
+    AGGREGATE_LIST(TableRow()) OVER w AS frame,
+
+FROM plato.Input
+WINDOW
+    w as (PARTITION BY key, subkey ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/full/session.cfg b/yql/essentials/tests/sql/suites/window/full/session.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/session.sql b/yql/essentials/tests/sql/suites/window/full/session.sql
new file mode 100644
index 0000000000..76ee7bd0c8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session.sql
@@ -0,0 +1,21 @@
+/* syntax version 1 */
+/* postgres can not */
+USE plato;
+
+-- add non-optional partition key
+$src = SELECT t.*, user ?? "u0" as user_nonopt FROM Input as t;
+
+SELECT
+    user,
+    user_nonopt,
+    ts,
+    payload,
+    AGGREGATE_LIST(TableRow()) over w as full_session,
+    COUNT(1) over w as session_len,
+FROM $src
+WINDOW w AS (
+    PARTITION BY user, user_nonopt, SessionWindow(ts, 10)
+    ORDER BY ts
+    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)
+ORDER BY user, payload;
diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases.cfg b/yql/essentials/tests/sql/suites/window/full/session_aliases.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_aliases.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases.sql b/yql/essentials/tests/sql/suites/window/full/session_aliases.sql
new file mode 100644
index 0000000000..b809c6b534
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_aliases.sql
@@ -0,0 +1,19 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    user,
+    ts,
+    SessionStart() over w1 as ss1,
+    SessionStart() over w as ss,
+    
+    ListSort(AGGREGATE_LIST(ts) over w) as ts_session,
+    COUNT(1) over w as session_len,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION BY user, SessionWindow(ts, 10) as ss0
+),
+w1 AS (
+    PARTITION BY SessionWindow(ts, 10), user
+)
+ORDER BY user, ts;
diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.cfg b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.sql b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.sql
new file mode 100644
index 0000000000..2bd49f12f1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.sql
@@ -0,0 +1,19 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    user,
+    ts,
+    SessionStart() over w1 as ss1,
+    SessionStart() over w as ss,
+    
+    ListSort(AGGREGATE_LIST(ts) over w) as ts_session,
+    COUNT(1) over w as session_len,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION COMPACT BY user, SessionWindow(ts, 10) as ss0
+),
+w1 AS (
+    PARTITION COMPACT BY SessionWindow(ts, 10), user
+)
+ORDER BY user, ts;
diff --git a/yql/essentials/tests/sql/suites/window/full/session_compact.cfg b/yql/essentials/tests/sql/suites/window/full/session_compact.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_compact.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/session_compact.sql b/yql/essentials/tests/sql/suites/window/full/session_compact.sql
new file mode 100644
index 0000000000..8c86df8e1c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_compact.sql
@@ -0,0 +1,16 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    user,
+    ts,
+    payload,
+    AGGREGATE_LIST(ts) over w as ts_session,
+    COUNT(1) over w as session_len,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION COMPACT BY user, SessionWindow(ts, 10)
+    ORDER BY ts
+    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)
+ORDER BY user, payload;
diff --git a/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.cfg b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.sql b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.sql
new file mode 100644
index 0000000000..6280f571af
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.sql
@@ -0,0 +1,21 @@
+/* syntax version 1 */
+/* postgres can not */
+USE plato;
+
+-- add non-optional partition key
+$src = SELECT t.*, user ?? "u0" as user_nonopt FROM Input as t;
+
+SELECT
+    user,
+    user_nonopt,
+    ts,
+    payload,
+    AGGREGATE_LIST(TableRow()) over w as full_session,
+    COUNT(1) over w as session_len,
+FROM $src
+WINDOW w AS (
+    PARTITION BY user, user_nonopt, SessionWindow(ts, 10)
+    ORDER BY ts DESC
+    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)
+ORDER BY user, payload;
diff --git a/yql/essentials/tests/sql/suites/window/full/syscolumns.cfg b/yql/essentials/tests/sql/suites/window/full/syscolumns.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/syscolumns.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/full/syscolumns.sql b/yql/essentials/tests/sql/suites/window/full/syscolumns.sql
new file mode 100644
index 0000000000..d2533c062b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/full/syscolumns.sql
@@ -0,0 +1,15 @@
+/* syntax version 1 */
+/* postgres can not */
+use plato;
+
+select
+  value,
+  max(value) over (partition by cast(TableName() as Utf8)),
+  cast(TableName()  as Utf8),
+from Input order by value;
+
+select
+  value,
+  max(value) over (order by cast(TableName() as Utf8) rows between unbounded preceding and unbounded following),
+  cast(TableName()  as Utf8),
+from Input order by value;