MR: no ORDER BY in streaming mode

commit_hash:120a06d5f684791ad1474522263398dfedf8da22

4 files changed, 146 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 74ea11e4a39..e299c4d970d 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -734,6 +734,20 @@
             "uri": "https://{canondata_backend}/1942278/bea251ec797c6ae6c79a3fa31fd0d3dbee273fa6/resource.tar.gz#test.test_library-library_yqls--Results_/results.txt"
         }
     ],
+    "test.test[match_recognize-alerts_without_order-streaming-default.txt-Debug]": [
+        {
+            "checksum": "da3c36fb5e54fd02cedfd15c0d0b9d0b",
+            "size": 5896,
+            "uri": "https://{canondata_backend}/937458/61af1cc3453c6ab9c5a837eeb404bf874f130293/resource.tar.gz#test.test_match_recognize-alerts_without_order-streaming-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[match_recognize-alerts_without_order-streaming-default.txt-Results]": [
+        {
+            "checksum": "6f125fabed1ec00aaab81efa4ab4a1b3",
+            "size": 4625,
+            "uri": "https://{canondata_backend}/937458/61af1cc3453c6ab9c5a837eeb404bf874f130293/resource.tar.gz#test.test_match_recognize-alerts_without_order-streaming-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[match_recognize-greedy_quantifiers-default.txt-Debug]": [
         {
             "checksum": "66fb0a8ccd3814cb306c356fcecea0d1",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 2328e0f0a6d..8b6291d7191 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -4185,6 +4185,13 @@
             "uri": "https://{canondata_backend}/1920236/5e37b541c71c89b1b95dee0463a5a2e9bc5999f4/resource.tar.gz#test_sql2yql.test_match_recognize-alerts_/sql.yql"
         }
     ],
+    "test_sql2yql.test[match_recognize-alerts_without_order-streaming]": [
+        {
+            "checksum": "2544bb720aab6ef9d8d57f909f58ce8f",
+            "size": 9925,
+            "uri": "https://{canondata_backend}/1925842/7d0ab953a9979e9baa7ae26ebae2128b1cbe8128/resource.tar.gz#test_sql2yql.test_match_recognize-alerts_without_order-streaming_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[match_recognize-alerts_without_order]": [
         {
             "checksum": "7e6cd1cda9ddc8a2fe0f41ace902517e",
@@ -10213,6 +10220,11 @@
             "uri": "file://test_sql_format.test_match_recognize-alerts_/formatted.sql"
         }
     ],
+    "test_sql_format.test[match_recognize-alerts_without_order-streaming]": [
+        {
+            "uri": "file://test_sql_format.test_match_recognize-alerts_without_order-streaming_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[match_recognize-alerts_without_order]": [
         {
             "uri": "file://test_sql_format.test_match_recognize-alerts_without_order_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_match_recognize-alerts_without_order-streaming_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_match_recognize-alerts_without_order-streaming_/formatted.sql
new file mode 100644
index 00000000000..8ba43bd8299
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_match_recognize-alerts_without_order-streaming_/formatted.sql
@@ -0,0 +1,56 @@
+$osquery_data = [
+    <|dt: 1688910000, host: 'fqdn1', ev_type: 'someEv', ev_status: '', user: '', vpn: FALSE,|>,
+    <|dt: 1688910050, host: 'fqdn2', ev_type: 'login', ev_status: 'success', user: '', vpn: TRUE,|>,
+    <|dt: 1688910100, host: 'fqdn1', ev_type: 'login', ev_status: 'success', user: '', vpn: TRUE,|>,
+    <|dt: 1688910220, host: 'fqdn1', ev_type: 'login', ev_status: 'success', user: '', vpn: FALSE,|>,
+    <|dt: 1688910300, host: 'fqdn1', ev_type: 'delete_all', ev_status: '', user: '', vpn: FALSE,|>,
+    <|dt: 1688910400, host: 'fqdn2', ev_type: 'delete_all', ev_status: '', user: '', vpn: FALSE,|>,
+    <|dt: 1688910500, host: 'fqdn1', ev_type: 'login', ev_status: 'failed', user: 'user1', vpn: FALSE,|>,
+    <|dt: 1688910500, host: 'fqdn1', ev_type: 'login', ev_status: 'failed', user: 'user2', vpn: FALSE,|>,
+    <|dt: 1688910600, host: 'fqdn', ev_type: 'someEv', ev_status: '', user: 'user1', vpn: FALSE,|>,
+    <|dt: 1688910800, host: 'fqdn2', ev_type: 'login', ev_status: 'failed', user: 'user1', vpn: FALSE,|>,
+    <|dt: 1688910900, host: 'fqdn2', ev_type: 'login', ev_status: 'failed', user: 'user2', vpn: FALSE,|>,
+    <|dt: 1688911000, host: 'fqdn2', ev_type: 'login', ev_status: 'success', user: 'user1', vpn: FALSE,|>,
+    <|dt: 1688911001, host: 'fqdn2', ev_type: 'login', ev_status: 'success', user: 'user1', vpn: FALSE,|>,
+];
+
+PRAGMA FeatureR010 = 'prototype';
+PRAGMA config.flags('MatchRecognizeStream', 'force');
+
+SELECT
+    *
+FROM
+    AS_TABLE($osquery_data) MATCH_RECOGNIZE (
+        MEASURES
+            LAST(LOGIN_SUCCESS_REMOTE.host) AS remote_login_host,
+            LAST(LOGIN_SUCCESS_REMOTE.user) AS remote_login_user,
+            LAST(LOGIN_SUCCESS_REMOTE.dt) AS remote_login_dt,
+            LAST(SUSPICIOUS_ACTION_SOON.dt) AS suspicious_action_dt,
+            FIRST(LOGIN_FAILED_SAME_USER.dt) AS brutforce_begin,
+            FIRST(LOGIN_SUCCESS_SAME_USER.dt) AS brutforce_end,
+            LAST(LOGIN_SUCCESS_SAME_USER.user) AS brutforce_login
+        ONE ROW PER MATCH
+        AFTER MATCH SKIP TO NEXT ROW
+        PATTERN (LOGIN_SUCCESS_REMOTE ANY_ROW1 * SUSPICIOUS_ACTION_SOON | (LOGIN_FAILED_SAME_USER ANY_ROW2 *) {2,} LOGIN_SUCCESS_SAME_USER)
+        DEFINE
+            LOGIN_SUCCESS_REMOTE AS LOGIN_SUCCESS_REMOTE.ev_type == 'login'
+            AND LOGIN_SUCCESS_REMOTE.ev_status == 'success'
+            AND LOGIN_SUCCESS_REMOTE.vpn == TRUE
+            AND COALESCE(LOGIN_SUCCESS_REMOTE.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE),
+            ANY_ROW1 AS COALESCE(ANY_ROW1.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE),
+            SUSPICIOUS_ACTION_SOON AS SUSPICIOUS_ACTION_SOON.host == LAST(LOGIN_SUCCESS_REMOTE.host)
+            AND SUSPICIOUS_ACTION_SOON.ev_type == 'delete_all'
+            AND COALESCE(SUSPICIOUS_ACTION_SOON.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE),
+            LOGIN_FAILED_SAME_USER AS LOGIN_FAILED_SAME_USER.ev_type == 'login'
+            AND LOGIN_FAILED_SAME_USER.ev_status != 'success'
+            AND (
+                LAST(LOGIN_FAILED_SAME_USER.user) IS NULL
+                OR LAST(LOGIN_FAILED_SAME_USER.user) == LOGIN_FAILED_SAME_USER.user
+            ) AND COALESCE(LOGIN_FAILED_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE),
+            ANY_ROW2 AS COALESCE(ANY_ROW2.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE),
+            LOGIN_SUCCESS_SAME_USER AS LOGIN_SUCCESS_SAME_USER.ev_type == 'login'
+            AND LOGIN_SUCCESS_SAME_USER.ev_status == 'success'
+            AND LOGIN_SUCCESS_SAME_USER.user == LAST(LOGIN_FAILED_SAME_USER.user)
+            AND COALESCE(LOGIN_SUCCESS_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE)
+    ) AS MATCHED
+;
diff --git a/yql/essentials/tests/sql/suites/match_recognize/alerts_without_order-streaming.sql b/yql/essentials/tests/sql/suites/match_recognize/alerts_without_order-streaming.sql
new file mode 100644
index 00000000000..8c627a65cde
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/match_recognize/alerts_without_order-streaming.sql
@@ -0,0 +1,64 @@
+$osquery_data = [
+<|dt:1688910000, host:"fqdn1", ev_type:"someEv",     ev_status:"",        user:"",       vpn:false, |>,
+<|dt:1688910050, host:"fqdn2", ev_type:"login",      ev_status:"success", user:"",       vpn:true,  |>,
+<|dt:1688910100, host:"fqdn1", ev_type:"login",      ev_status:"success", user:"",       vpn:true,  |>,
+<|dt:1688910220, host:"fqdn1", ev_type:"login",      ev_status:"success", user:"",       vpn:false, |>,
+<|dt:1688910300, host:"fqdn1", ev_type:"delete_all", ev_status:"",        user:"",       vpn:false, |>,
+<|dt:1688910400, host:"fqdn2", ev_type:"delete_all", ev_status:"",        user:"",       vpn:false, |>,
+<|dt:1688910500, host:"fqdn1", ev_type:"login",      ev_status:"failed",  user:"user1",  vpn:false, |>,
+<|dt:1688910500, host:"fqdn1", ev_type:"login",      ev_status:"failed",  user:"user2",  vpn:false, |>,
+<|dt:1688910600, host:"fqdn",  ev_type:"someEv",     ev_status:"",        user:"user1",  vpn:false, |>,
+<|dt:1688910800, host:"fqdn2", ev_type:"login",      ev_status:"failed",  user:"user1",  vpn:false, |>,
+<|dt:1688910900, host:"fqdn2", ev_type:"login",      ev_status:"failed",  user:"user2",  vpn:false, |>,
+<|dt:1688911000, host:"fqdn2", ev_type:"login",      ev_status:"success", user:"user1",  vpn:false, |>,
+<|dt:1688911001, host:"fqdn2", ev_type:"login",      ev_status:"success", user:"user1",  vpn:false, |>,
+];
+
+pragma FeatureR010="prototype";
+pragma config.flags("MatchRecognizeStream", "force");
+
+SELECT *
+FROM AS_TABLE($osquery_data) MATCH_RECOGNIZE(
+    MEASURES
+      LAST(LOGIN_SUCCESS_REMOTE.host) as remote_login_host,
+      LAST(LOGIN_SUCCESS_REMOTE.user) as remote_login_user,
+      LAST(LOGIN_SUCCESS_REMOTE.dt) as remote_login_dt,
+      LAST(SUSPICIOUS_ACTION_SOON.dt) as suspicious_action_dt,
+      FIRST(LOGIN_FAILED_SAME_USER.dt) as brutforce_begin,
+      FIRST(LOGIN_SUCCESS_SAME_USER.dt) as brutforce_end,
+      LAST(LOGIN_SUCCESS_SAME_USER.user) as brutforce_login
+
+    ONE ROW PER MATCH
+    AFTER MATCH SKIP TO NEXT ROW
+    PATTERN (
+      LOGIN_SUCCESS_REMOTE ANY_ROW1* SUSPICIOUS_ACTION_SOON |
+      (LOGIN_FAILED_SAME_USER ANY_ROW2*){2,} LOGIN_SUCCESS_SAME_USER
+    )
+    DEFINE
+        LOGIN_SUCCESS_REMOTE as
+            LOGIN_SUCCESS_REMOTE.ev_type = "login" and
+            LOGIN_SUCCESS_REMOTE.ev_status = "success" and
+            LOGIN_SUCCESS_REMOTE.vpn = true and
+            COALESCE(LOGIN_SUCCESS_REMOTE.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE),
+        ANY_ROW1 as
+            COALESCE(ANY_ROW1.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE),
+        SUSPICIOUS_ACTION_SOON as
+            SUSPICIOUS_ACTION_SOON.host = LAST(LOGIN_SUCCESS_REMOTE.host) and
+            SUSPICIOUS_ACTION_SOON.ev_type = "delete_all" and
+            COALESCE(SUSPICIOUS_ACTION_SOON.dt - FIRST(LOGIN_SUCCESS_REMOTE.dt) <= 500, TRUE),
+        LOGIN_FAILED_SAME_USER as
+            LOGIN_FAILED_SAME_USER.ev_type = "login" and
+            LOGIN_FAILED_SAME_USER.ev_status <> "success" and
+            (LAST(LOGIN_FAILED_SAME_USER.user) IS NULL
+                or LAST(LOGIN_FAILED_SAME_USER.user) = LOGIN_FAILED_SAME_USER.user
+            ) and COALESCE(LOGIN_FAILED_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE),
+        ANY_ROW2 as
+            COALESCE(ANY_ROW2.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE),
+        LOGIN_SUCCESS_SAME_USER as
+            LOGIN_SUCCESS_SAME_USER.ev_type = "login" and
+            LOGIN_SUCCESS_SAME_USER.ev_status = "success" and
+            LOGIN_SUCCESS_SAME_USER.user = LAST(LOGIN_FAILED_SAME_USER.user) and
+            COALESCE(LOGIN_SUCCESS_SAME_USER.dt - FIRST(LOGIN_FAILED_SAME_USER.dt) <= 500, TRUE)
+) AS MATCHED
+;
+