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

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

63 files changed, 501 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/flatten_by/default.cfg b/yql/essentials/tests/sql/suites/flatten_by/default.cfg
new file mode 100644
index 0000000000..58878f8945
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/default.cfg
@@ -0,0 +1 @@
+in Input input.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_and_where.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_and_where.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_and_where.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_and_where.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_and_where.sql
new file mode 100644
index 0000000000..df60a53b83
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_and_where.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data = (select mod, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select mod, iv from $data as d flatten by lv as iv where iv < 'd' order by mod, iv;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_by_aster_opt.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_aster_opt.sql
new file mode 100644
index 0000000000..7294949685
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_aster_opt.sql
@@ -0,0 +1,3 @@
+/* postgres can not */
+/* syntax version 1 */
+select * from (select d.*, Just(key) as ok from plato.Input as d) flatten by ok;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_by_group_by_alias_collision.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_group_by_alias_collision.sql
new file mode 100644
index 0000000000..abf34243eb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_group_by_alias_collision.sql
@@ -0,0 +1,11 @@
+/* syntax version 1 */
+
+$data = [<|key:1, subkeys:[1,2,2,3,4,5]|>, <|key:2, subkeys:[1,2,3,5,6,8]|>];
+
+SELECT 
+    subkey,
+    COUNT(key) as cnt
+FROM AS_TABLE($data)
+FLATTEN LIST BY subkeys as subkey
+GROUP BY CAST(subkey as String) as subkey
+ORDER BY subkey;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_by_opt_dict.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_opt_dict.cfg
new file mode 100644
index 0000000000..bf1b560b58
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_opt_dict.cfg
@@ -0,0 +1 @@
+in Input input_intersect_sorted.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_by_opt_dict.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_opt_dict.sql
new file mode 100644
index 0000000000..1182060308
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_opt_dict.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data_dict = (select mod, Just(ToDict(ListEnumerate(ListTake(ListSort(aggregate_list(key)), 1)))) as dk, ListTake(ListSort(aggregate_list(value)), 1) as lv from plato.Input group by cast(subkey as uint32) % 10 as mod);
+
+select * from $data_dict flatten dict by dk order by mod;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_by_typed_table.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_typed_table.cfg
new file mode 100644
index 0000000000..eaccb9ef55
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_typed_table.cfg
@@ -0,0 +1 @@
+in Input typed_table.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_by_typed_table.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_typed_table.sql
new file mode 100644
index 0000000000..10cf6ca887
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_by_typed_table.sql
@@ -0,0 +1,3 @@
+/* postgres can not */
+/* syntax version 1 */
+select bb from plato.Input view opt_struct flatten by b as bb;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_columns.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns.sql
new file mode 100644
index 0000000000..12fe13e98d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns.sql
@@ -0,0 +1,10 @@
+/* postgres can not */
+USE plato;
+
+SELECT * FROM (
+SELECT
+    AsStruct(key as key, subkey as subkey),
+    AsStruct("value: " || value as value)
+FROM Input
+)
+FLATTEN COLUMNS;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_by_aggregate.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_by_aggregate.sql
new file mode 100644
index 0000000000..3ab01cc18d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_by_aggregate.sql
@@ -0,0 +1,16 @@
+/* postgres can not */
+USE plato;
+
+$input = (SELECT *
+    FROM (
+        SELECT Unwrap(some(row))
+        FROM (
+            SELECT TableRow() as row
+            FROM Input
+        )
+    ) FLATTEN COLUMNS
+);
+
+--INSERT INTO Output WITH TRUNCATE
+SELECT *
+FROM $input
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_non_struct.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_non_struct.sql
new file mode 100644
index 0000000000..c8fe587c6e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_non_struct.sql
@@ -0,0 +1,6 @@
+/* postgres can not */
+select * from (
+select 1,AsStruct(2 as foo),Just(AsStruct(3 as bar)),
+Just(AsStruct(Just(4) as qwe))
+)
+flatten columns;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_with_opt_struct.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_with_opt_struct.sql
new file mode 100644
index 0000000000..80ad6d7a82
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_columns_with_opt_struct.sql
@@ -0,0 +1,6 @@
+SELECT
+    *
+FROM (
+    SELECT JUST(<|col1: 1, col2: NULL, col3: Just(3), col4: 4p|>)
+)
+FLATTEN COLUMNS;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_corr_name_column.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_corr_name_column.sql
new file mode 100644
index 0000000000..666a9f5706
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_corr_name_column.sql
@@ -0,0 +1,15 @@
+/* syntax version 1 */
+/* postgres can not */
+USE plato;
+
+$data = select 1 as n, AsList(4, 5, 6) as l, AsStruct(10 as n, AsList(1, 2, 3) as l) as s union all
+        select 2 as n, AsList(4, 5)    as l, AsStruct(20 as n, AsList(1, 2)    as l) as s;
+
+select n,l  from $data as l flatten by l   order by n,l;
+select n,l  from $data as l flatten by l.l order by n,l;
+
+select n,l    from $data as s flatten by s.l order by n,l;
+select n,newl from $data as s flatten by (s.l as newl) order by n,newl;
+
+select n,l from $data as s flatten by (s.s.l as l) order by n,l;
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_dict.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict.cfg
new file mode 100644
index 0000000000..bf1b560b58
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict.cfg
@@ -0,0 +1 @@
+in Input input_intersect_sorted.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_dict.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict.sql
new file mode 100644
index 0000000000..a9618c7aac
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict.sql
@@ -0,0 +1,13 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma sampleselect;
+
+$data_dict = (select mod, YQL::ToIndexDict(ListSort(aggregate_list(key))) as dk, ListSort(aggregate_list(subkey)) as ls, ListSort(aggregate_list(value)) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select
+  mod, iv, ls,
+  dd.di.0 as key,
+  dd.di.1 as value
+from $data_dict as dd
+flatten by (dk as di, lv as iv, ls)
+order by mod, iv, ls, key, value;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_dict_by_opt.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict_by_opt.cfg
new file mode 100644
index 0000000000..bf1b560b58
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict_by_opt.cfg
@@ -0,0 +1 @@
+in Input input_intersect_sorted.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_dict_by_opt.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict_by_opt.sql
new file mode 100644
index 0000000000..79acee152b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_dict_by_opt.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data_dict = (select mod, YQL::ToIndexDict(ListTake(ListSort(aggregate_list(Just(key))), 1)) as dk, ListTake(ListSort(aggregate_list(subkey)), 1) as ls from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select * from $data_dict flatten by (dk as di, ls, mod) order by mod;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr.cfg
new file mode 100644
index 0000000000..5c248bff3d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr.cfg
@@ -0,0 +1 @@
+udf string_udf
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr.sql
new file mode 100644
index 0000000000..e2b17cb4cc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$data = SELECT "a,b,c,d" AS a, "e,f,g,h" AS b, "x" AS c;
+
+SELECT a,bb,c FROM $data FLATTEN BY (String::SplitToList(a, ",") as a, String::SplitToList(b, ",") as bb) ORDER BY a,bb;
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_groupby.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_groupby.cfg
new file mode 100644
index 0000000000..5c248bff3d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_groupby.cfg
@@ -0,0 +1 @@
+udf string_udf
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_groupby.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_groupby.sql
new file mode 100644
index 0000000000..235e70d859
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_groupby.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$data = SELECT "a,b,c,d" AS a, "e,f,g,h" AS b, "x" AS c;
+
+SELECT bb,count(*) as count FROM $data FLATTEN BY (String::SplitToList(a, ",") as a, String::SplitToList(b, ",") as bb) GROUP BY bb ORDER BY bb,count;
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_join.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_join.cfg
new file mode 100644
index 0000000000..c1a1de6dd0
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_join.cfg
@@ -0,0 +1,4 @@
+in Input input.txt
+udf string_udf
+providers yt
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_join.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_join.sql
new file mode 100644
index 0000000000..810c8b615d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_join.sql
@@ -0,0 +1,8 @@
+/* syntax version 1 */
+/* postgres can not */
+USE plato;
+
+$data = SELECT "075,020,075,020" AS a, "x" AS c;
+
+SELECT * FROM ANY $data as x FLATTEN BY (String::SplitToList(a, ",") as aa) JOIN Input as y ON x.aa = y.key ORDER BY aa;
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_struct.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_struct.sql
new file mode 100644
index 0000000000..35ca94eeb8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_struct.sql
@@ -0,0 +1,12 @@
+/* syntax version 1 */
+/* postgres can not */
+USE plato;
+
+$data = select 1 as n, AsList(4, 5, 6) as l, AsStruct(10 as n, AsList(1, 2, 3) as l) as s union all
+        select 2 as n, AsList(4, 5)    as l, AsStruct(20 as n, AsList(1, 2)    as l) as s;
+
+select n,l from $data      flatten by s.l as l order by n,l;
+select n,l from $data      flatten by (s.l as l) order by n,l;
+select n,l from $data      flatten by (ListExtend(s.l, AsList(100)) as l) order by n,l;
+
+select n,l,sl from $data flatten by (l, s.l as sl) order by n,l,sl  
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_where.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_where.cfg
new file mode 100644
index 0000000000..5c248bff3d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_where.cfg
@@ -0,0 +1 @@
+udf string_udf
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_where.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_where.sql
new file mode 100644
index 0000000000..f2ba420046
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_expr_where.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$data = SELECT "a,b,c,d" AS a, "e,f,g,h" AS b, "x" AS c;
+
+SELECT a,bb,c FROM $data FLATTEN BY (String::SplitToList(a, ",") as a, String::SplitToList(b, ",") as bb) WHERE bb != "h" ORDER BY a,bb;
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_few_fields.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_few_fields.cfg
new file mode 100644
index 0000000000..bf1b560b58
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_few_fields.cfg
@@ -0,0 +1 @@
+in Input input_intersect_sorted.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_few_fields.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_few_fields.sql
new file mode 100644
index 0000000000..2f7ccd4bbb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_few_fields.sql
@@ -0,0 +1,8 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma sampleselect;
+
+$data_deep = (select mod, aggregate_list(key) as lk, aggregate_list(subkey) as ls, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+-- order to have same results on yamr and yt
+select * from $data_deep flatten by (lk as ik, ls, lv) order by mod, ik, ls, lv;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_list.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_list.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_list.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_list.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_list.sql
new file mode 100644
index 0000000000..4a7c16bac7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_list.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data = (select mod, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select mod, iv from $data flatten by lv as iv order by mod, iv;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.cfg
new file mode 100644
index 0000000000..2513e0a9c7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.cfg
@@ -0,0 +1 @@
+in Input flatten_list_on_flatten_by.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.sql
new file mode 100644
index 0000000000..dfb14aea15
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.sql
@@ -0,0 +1,22 @@
+/* syntax version 1 */
+USE plato;
+
+$flatten = (
+    SELECT
+        answer_and_clicks.0 AS permalink,
+       bc_type_tuple
+    FROM Input AS a
+    FLATTEN BY parsed_answers_and_clicks as answer_and_clicks
+    WHERE answer_and_clicks.1 = 1
+);
+
+SELECT 
+    bc_type,
+    permalink,
+FROM (
+    SELECT
+        asList(bc_type_tuple.0, 'total') as bc_type,
+        a.* WITHOUT bc_type_tuple
+    FROM $flatten as a
+ )
+FLATTEN LIST BY bc_type;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.txt b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.txt
new file mode 100644
index 0000000000..933b47e67d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.txt
@@ -0,0 +1,9 @@
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[1022482316;];1;["bizfinder";];];];};
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[63320754500;];1;["bizfinder";];];];};
+{"bc_type_tuple"=["unknown";"other";];"parsed_answers_and_clicks"=[[[56725259;];1;["geocoder";];];];};
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[1393063799;];1;["yabs";];];];};
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[1102340823;];1;["bizfinder";];];];};
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[1030456454;];1;["yabs";];];[[1023091930;];0;["yabs";];];[[91758116818;];0;["bizfinder";];];[[1089475933;];0;["yabs";];];[[1001186033;];0;["bizfinder";];];[[1746904934;];0;["bizfinder";];];[[16660487830;];0;["bizfinder";];];[[1927062611;];0;["bizfinder";];];[[211087055144;];0;["bizfinder";];];[[240631521347;];0;["bizfinder";];];];};
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[1128735493;];1;["bizfinder";];];[[1093055816;];0;["bizfinder";];];[[1101660521;];0;["bizfinder";];];];};
+{"bc_type_tuple"=["not_discovery";"1org";];"parsed_answers_and_clicks"=[[[1159830271;];1;["bizfinder";];];[[1030802514;];0;["bizfinder";];];];};
+
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.txt.attr b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.txt.attr
new file mode 100644
index 0000000000..1f5a7568d4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_list_on_flatten_by.txt.attr
@@ -0,0 +1,55 @@
+{
+"_yql_row_spec"= {
+        "StrictSchema"= true;
+"Type"= [
+            "StructType";
+            [
+                [
+                    "bc_type_tuple";
+                    [
+                        "TupleType";
+                        [
+                            [
+                                "DataType";
+                                "String"
+                            ];
+                            [
+                                "DataType";
+                                "String"
+                            ]
+                        ]
+                    ]
+                ];
+                [
+                    "parsed_answers_and_clicks";
+                    [
+                        "ListType";
+                        [
+                            "TupleType";
+                            [
+                                [
+                                    "OptionalType";
+                                    [
+                                        "DataType";
+                                        "Int64"
+                                    ]
+                                ];
+                                [
+                                    "DataType";
+                                    "Int32"
+                                ];
+                                [
+                                    "OptionalType";
+                                    [
+                                        "DataType";
+                                        "String"
+                                    ]
+                                ]
+                            ]
+                        ]
+                    ]
+                ]
+            ]
+        ]
+    };
+}
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_member_is_struct.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_member_is_struct.cfg
new file mode 100644
index 0000000000..ff4186ea03
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_member_is_struct.cfg
@@ -0,0 +1 @@
+in Input input_opt_struct_field.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_member_is_struct.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_member_is_struct.sql
new file mode 100644
index 0000000000..20bcbb42be
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_member_is_struct.sql
@@ -0,0 +1,12 @@
+/* postgres can not */
+USE plato;
+
+--INSERT INTO Output
+SELECT
+  countIf(kkstritem % 10 == 0) as count_dec,
+  countIf(kkstritem < 100) as count_small,
+  countIf(kkstritem > 900) as count_huge
+FROM (
+      SELECT keyitem.kk as kkstr FROM Input as d FLATTEN BY key as keyitem
+)
+FLATTEN BY kkstr as kkstritem
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_mode.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_mode.sql
new file mode 100644
index 0000000000..655c4940e1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_mode.sql
@@ -0,0 +1,40 @@
+/* postgres can not */
+USE plato;
+
+SELECT
+    *
+FROM (SELECT Just(1) AS x)
+FLATTEN OPTIONAL BY x;
+
+$lst = AsList(1,2,3);
+SELECT
+    *
+FROM (SELECT $lst AS x)
+FLATTEN LIST BY x ORDER BY x;
+
+SELECT
+    x
+FROM (SELECT Just($lst) AS x)
+FLATTEN LIST BY x ORDER BY x;
+
+SELECT
+    *
+FROM (SELECT Just($lst) AS x)
+FLATTEN OPTIONAL BY x ORDER BY x;
+
+$dct = AsDict(AsTuple(1,"foo"),AsTuple(2,"bar"),AsTuple(3,"baz"));
+
+SELECT
+    *
+FROM (SELECT $dct AS x)
+FLATTEN DICT BY x ORDER BY x;
+
+SELECT
+    x
+FROM (SELECT Just($dct) AS x)
+FLATTEN DICT BY x ORDER BY x;
+
+SELECT
+    ListSort(DictItems(x))
+FROM (SELECT Just($dct) AS x)
+FLATTEN OPTIONAL BY x;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field.cfg
new file mode 100644
index 0000000000..bf1b560b58
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field.cfg
@@ -0,0 +1 @@
+in Input input_intersect_sorted.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field.sql
new file mode 100644
index 0000000000..c96cad25fb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data = (select mod, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select * from $data flatten by lv as iv order by iv;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field_another.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field_another.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field_another.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field_another.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field_another.sql
new file mode 100644
index 0000000000..6145dcecbd
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_one_field_another.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data = (select mod, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select d.lv, d.mod  from $data as d flatten by (lv) order by lv;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_two_fields.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_two_fields.cfg
new file mode 100644
index 0000000000..bf1b560b58
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_two_fields.cfg
@@ -0,0 +1 @@
+in Input input_intersect_sorted.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_two_fields.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_two_fields.sql
new file mode 100644
index 0000000000..85633c4ae5
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_two_fields.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data_deep = (select mod, aggregate_list(key) as lk, aggregate_list(subkey) as ls, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select * from $data_deep flatten by (lk as ik, lv) order by ik, lv, mod;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by.sql
new file mode 100644
index 0000000000..9d68c54e9f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data_deep = (select mod, aggregate_list(cast(key as uint32)) as lk, aggregate_list(cast(subkey as uint32)) as ls, Count(*) as cc from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select sum(cc) as sc, sum(mod) as sm from $data_deep as d flatten by (lk as itk, ls as its) group by its + itk as ss order by sc, sm;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by_expr.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by_expr.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by_expr.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by_expr.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by_expr.sql
new file mode 100644
index 0000000000..cb6da4021f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_group_by_expr.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+$data_deep = (select mod, aggregate_list(cast(key as uint32)) as lk, aggregate_list(cast(subkey as uint32)) as ls, Count(*) as cc from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select ss, sum(cc) as sc, sum(mod) as sm from $data_deep as d flatten by (lk as itk, ls as its) group by its + itk as ss order by ss;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_join.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_join.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_join.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_join.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_join.sql
new file mode 100644
index 0000000000..b7139810ec
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_join.sql
@@ -0,0 +1,9 @@
+/* syntax version 1 */
+/* postgres can not */
+$data = (select mod, aggregate_list(value) as lv from plato.Input group by cast(key as uint32) % 10 as mod);
+
+select d.mod, d.lv, j.key
+from $data as d
+flatten by lv
+join plato.Input as j on d.mod == cast(j.key as uint32) / 10 % 10
+order by d.mod, d.lv;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_resource.cfg b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_resource.cfg
new file mode 100644
index 0000000000..289c4251bb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_resource.cfg
@@ -0,0 +1,2 @@
+in Input input.txt
+udf python3_udf
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_resource.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_resource.sql
new file mode 100644
index 0000000000..8dda0726f4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_resource.sql
@@ -0,0 +1,19 @@
+/* postgres can not */
+/* kikimr can not */
+/* syntax version 1 */
+$script = @@
+def save(item):
+    return item
+
+def load(item):
+    return item
+@@;
+
+$save = Python3::save(Callable<(String)->Resource<Python3>>, $script);
+$load = Python3::load(Callable<(Resource<Python3>)->String>, $script);
+
+$input = (
+    SELECT key, AsList($save(value), $save(subkey)) AS resourceList FROM plato.Input
+);
+
+SELECT key, $load(resourceList) AS value FROM $input FLATTEN BY resourceList;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/flatten_with_subquery.sql b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_subquery.sql
new file mode 100644
index 0000000000..ac7cab2094
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/flatten_with_subquery.sql
@@ -0,0 +1,9 @@
+/* syntax version 1 */
+/* postgres can not */
+
+define subquery $bar() as
+    select [1,2] as ks; 
+end define;
+
+select key from $bar() flatten list by ks as key order by key;
+select key from $bar() flatten list by (ListExtend(ks, [3]) as key) order by key;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/input.txt b/yql/essentials/tests/sql/suites/flatten_by/input.txt
new file mode 100644
index 0000000000..65949ea745
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/input.txt
@@ -0,0 +1,4 @@
+{"key"="075";"subkey"="1";"value"="abc"};
+{"key"="800";"subkey"="2";"value"="ddd"};
+{"key"="020";"subkey"="3";"value"="q"};
+{"key"="150";"subkey"="4";"value"="qzz"};
diff --git a/yql/essentials/tests/sql/suites/flatten_by/input_intersect.txt b/yql/essentials/tests/sql/suites/flatten_by/input_intersect.txt
new file mode 100644
index 0000000000..42bcf2179e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/input_intersect.txt
@@ -0,0 +1,14 @@
+{"key"="075";"subkey"="911";"value"="abc"};
+{"key"="911";"subkey"="1";"value"="kkk"};
+{"key"="023";"subkey"="527";"value"="aaa"};
+{"key"="527";"subkey"="023";"value"="bbb"};
+{"key"="037";"subkey"="075";"value"="ddd"};
+{"key"="761";"subkey"="911";"value"="ccc"};
+{"key"="200";"subkey"="075";"value"="qqq"};
+{"key"="150";"subkey"="075";"value"="zzz"};
+{"key"="023";"subkey"="911";"value"="vca"};
+{"key"="527";"subkey"="150";"value"="oef"};
+{"key"="037";"subkey"="761";"value"="vdf"};
+{"key"="761";"subkey"="037";"value"="aet"};
+{"key"="200";"subkey"="150";"value"="fdb"};
+{"key"="150";"subkey"="037";"value"="bfs"};
diff --git a/yql/essentials/tests/sql/suites/flatten_by/input_intersect_sorted.txt b/yql/essentials/tests/sql/suites/flatten_by/input_intersect_sorted.txt
new file mode 100644
index 0000000000..617e73e92f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/input_intersect_sorted.txt
@@ -0,0 +1,14 @@
+{"key"="023";"subkey"="527";"value"="aaa"};
+{"key"="023";"subkey"="911";"value"="vca"};
+{"key"="037";"subkey"="075";"value"="ddd"};
+{"key"="037";"subkey"="761";"value"="vdf"};
+{"key"="075";"subkey"="911";"value"="abc"};
+{"key"="150";"subkey"="037";"value"="bfs"};
+{"key"="150";"subkey"="075";"value"="zzz"};
+{"key"="200";"subkey"="075";"value"="qqq"};
+{"key"="200";"subkey"="150";"value"="fdb"};
+{"key"="527";"subkey"="023";"value"="bbb"};
+{"key"="527";"subkey"="150";"value"="oef"};
+{"key"="761";"subkey"="037";"value"="aet"};
+{"key"="761";"subkey"="911";"value"="ccc"};
+{"key"="911";"subkey"="1";"value"="kkk"};
diff --git a/yql/essentials/tests/sql/suites/flatten_by/input_opt_struct_field.txt b/yql/essentials/tests/sql/suites/flatten_by/input_opt_struct_field.txt
new file mode 100644
index 0000000000..72dd9c3bfb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/input_opt_struct_field.txt
@@ -0,0 +1,12 @@
+{"key"=[[]];"subkey"="3";"value"="zer"};
+{"key"=[[]];"subkey"="9";"value"="ger"};
+{"key"=[[ 23u]];"subkey"="3";"value"="aaa"};
+{"key"=[[ 37u]];"subkey"="5";"value"="ddd"};
+{"key"=[[ 75u]];"subkey"="1";"value"="abc"};
+{"key"=[[150u]];"subkey"="1";"value"="aaa"};
+{"key"=[[150u]];"subkey"="3";"value"="iii"};
+{"key"=[[150u]];"subkey"="8";"value"="zzz"};
+{"key"=[[200u]];"subkey"="7";"value"="qqq"};
+{"key"=[[527u]];"subkey"="4";"value"="bbb"};
+{"key"=[[761u]];"subkey"="6";"value"="ccc"};
+{"key"=[[911u]];"subkey"="2";"value"="kkk"};
diff --git a/yql/essentials/tests/sql/suites/flatten_by/input_opt_struct_field.txt.attr b/yql/essentials/tests/sql/suites/flatten_by/input_opt_struct_field.txt.attr
new file mode 100644
index 0000000000..b5a76775f2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/input_opt_struct_field.txt.attr
@@ -0,0 +1,11 @@
+{
+    "_yql_row_spec"={
+        "Type"=["StructType";[
+            ["key";["OptionalType";["StructType";[
+                ["kk";["OptionalType";["DataType";"Uint32"]]];
+            ]]]];
+            ["subkey";["DataType";"String"]];
+            ["value";["DataType";"String"]];
+        ]];
+    }
+}
diff --git a/yql/essentials/tests/sql/suites/flatten_by/struct_with_wrong_correlation.sqlx b/yql/essentials/tests/sql/suites/flatten_by/struct_with_wrong_correlation.sqlx
new file mode 100644
index 0000000000..78e5c04267
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/struct_with_wrong_correlation.sqlx
@@ -0,0 +1,10 @@
+/* postgres can not */
+pragma sampleselect;
+use plato;
+
+$data_dict = (select mod, some(AsStruct(key as s, subkey as subkey)) as list_struct from Input group by cast(key as uint32) % 10 as mod order by mod);
+
+select
+  zz.s -- try with invalid correlation (show as wrong column)
+from $data_dict as dd
+flatten by list_struct as s
diff --git a/yql/essentials/tests/sql/suites/flatten_by/struct_without_correlation.sql b/yql/essentials/tests/sql/suites/flatten_by/struct_without_correlation.sql
new file mode 100644
index 0000000000..b280873d88
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/struct_without_correlation.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma sampleselect;
+use plato;
+
+$data_dict = (select mod, aggregate_list(AsStruct(key as `struct`, subkey as subkey)) as list_struct from Input group by cast(key as uint32) % 10 as mod);
+
+--insert into plato.Output
+select
+  mod, `struct`.`struct`
+from $data_dict as dd
+flatten by list_struct as `struct`
+order by mod, column1;
+--order by mod, iv, ls;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/table_funcs_spec_flatten_by.sqlx b/yql/essentials/tests/sql/suites/flatten_by/table_funcs_spec_flatten_by.sqlx
new file mode 100644
index 0000000000..a4815a1bc5
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/table_funcs_spec_flatten_by.sqlx
@@ -0,0 +1,10 @@
+/* postgres can not */
+use plato;
+
+insert into Output
+select
+  key,
+  value,
+  TablePath()
+from Input flatten by (key)
+;
diff --git a/yql/essentials/tests/sql/suites/flatten_by/typed_table.txt b/yql/essentials/tests/sql/suites/flatten_by/typed_table.txt
new file mode 100644
index 0000000000..cc1df00106
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/typed_table.txt
@@ -0,0 +1,4 @@
+{"key"="075";"subkey"="";"value"="a7c"};
+{"key"="800";"subkey"="2";"value"="ddd"};
+{"key"="020";"subkey"="";"value"="q"};
+{"key"="150";"subkey"="4";"value"="q8z"};
diff --git a/yql/essentials/tests/sql/suites/flatten_by/typed_table.txt.attr b/yql/essentials/tests/sql/suites/flatten_by/typed_table.txt.attr
new file mode 100644
index 0000000000..67a1eb4bde
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/flatten_by/typed_table.txt.attr
@@ -0,0 +1,31 @@
+{
+    "_yql_view_opt_struct"="SELECT YQL::AsList(1,2,3) as b FROM self;";
+    "_yql_row_spec" = {
+        "Type" = [
+            "StructType";
+            [
+                [
+                    "key";
+                    [
+                        "DataType";
+                        "String"
+                    ]
+                ];
+                [
+                    "subkey";
+                    [
+                        "DataType";
+                        "String"
+                    ]
+                ];
+                [
+                    "value";
+                    [
+                        "DataType";
+                        "String"
+                    ]
+                ]
+            ]
+        ]
+    }
+}