Merge branch 'rightlib' into mergelibs-241120-1113

14 files changed, 125 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.sql
new file mode 100644
index 0000000000..aca3f1c6e6
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.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 BY key ORDER BY value ROWS BETWEEN 3 FOLLOWING AND 5 FOLLOWING),
+    w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.sql
new file mode 100644
index 0000000000..e3adba7ded
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.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 BY key ORDER BY value ROWS BETWEEN 5 PRECEDING AND 3 PRECEDING),
+    w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.sql
new file mode 100644
index 0000000000..8b43cd6699
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.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 BY key ORDER BY value ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING),
+    w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.sql
new file mode 100644
index 0000000000..f301c179de
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.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 BY key ORDER BY value ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
+    w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.sql
new file mode 100644
index 0000000000..2b744948ca
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_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 (PARTITION BY key ORDER BY value ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING),
+    w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/generic/session.cfg b/yql/essentials/tests/sql/suites/window/generic/session.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/session.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/session.sql b/yql/essentials/tests/sql/suites/window/generic/session.sql
new file mode 100644
index 0000000000..998f12e1ea
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/session.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 BY user, SessionWindow(ts, 10)
+    ORDER BY ts
+    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
+)
+ORDER BY user, payload;
diff --git a/yql/essentials/tests/sql/suites/window/generic/session_aliases.cfg b/yql/essentials/tests/sql/suites/window/generic/session_aliases.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/session_aliases.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/generic/session_aliases.sql b/yql/essentials/tests/sql/suites/window/generic/session_aliases.sql
new file mode 100644
index 0000000000..2375d56064
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/generic/session_aliases.sql
@@ -0,0 +1,23 @@
+/* syntax version 1 */
+/* postgres can not */
+
+SELECT
+    user,
+    ts,
+    SessionStart() over w1 as ss1,
+    SessionStart() over w as ss,
+    
+    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
+    ORDER BY ts
+    ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING
+),
+w1 AS (
+    PARTITION BY SessionWindow(ts, 10), user
+    ORDER BY ts
+    ROWS BETWEEN 100 PRECEDING AND 100 FOLLOWING
+)
+ORDER BY user, ts;