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

commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00

36 files changed, 274 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_const_interval.cfg b/yql/essentials/tests/sql/suites/agg_apply/avg_const_interval.cfg
new file mode 100644
index 0000000000..04d34fa0d8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_const_interval.cfg
@@ -0,0 +1,2 @@
+in Input input.txt
+
diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_const_interval.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_const_interval.sql
new file mode 100644
index 0000000000..ee9bd7870b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_const_interval.sql
@@ -0,0 +1,6 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+$a = select CurrentUtcDate() as _date, Just(Interval("P1W")) as parsed_lag from plato.Input;
+SELECT AVG(parsed_lag) FROM  $a;
diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_decimal.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_decimal.sql
new file mode 100644
index 0000000000..96f49ebf5d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_decimal.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    avg(key)
+FROM (values (Decimal("0.1",10,1)),(Decimal("0.2",10,1)),(Decimal("0.3",10,1))) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_interval.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_interval.sql
new file mode 100644
index 0000000000..d87106a991
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_interval.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    avg(key)
+FROM (values (Interval("P1D")),(Interval("P2D")),(Interval("P3D"))) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_numeric.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric.sql
new file mode 100644
index 0000000000..8e5ae7410c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    avg(key)
+FROM (values (1),(2),(3)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_numeric_opt.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric_opt.sql
new file mode 100644
index 0000000000..7f1249d119
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_numeric_opt.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    avg(key)
+FROM (values (1),(null),(3)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/avg_state_type.sql b/yql/essentials/tests/sql/suites/agg_apply/avg_state_type.sql
new file mode 100644
index 0000000000..67e7997756
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/avg_state_type.sql
@@ -0,0 +1,13 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+select 
+formattype(typeof(avg(null))),
+formattype(typeof(avg(1))),
+formattype(typeof(avg(decimal("10",10,1)))),
+formattype(typeof(avg(interval("P10D")))),
+formattype(typeof(avg(just(1)))),
+formattype(typeof(avg(just(decimal("10",10,1))))),
+formattype(typeof(avg(just(interval("P10D")))))
+from (select 1) group by () with combine
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/count.sql b/yql/essentials/tests/sql/suites/agg_apply/count.sql
new file mode 100644
index 0000000000..4d2917715e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/count.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    count(*),count(key)
+FROM (values (1),(null),(3)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/default.cfg b/yql/essentials/tests/sql/suites/agg_apply/default.cfg
new file mode 100644
index 0000000000..e69de29bb2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/default.cfg
diff --git a/yql/essentials/tests/sql/suites/agg_apply/input.txt b/yql/essentials/tests/sql/suites/agg_apply/input.txt
new file mode 100644
index 0000000000..70dd8b1eff
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/input.txt
@@ -0,0 +1,4 @@
+{"key"=1;"subkey"=10;"value"=5};
+{"key"=1;"subkey"=20;"value"=6};
+{"key"=1;"subkey"=30;"value"=7};
+{"key"=0;"subkey"=40;"value"=8};
diff --git a/yql/essentials/tests/sql/suites/agg_apply/input.txt.attr b/yql/essentials/tests/sql/suites/agg_apply/input.txt.attr
new file mode 100644
index 0000000000..84791918db
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/input.txt.attr
@@ -0,0 +1,30 @@
+{
+    "_yql_row_spec" = {
+        "Type" = [
+            "StructType";
+            [
+                [
+                    "key";
+                    [
+                        "DataType";
+                        "Int32"
+                    ]
+                ];
+                [
+                    "subkey";
+                    [
+                        "DataType";
+                        "Int32"
+                    ]
+                ];
+                [
+                    "value";
+                    [
+                        "DataType";
+                        "Int32"
+                    ]
+                ]
+            ]
+        ]
+    };
+}
diff --git a/yql/essentials/tests/sql/suites/agg_apply/max.sql b/yql/essentials/tests/sql/suites/agg_apply/max.sql
new file mode 100644
index 0000000000..70895a3780
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/max.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    max(key)
+FROM (values (1),(2),(3)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/min.sql b/yql/essentials/tests/sql/suites/agg_apply/min.sql
new file mode 100644
index 0000000000..60b25ff5c5
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/min.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    min(key)
+FROM (values (1),(2),(3)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_column_subset.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_column_subset.sql
new file mode 100644
index 0000000000..ff904cf8cb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_column_subset.sql
@@ -0,0 +1,6 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+$x = select * from as_table([<|x:1,y:2,z:3|>, <|x:4,y:5,z:6|>]);
+select count(x + y) from $x;
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_len_count.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count.sql
new file mode 100644
index 0000000000..5b7a03d163
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    count(1)
+FROM (values (1),(null),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_all.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_all.sql
new file mode 100644
index 0000000000..1f4310546b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_all.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    count(*)
+FROM (values (1),(null),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_distinct.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_distinct.sql
new file mode 100644
index 0000000000..4768f51d23
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_distinct.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    count(distinct key)
+FROM (values (1),(null),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_null.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_null.sql
new file mode 100644
index 0000000000..5821db7652
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_len_count_null.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    count(1/0)
+FROM (values (1),(null),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_sum_divide_by_zero.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_divide_by_zero.sql
new file mode 100644
index 0000000000..15236cd2ff
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_divide_by_zero.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(1/0)
+FROM (values (1),(null),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/opt_sum_null.sql b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_null.sql
new file mode 100644
index 0000000000..c2086f7f5b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/opt_sum_null.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(null)
+FROM (values (1),(null),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/pg_int4.sql b/yql/essentials/tests/sql/suites/agg_apply/pg_int4.sql
new file mode 100644
index 0000000000..7f2e790363
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/pg_int4.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    Pg::count(),Pg::count(key),Pg::min(key),Pg::max(key),Pg::sum(key),Pg::avg(key)
+FROM (values (1),(2),(3)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/pg_int8.sql b/yql/essentials/tests/sql/suites/agg_apply/pg_int8.sql
new file mode 100644
index 0000000000..523f23fc7e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/pg_int8.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    Pg::count(),Pg::count(key),Pg::min(key),Pg::max(key),Pg::sum(key),Pg::avg(key)
+FROM (values (1l),(2l),(3l)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/pg_text.sql b/yql/essentials/tests/sql/suites/agg_apply/pg_text.sql
new file mode 100644
index 0000000000..abbb4d2016
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/pg_text.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    Pg::count(),Pg::count(key),Pg::min(key),Pg::max(key),Pg::string_agg(key,"|"u)
+FROM (values ("a"u),("b"u),("c"u)) as a(key)
diff --git a/yql/essentials/tests/sql/suites/agg_apply/some_notnull.sql b/yql/essentials/tests/sql/suites/agg_apply/some_notnull.sql
new file mode 100644
index 0000000000..7e3cd4fb3c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/some_notnull.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    some(key)
+FROM (values (1)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/some_null.sql b/yql/essentials/tests/sql/suites/agg_apply/some_null.sql
new file mode 100644
index 0000000000..2bf12e30d9
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/some_null.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    some(key)
+FROM (values (null)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_decimal.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal.sql
new file mode 100644
index 0000000000..5ba05328dc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(key)
+FROM (values (CAST("1.51" AS Decimal(10, 3))), (CAST("2.22" AS Decimal(10, 3))), (CAST("3.49" AS Decimal(10, 3)))) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_decimal_null.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal_null.sql
new file mode 100644
index 0000000000..75df4277f2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_decimal_null.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(key)
+FROM (values (CAST("1.51" AS Decimal(10, 3))), (null), (CAST("3.49" AS Decimal(10, 3)))) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_double.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_double.sql
new file mode 100644
index 0000000000..698e215e5c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_double.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(key)
+FROM (values (1.51),(null),(3.49)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_interval.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_interval.sql
new file mode 100644
index 0000000000..b08de4bb54
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_interval.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(key)
+FROM (values (cast(1 as Interval)),(null),(cast(3 as Interval))) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_signed.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_signed.sql
new file mode 100644
index 0000000000..ab731a9042
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_signed.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(key)
+FROM (values (1),(null),(3)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_type.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_type.sql
new file mode 100644
index 0000000000..552b64390c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_type.sql
@@ -0,0 +1,12 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+$p =
+SELECT
+    sum(value) as a
+FROM AS_TABLE([<|key: 1, value: 2|>])
+;
+
+$p = PROCESS $p;
+select FormatType(TypeOf($p));
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_type_empty.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_type_empty.sql
new file mode 100644
index 0000000000..292ddcc098
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_type_empty.sql
@@ -0,0 +1,13 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+$p =
+SELECT
+    sum(value) as a
+FROM AS_TABLE([<|key: 1, value: 2|>])
+LIMIT 0
+;
+
+$p = PROCESS $p;
+select FormatType(TypeOf($p));
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_type_group_by.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_type_group_by.sql
new file mode 100644
index 0000000000..112ca28e29
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_type_group_by.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+$p =
+SELECT
+    key, sum(value) as a
+FROM AS_TABLE([<|key: 1, value: 2|>])
+GROUP BY
+    key
+;
+
+$p = PROCESS $p;
+select FormatType(TypeOf($p));
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/sum_unsigned.sql b/yql/essentials/tests/sql/suites/agg_apply/sum_unsigned.sql
new file mode 100644
index 0000000000..1fb4103665
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/sum_unsigned.sql
@@ -0,0 +1,7 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+SELECT
+    sum(key)
+FROM (values (1u),(null),(3u)) as a(key)
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/table.cfg b/yql/essentials/tests/sql/suites/agg_apply/table.cfg
new file mode 100644
index 0000000000..e68191f82c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/table.cfg
@@ -0,0 +1 @@
+in Input input.txt
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/agg_apply/table.sql b/yql/essentials/tests/sql/suites/agg_apply/table.sql
new file mode 100644
index 0000000000..e81956b605
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/agg_apply/table.sql
@@ -0,0 +1,5 @@
+/* syntax version 1 */
+/* postgres can not */
+pragma EmitAggApply;
+
+select count(if(key=1,cast(key as string))) from plato.Input;