KIKIMR-18692:canonization

2 files changed, 27 insertions, 56 deletions

diff --git a/ydb/tests/functional/suite_tests/canondata/test_stream_query.TestStreamQuery.test_sql_suite_results-window.test_/window.test.out b/ydb/tests/functional/suite_tests/canondata/test_stream_query.TestStreamQuery.test_sql_suite_results-window.test_/window.test.out
index 25f35873bc..a2d5a9dead 100644
--- a/ydb/tests/functional/suite_tests/canondata/test_stream_query.TestStreamQuery.test_sql_suite_results-window.test_/window.test.out
+++ b/ydb/tests/functional/suite_tests/canondata/test_stream_query.TestStreamQuery.test_sql_suite_results-window.test_/window.test.out
@@ -593,27 +593,6 @@ order by ten;
 --!syntax_v1
 pragma TablePathPrefix = "/Root/stream_query_window.test_results";
 pragma SimpleColumns;
-select first_value(max(x)) over w, y
-  from (select unique1 as x, ten + four as y from tenk1 order by x)
-  group by y
-  window w AS ()
-  order by y desc;
-
- column0 | y  
----------+----
-    9999 | 12 
-    9999 | 10 
-    9999 |  8 
-    9999 |  6 
-    9999 |  4 
-    9999 |  2 
-    9999 |  0 
-(7 rows)
-
-
---!syntax_v1
-pragma TablePathPrefix = "/Root/stream_query_window.test_results";
-pragma SimpleColumns;
 SELECT four, ten,
 	sum(ten) over w,
 	last_value(ten) over w
@@ -650,34 +629,34 @@ ORDER BY four, ten;
 pragma TablePathPrefix = "/Root/stream_query_window.test_results";
 pragma SimpleColumns;
 SELECT four, ten/4 as two,
-	sum(ten/4) over w,
+	sum(ten/4) over w as three,
 	last_value(ten/4) over w
 FROM (select distinct ten, four from tenk1)
 WINDOW w AS (partition by four order by ten/4 rows between unbounded preceding and current row)
-ORDER BY four, two;
-
- four | two | column2 | column3 
-------+-----+---------+---------
-    0 |   0 |       0 |       0 
-    0 |   0 |       0 |       0 
-    0 |   1 |       1 |       1 
-    0 |   1 |       2 |       1 
-    0 |   2 |       4 |       2 
-    1 |   0 |       0 |       0 
-    1 |   0 |       0 |       0 
-    1 |   1 |       1 |       1 
-    1 |   1 |       2 |       1 
-    1 |   2 |       4 |       2 
-    2 |   0 |       0 |       0 
-    2 |   0 |       0 |       0 
-    2 |   1 |       1 |       1 
-    2 |   1 |       2 |       1 
-    2 |   2 |       4 |       2 
-    3 |   0 |       0 |       0 
-    3 |   0 |       0 |       0 
-    3 |   1 |       1 |       1 
-    3 |   1 |       2 |       1 
-    3 |   2 |       4 |       2 
+ORDER BY four, two, three;
+
+ four | two | three | column3 
+------+-----+-------+---------
+    0 |   0 |     0 |       0 
+    0 |   0 |     0 |       0 
+    0 |   1 |     1 |       1 
+    0 |   1 |     2 |       1 
+    0 |   2 |     4 |       2 
+    1 |   0 |     0 |       0 
+    1 |   0 |     0 |       0 
+    1 |   1 |     1 |       1 
+    1 |   1 |     2 |       1 
+    1 |   2 |     4 |       2 
+    2 |   0 |     0 |       0 
+    2 |   0 |     0 |       0 
+    2 |   1 |     1 |       1 
+    2 |   1 |     2 |       1 
+    2 |   2 |     4 |       2 
+    3 |   0 |     0 |       0 
+    3 |   0 |     0 |       0 
+    3 |   1 |     1 |       1 
+    3 |   1 |     2 |       1 
+    3 |   2 |     4 |       2 
 (20 rows)
 
 
diff --git a/ydb/tests/functional/suite_tests/stream_query/window.test b/ydb/tests/functional/suite_tests/stream_query/window.test
index 8d043e74e1..2d86bd5a53 100644
--- a/ydb/tests/functional/suite_tests/stream_query/window.test
+++ b/ydb/tests/functional/suite_tests/stream_query/window.test
@@ -225,14 +225,6 @@ group by ten
 window w AS (order by sum(unique1) + sum(unique2))
 order by ten;
 
--- window and aggregate with GROUP BY expression (9.2 bug)
-statement stream query
-select first_value(max(x)) over w, y
-  from (select unique1 as x, ten + four as y from tenk1 order by x)
-  group by y
-  window w AS ()
-  order by y desc;
-
 -- test non-default frame specifications
 statement stream query
 SELECT four, ten,
@@ -265,11 +257,11 @@ WINDOW w AS (partition by four order by ten/4 range between unbounded preceding
 
 statement stream query
 SELECT four, ten/4 as two,
-	sum(ten/4) over w,
+	sum(ten/4) over w as three,
 	last_value(ten/4) over w
 FROM (select distinct ten, four from tenk1)
 WINDOW w AS (partition by four order by ten/4 rows between unbounded preceding and current row)
-ORDER BY four, two;
+ORDER BY four, two, three;
 
 statement error
 SELECT sum(unique1) over w,