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

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

18 files changed, 177 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations.cfg b/yql/essentials/tests/sql/suites/window/current/aggregations.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/aggregations.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations.sql b/yql/essentials/tests/sql/suites/window/current/aggregations.sql
new file mode 100644
index 0000000000..444ea196cc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/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 plato.Input
+WINDOW
+    w1 as (PARTITION BY key ORDER BY value),
+    w2 as (                 ORDER BY value)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.cfg
new file mode 100644
index 0000000000..000848a05d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.cfg
@@ -0,0 +1 @@
+in Input input5.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.sql
new file mode 100644
index 0000000000..c73f26cb67
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/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 plato.Input
+WINDOW
+    w1 as (PARTITION BY key ORDER BY value),
+    w2 as (                 ORDER BY value)
+ORDER BY value;
diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current.cfg b/yql/essentials/tests/sql/suites/window/current/ansi_current.cfg
new file mode 100644
index 0000000000..812be3893b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/ansi_current.cfg
@@ -0,0 +1 @@
+in Input input4.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current.sql b/yql/essentials/tests/sql/suites/window/current/ansi_current.sql
new file mode 100644
index 0000000000..d005c57dc2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/ansi_current.sql
@@ -0,0 +1,15 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma AnsiCurrentRow;
+
+SELECT
+    value,
+    key,
+    subkey,
+    SUM(cast(subkey as Int32)) over w as subkey_sum,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION BY value
+    ORDER BY key
+)
+ORDER BY value, key, subkey;
diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.cfg b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.cfg
new file mode 100644
index 0000000000..812be3893b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.cfg
@@ -0,0 +1 @@
+in Input input4.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.sql b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.sql
new file mode 100644
index 0000000000..b89caf963f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.sql
@@ -0,0 +1,25 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma AnsiCurrentRow;
+
+SELECT
+    value,
+    key,
+    subkey,
+    SUM(cast(subkey as Int32)) over w  as subkey_sum_ansi,
+    SUM(cast(subkey as Int32)) over w1 as subkey_sum,
+    SUM(cast(subkey as Int32)) over w2 as subkey_sum_next,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION BY value
+    ORDER BY key
+), w1 AS (
+    PARTITION BY value
+    ORDER BY key
+    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+), w2 AS (
+    PARTITION BY value
+    ORDER BY key
+    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+)
+ORDER BY value, key, subkey;
diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.cfg b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.cfg
new file mode 100644
index 0000000000..812be3893b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.cfg
@@ -0,0 +1 @@
+in Input input4.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.sql b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.sql
new file mode 100644
index 0000000000..8baa348440
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.sql
@@ -0,0 +1,16 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma AnsiCurrentRow;
+
+SELECT
+    value,
+    key,
+    subkey,
+    SUM(cast(subkey as Int32)) over w  as subkey_sum_ansi,
+    LEAD(cast(subkey as Int32)) over w  as subkey_next,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION BY value
+    ORDER BY key
+)
+ORDER BY value, key, subkey;
diff --git a/yql/essentials/tests/sql/suites/window/current/session.cfg b/yql/essentials/tests/sql/suites/window/current/session.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/session.sql b/yql/essentials/tests/sql/suites/window/current/session.sql
new file mode 100644
index 0000000000..10ae2c44d3
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session.sql
@@ -0,0 +1,15 @@
+/* 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
+)
+ORDER BY user, payload;
diff --git a/yql/essentials/tests/sql/suites/window/current/session_aliases.cfg b/yql/essentials/tests/sql/suites/window/current/session_aliases.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session_aliases.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/session_aliases.sql b/yql/essentials/tests/sql/suites/window/current/session_aliases.sql
new file mode 100644
index 0000000000..9aa080e22c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session_aliases.sql
@@ -0,0 +1,21 @@
+/* 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
+),
+w1 AS (
+    PARTITION BY SessionWindow(ts, 10), user
+    ORDER BY ts
+)
+ORDER BY user, ts, session_len;
diff --git a/yql/essentials/tests/sql/suites/window/current/session_extended.cfg b/yql/essentials/tests/sql/suites/window/current/session_extended.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session_extended.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/session_extended.sql b/yql/essentials/tests/sql/suites/window/current/session_extended.sql
new file mode 100644
index 0000000000..5990b4ec12
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session_extended.sql
@@ -0,0 +1,29 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$init = ($row) -> (AsStruct($row.ts ?? 0 as value, 1 as count));
+
+$calculate = ($_row, $state) -> ($state.value);
+-- split partition into two-element grooups, make session key to be cumulative sum of ts from partition start
+$update = ($row, $state) -> {
+  $state = AsStruct($state.count + 1 as count, $state.value as value);
+  $state = AsStruct($state.count as count, $state.value + ($row.ts ?? 0) as value);
+  return  AsTuple(Unwrap($state.count % 2) == 1, $state);
+};
+
+
+SELECT
+    user,
+    ts,
+    payload,
+    AGGREGATE_LIST(cast(ts as string) ?? "null") over w as ts_session,
+    COUNT(1) over w as session_len,
+    SessionStart() over w as session_start,
+    SessionState() over w as session_state,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION BY user, SessionWindow(ts + 1, $init, $update, $calculate)
+    ORDER BY ts
+)
+ORDER BY user, payload;
+
diff --git a/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.cfg b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.cfg
new file mode 100644
index 0000000000..c788a7d1ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.cfg
@@ -0,0 +1 @@
+in Input session1.txt
diff --git a/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.sql b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.sql
new file mode 100644
index 0000000000..81efa941a6
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.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,
+    SessionStart() over w as session_start,
+FROM plato.Input
+WINDOW w AS (
+    PARTITION BY SessionWindow(ts, 10), user
+    ORDER BY payload
+)
+ORDER BY user, payload;