Merge branch 'rightlib' into mergelibs-241120-1113

27 files changed, 127 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/distinct/default.cfg b/yql/essentials/tests/sql/suites/distinct/default.cfg
new file mode 100644
index 0000000000..389a8e8f21
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/default.cfg
@@ -0,0 +1,4 @@
+in Input input.txt
+in Input2 input2.txt
+in Input3 input3.txt
+in Input4 input4.txt
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_and_join.cfg b/yql/essentials/tests/sql/suites/distinct/distinct_and_join.cfg
new file mode 100644
index 0000000000..3df7813790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_and_join.cfg
@@ -0,0 +1 @@
+in Input input_intersect.txt
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_and_join.sql b/yql/essentials/tests/sql/suites/distinct/distinct_and_join.sql
new file mode 100644
index 0000000000..3f36ed9841
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_and_join.sql
@@ -0,0 +1,8 @@
+SELECT
+DISTINCT
+    i1.key,
+    i2.key,
+    i1.subkey
+FROM plato.Input AS i1 JOIN plato.Input AS i2 on i1.key == i2.subkey
+ORDER BY i1.key, i2.key, i1.subkey
+;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_by_tuple.sql b/yql/essentials/tests/sql/suites/distinct/distinct_by_tuple.sql
new file mode 100644
index 0000000000..ba6c707502
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_by_tuple.sql
@@ -0,0 +1,2 @@
+/* postgres can not */
+select distinct key from (select AsTuple(Just(key), Just(key)) as key, subkey, value from plato.Input) order by key;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_columns.sql b/yql/essentials/tests/sql/suites/distinct/distinct_columns.sql
new file mode 100644
index 0000000000..75428df492
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_columns.sql
@@ -0,0 +1 @@
+select distinct key, subkey, value from plato.Input3 order by key, subkey;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_columns_after_group.sql b/yql/essentials/tests/sql/suites/distinct/distinct_columns_after_group.sql
new file mode 100644
index 0000000000..0d98c79f1d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_columns_after_group.sql
@@ -0,0 +1,4 @@
+/* postgres can not */
+select distinct avg_key, min_val from (
+    select avg(cast(key as int)) as avg_key, min(value) as min_val from plato.Input3 group by subkey
+) as x order by avg_key, min_val;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_count_and_avg.sql b/yql/essentials/tests/sql/suites/distinct/distinct_count_and_avg.sql
new file mode 100644
index 0000000000..ac99b592f4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_count_and_avg.sql
@@ -0,0 +1 @@
+select count(distinct key) as count, avg(numKey) as avg from (select key, cast(key as int) as numKey, value from plato.Input2) as x group by value order by count;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_count_and_full_count.sql b/yql/essentials/tests/sql/suites/distinct/distinct_count_and_full_count.sql
new file mode 100644
index 0000000000..84900dc016
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_count_and_full_count.sql
@@ -0,0 +1 @@
+select value, count(distinct key) as dist, count(key) as full from plato.Input2 group by value order by value;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_count_no_gouping.sql b/yql/essentials/tests/sql/suites/distinct/distinct_count_no_gouping.sql
new file mode 100644
index 0000000000..aec6be38e1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_count_no_gouping.sql
@@ -0,0 +1 @@
+select count(distinct key) as dist, count(key) as full from plato.Input2;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_count_only.sql b/yql/essentials/tests/sql/suites/distinct/distinct_count_only.sql
new file mode 100644
index 0000000000..45c1ec2740
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_count_only.sql
@@ -0,0 +1 @@
+select count(distinct key) from plato.Input2;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_groupby.sql b/yql/essentials/tests/sql/suites/distinct/distinct_groupby.sql
new file mode 100644
index 0000000000..232a2b5a96
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_groupby.sql
@@ -0,0 +1,6 @@
+/* syntax version 1 */
+
+use plato;
+
+select distinct k || "_" as k1, "_" || v as v1 from Input2 group by key as k, value as v order by k1,v1;
+
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_having_no_agg.sql b/yql/essentials/tests/sql/suites/distinct/distinct_having_no_agg.sql
new file mode 100644
index 0000000000..dfc8943362
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_having_no_agg.sql
@@ -0,0 +1,6 @@
+/* syntax version 1 */
+pragma warning("disable", "4526");
+use plato;
+
+select distinct key from Input2 having key != '0';
+
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_join.sql b/yql/essentials/tests/sql/suites/distinct/distinct_join.sql
new file mode 100644
index 0000000000..0e2aba1c5c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_join.sql
@@ -0,0 +1,8 @@
+/* syntax version 1 */
+
+use plato;
+
+select distinct * from (select Unwrap(cast(key as Int32)) as key, value from Input2) as a 
+join                   (select Just(1ul) as key, 123 as subkey) as b
+using(key) order by value;
+
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_list_after_group.sql b/yql/essentials/tests/sql/suites/distinct/distinct_list_after_group.sql
new file mode 100644
index 0000000000..33a780ea0d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_list_after_group.sql
@@ -0,0 +1,3 @@
+/* syntax version 1 */
+/* postgres can not */
+select listsort(aggregate_list(distinct key)) as key_list, value as name from plato.Input3 group by value order by name;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_one_count.sql b/yql/essentials/tests/sql/suites/distinct/distinct_one_count.sql
new file mode 100644
index 0000000000..a5778dfe30
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_one_count.sql
@@ -0,0 +1 @@
+select value, count(distinct key) as count from plato.Input2 group by value order by value;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_star.sql b/yql/essentials/tests/sql/suites/distinct/distinct_star.sql
new file mode 100644
index 0000000000..ac96695c1f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_star.sql
@@ -0,0 +1,9 @@
+/* syntax version 1 */
+
+use plato;
+
+select distinct * from Input2 order by key, subkey;
+
+select distinct * without subkey from Input2 order by key, value;
+
+select distinct a.*, TableName() as tn, without subkey from Input2 as a order by key, value;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_star1.cfg b/yql/essentials/tests/sql/suites/distinct/distinct_star1.cfg
new file mode 100644
index 0000000000..d1dbe1ff8c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_star1.cfg
@@ -0,0 +1 @@
+in Input input_dups.txt
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_star1.sql b/yql/essentials/tests/sql/suites/distinct/distinct_star1.sql
new file mode 100644
index 0000000000..775d2ac81d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_star1.sql
@@ -0,0 +1,3 @@
+/* syntax version 1 */
+
+select distinct * from plato.Input order by key, subkey;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_star_inmem.sql b/yql/essentials/tests/sql/suites/distinct/distinct_star_inmem.sql
new file mode 100644
index 0000000000..520e6e13bf
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_star_inmem.sql
@@ -0,0 +1,6 @@
+/* syntax version 1 */
+/* postgres can not */
+
+$src = select 1, 2, 3 union all select 1, 2, 3;
+
+select distinct * from $src;
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_union_all.sql b/yql/essentials/tests/sql/suites/distinct/distinct_union_all.sql
new file mode 100644
index 0000000000..7ddf2e4906
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_union_all.sql
@@ -0,0 +1,9 @@
+/* syntax version 1 */
+
+use plato;
+pragma AnsiOrderByLimitInUnionAll;
+
+select distinct key, value from Input2
+union all
+select key, value from Input2 order by key, value;
+
diff --git a/yql/essentials/tests/sql/suites/distinct/distinct_window.sql b/yql/essentials/tests/sql/suites/distinct/distinct_window.sql
new file mode 100644
index 0000000000..ab37cd91a6
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/distinct_window.sql
@@ -0,0 +1,8 @@
+/* syntax version 1 */
+
+use plato;
+
+select distinct AGGREGATE_LIST(value) over w as values, key from Input2
+window w as (partition by key order by value rows between unbounded preceding and unbounded following)
+order by key;
+
diff --git a/yql/essentials/tests/sql/suites/distinct/input.txt b/yql/essentials/tests/sql/suites/distinct/input.txt
new file mode 100644
index 0000000000..65949ea745
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/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/distinct/input2.txt b/yql/essentials/tests/sql/suites/distinct/input2.txt
new file mode 100644
index 0000000000..5c939cf453
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/input2.txt
@@ -0,0 +1,4 @@
+{"key"="1";"subkey"="10";"value"="FOO"};
+{"key"="1";"subkey"="20";"value"="BAR"};
+{"key"="1";"subkey"="30";"value"="BAR"};
+{"key"="0";"subkey"="40";"value"="FOO"};
diff --git a/yql/essentials/tests/sql/suites/distinct/input3.txt b/yql/essentials/tests/sql/suites/distinct/input3.txt
new file mode 100644
index 0000000000..48d828f6fd
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/input3.txt
@@ -0,0 +1,7 @@
+{"key"="1";"subkey"="10";"value"="FOO"};
+{"key"="1";"subkey"="20";"value"="BAR"};
+{"key"="1";"subkey"="30";"value"="BAR"};
+{"key"="0";"subkey"="40";"value"="FOO"};
+{"key"="0";"subkey"="40";"value"="FOO"};
+{"key"="A";"subkey"="50";"value"="WAT"};
+{"key"="A";"subkey"="50";"value"="WAT"};
diff --git a/yql/essentials/tests/sql/suites/distinct/input4.txt b/yql/essentials/tests/sql/suites/distinct/input4.txt
new file mode 100644
index 0000000000..65f33616b2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/input4.txt
@@ -0,0 +1,9 @@
+{"key"="0";"subkey"="10";"value"="FOO"};
+{"key"="0";"subkey"="40";"value"="FOO"};
+{"key"="1";"subkey"="10";"value"="FOO"};
+{"key"="1";"subkey"="20";"value"="BAR"};
+{"key"="1";"subkey"="40";"value"="BAR"};
+{"key"="1";"subkey"="50";"value"="WAT"};
+{"key"="2";"subkey"="40";"value"="WAT"};
+{"key"="2";"subkey"="50";"value"="FOO"};
+{"key"="2";"subkey"="60";"value"="BAR"};
diff --git a/yql/essentials/tests/sql/suites/distinct/input_dups.txt b/yql/essentials/tests/sql/suites/distinct/input_dups.txt
new file mode 100644
index 0000000000..4e5e036bb1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/input_dups.txt
@@ -0,0 +1,5 @@
+{"key"="0";"subkey"="10";"value"="FOO"};
+{"key"="0";"subkey"="10";"value"="FOO"};
+{"key"="1";"subkey"="10";"value"="FOO"};
+{"key"="1";"subkey"="20";"value"="BAR"};
+{"key"="1";"subkey"="20";"value"="BAR"};
diff --git a/yql/essentials/tests/sql/suites/distinct/input_intersect.txt b/yql/essentials/tests/sql/suites/distinct/input_intersect.txt
new file mode 100644
index 0000000000..42bcf2179e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/distinct/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"};