diff options
| author | Maxim Yurchuk <maxim-yurchuk@ydb.tech> | 2024-11-20 17:37:57 +0000 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-11-20 17:37:57 +0000 |
| commit | f76323e9b295c15751e51e3443aa47a36bee8023 (patch) | |
| tree | 4113c8cad473a33e0f746966e0cf087252fa1d7a /yql/essentials/tests/sql/suites/optimizers | |
| parent | 753ecb8d410a4cb459c26f3a0082fb2d1724fe63 (diff) | |
| parent | a7b9a6afea2a9d7a7bfac4c5eb4c1a8e60adb9e6 (diff) | |
| download | ydb-f76323e9b295c15751e51e3443aa47a36bee8023.tar.gz | |
Merge pull request #11788 from ydb-platform/mergelibs-241120-1113
Library import 241120-1113
Diffstat (limited to 'yql/essentials/tests/sql/suites/optimizers')
148 files changed, 1715 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/optimizers/aggregate_over_aggregate.cfg b/yql/essentials/tests/sql/suites/optimizers/aggregate_over_aggregate.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/aggregate_over_aggregate.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/aggregate_over_aggregate.sql b/yql/essentials/tests/sql/suites/optimizers/aggregate_over_aggregate.sql new file mode 100644 index 0000000000..39306eb32a --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/aggregate_over_aggregate.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ + +use plato; + +select distinct * from Input group by value, subkey, key order by subkey; diff --git a/yql/essentials/tests/sql/suites/optimizers/and_absorption.cfg b/yql/essentials/tests/sql/suites/optimizers/and_absorption.cfg new file mode 100644 index 0000000000..a1b36ede45 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/and_absorption.cfg @@ -0,0 +1,2 @@ +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/and_absorption.sql b/yql/essentials/tests/sql/suites/optimizers/and_absorption.sql new file mode 100644 index 0000000000..d939b2f6e8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/and_absorption.sql @@ -0,0 +1,9 @@ +pragma config.flags("OptimizerFlags", "ExtractCommonPredicatesFromLogicalOps"); + +$a = 1 > 2; +$b = 3 < 4; +$c = 5 < 6; +$d = 7 > 8; + +select (($a or $b) and $a) == $a; +select (($b or $a) and $c and $b and ($d or $c)) == ($c and $b); diff --git a/yql/essentials/tests/sql/suites/optimizers/coalesce_propagate.sql b/yql/essentials/tests/sql/suites/optimizers/coalesce_propagate.sql new file mode 100644 index 0000000000..843106d800 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/coalesce_propagate.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ + +$src = [ + <|x:1/0, y:2/0|>, + <|x:1/0, y:1|>, + <|x:1, y:1/0|>, + <|x:2, y:2|>, + <|x:3, y:3|>, + <|x:4, y:4|>, + <|x:5, y:5|>, +]; + +select * from as_table($src) where not (x < 3 or y > 3); diff --git a/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset.cfg b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset.cfg new file mode 100644 index 0000000000..3f5bebb5a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset.sql b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset.sql new file mode 100644 index 0000000000..6ed4e0ed36 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset.sql @@ -0,0 +1,3 @@ +SELECT count(DISTINCT subkey) AS subkey +FROM plato.Input +WHERE (key == "075" OR key == "150"); diff --git a/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset_range.cfg b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset_range.cfg new file mode 100644 index 0000000000..f2bc0f7ec7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset_range.cfg @@ -0,0 +1,3 @@ +in Input0 sorted.txt +in Input1 sorted.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset_range.sql b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset_range.sql new file mode 100644 index 0000000000..3acd90202b --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/combinebykey_fields_subset_range.sql @@ -0,0 +1,5 @@ +/* postgres can not */ + +SELECT count(DISTINCT subkey) AS subkey +FROM plato.CONCAT(Input0, Input1) +WHERE (key == "075" OR key == "150"); diff --git a/yql/essentials/tests/sql/suites/optimizers/constant_fold_minmax.sql b/yql/essentials/tests/sql/suites/optimizers/constant_fold_minmax.sql new file mode 100644 index 0000000000..841d5ae740 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/constant_fold_minmax.sql @@ -0,0 +1,19 @@ +/* postgres can not */ + +SELECT + MIN_OF(50ut, -255ut, 5ut, 15ut) AS MinByte, + MAX_OF(50ut, -255ut, 5ut, 15ut) AS MaxByte, + MIN_OF(300000, -600000, 4) AS MinInt32, + MAX_OF(300000, -600000, 4) AS MaxInt32, + MIN_OF(300000u, 600u, -4000000000u) AS MinUInt32, + MAX_OF(300000u, 600u, -4000000000u) AS MaxUInt32, + MIN_OF(80l, 5000000000l, 90l, -6000000000l) AS MinInt64, + MAX_OF(80l, 5000000000l, 90l, -6000000000l) AS MaxInt64, + MIN_OF(80ul, -5000000000ul, 90ul, 6000000000ul) AS MinUInt64, + MAX_OF(80ul, -5000000000ul, 90ul, 6000000000ul) AS MaxUInt64, + MIN_OF(50ut, -10, 56l, 17u, 78ul) AS MinMixed1, + MAX_OF(50ut, -10, 56l, 17u, 78ul) AS MaxMixed1, + MIN_OF(50ut, 30, 40) AS MinMixed2, + MAX_OF(50ut, 30, 40) AS MaxMixed2, + MIN_OF(1) AS MinSingle, + MAX_OF(-1) AS MaxSingle; diff --git a/yql/essentials/tests/sql/suites/optimizers/default.cfg b/yql/essentials/tests/sql/suites/optimizers/default.cfg new file mode 100644 index 0000000000..9b1614d4c4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/default.cfg @@ -0,0 +1,3 @@ +in Input0 input0.txt +in Input1 input1.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/direct_row_after_merge.cfg b/yql/essentials/tests/sql/suites/optimizers/direct_row_after_merge.cfg new file mode 100644 index 0000000000..3712d0f1ba --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/direct_row_after_merge.cfg @@ -0,0 +1,3 @@ +in Input1 sorted.txt +in Input2 sorted.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/direct_row_after_merge.sql b/yql/essentials/tests/sql/suites/optimizers/direct_row_after_merge.sql new file mode 100644 index 0000000000..cde00141a3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/direct_row_after_merge.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +USE plato; + +SELECT + key, + TablePath() as path +FROM concat(Input1, Input2) +order by key, path;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/field_subset_for_multiusage.cfg b/yql/essentials/tests/sql/suites/optimizers/field_subset_for_multiusage.cfg new file mode 100644 index 0000000000..670afdcdfa --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/field_subset_for_multiusage.cfg @@ -0,0 +1,3 @@ +in Input1 input3.txt +in Input2 input_other.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/field_subset_for_multiusage.sql b/yql/essentials/tests/sql/suites/optimizers/field_subset_for_multiusage.sql new file mode 100644 index 0000000000..3a568de9ae --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/field_subset_for_multiusage.sql @@ -0,0 +1,15 @@ +use plato; + +select + key, + count(1) as cnt, + sum(cast(subkey as int32)) as sm +from concat(Input1, Input2) +where subkey in ("1", "2", "3", "4") +group by key +order by sm desc; + +select + count(1) as cnt, + sum(cast(subkey as int32)) as sm +from concat(Input1, Input2); diff --git a/yql/essentials/tests/sql/suites/optimizers/flatmap_with_non_struct_out.cfg b/yql/essentials/tests/sql/suites/optimizers/flatmap_with_non_struct_out.cfg new file mode 100644 index 0000000000..86ef4956fc --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/flatmap_with_non_struct_out.cfg @@ -0,0 +1,3 @@ +in Input input3.txt +res result.txt +providers yt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/flatmap_with_non_struct_out.sql b/yql/essentials/tests/sql/suites/optimizers/flatmap_with_non_struct_out.sql new file mode 100644 index 0000000000..4a99afbccf --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/flatmap_with_non_struct_out.sql @@ -0,0 +1,6 @@ +USE plato; + +$lst = process Input; +$dict = ToDict(ListMap($lst,($x)->(($x.key, $x.subkey)))); + +SELECT DictLength($dict);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/group_visit_lambdas.cfg b/yql/essentials/tests/sql/suites/optimizers/group_visit_lambdas.cfg new file mode 100644 index 0000000000..de847dcf33 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/group_visit_lambdas.cfg @@ -0,0 +1,6 @@ +in Input1 input3.txt +in Input2 input3.txt +in Input3 input3.txt +in Input4 opt_key.txt +in Input5 opt_key.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/group_visit_lambdas.sql b/yql/essentials/tests/sql/suites/optimizers/group_visit_lambdas.sql new file mode 100644 index 0000000000..f5e2aab6e5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/group_visit_lambdas.sql @@ -0,0 +1,13 @@ +/* postgres can not */ + +USE plato; + +select + key, + subkey, + value, + TablePath() as path +from + range("", "Input1", "Input5") +where key != "" +order by key, subkey, path;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/input0.txt b/yql/essentials/tests/sql/suites/optimizers/input0.txt new file mode 100644 index 0000000000..d3442809d7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input0.txt @@ -0,0 +1,200 @@ +{"key"="0";"subkey"="7";"value"="Value #18"}; +{"key"="1";"subkey"="12";"value"="Value #48"}; +{"key"="10";"subkey"="8";"value"="Value #33"}; +{"key"="100";"subkey"="9";"value"="Value #21"}; +{"key"="101";"subkey"="16";"value"="Value #32"}; +{"key"="102";"subkey"="12";"value"="Value #13"}; +{"key"="103";"subkey"="18";"value"="Value #11"}; +{"key"="104";"subkey"="3";"value"="Value #37"}; +{"key"="105";"subkey"="10";"value"="Value #52"}; +{"key"="106";"subkey"="4";"value"="Value #1"}; +{"key"="107";"subkey"="1";"value"="Value #47"}; +{"key"="108";"subkey"="16";"value"="Value #35"}; +{"key"="109";"subkey"="18";"value"="Value #12"}; +{"key"="11";"subkey"="13";"value"="Value #36"}; +{"key"="110";"subkey"="18";"value"="Value #39"}; +{"key"="111";"subkey"="14";"value"="Value #9"}; +{"key"="112";"subkey"="15";"value"="Value #14"}; +{"key"="113";"subkey"="2";"value"="Value #56"}; +{"key"="114";"subkey"="5";"value"="Value #49"}; +{"key"="115";"subkey"="1";"value"="Value #47"}; +{"key"="116";"subkey"="12";"value"="Value #13"}; +{"key"="117";"subkey"="3";"value"="Value #5"}; +{"key"="118";"subkey"="11";"value"="Value #27"}; +{"key"="119";"subkey"="14";"value"="Value #9"}; +{"key"="120";"subkey"="13";"value"="Value #25"}; +{"key"="121";"subkey"="11";"value"="Value #58"}; +{"key"="122";"subkey"="10";"value"="Value #59"}; +{"key"="123";"subkey"="15";"value"="Value #26"}; +{"key"="124";"subkey"="6";"value"="Value #16"}; +{"key"="125";"subkey"="10";"value"="Value #23"}; +{"key"="126";"subkey"="19";"value"="Value #17"}; +{"key"="127";"subkey"="5";"value"="Value #38"}; +{"key"="128";"subkey"="2";"value"="Value #56"}; +{"key"="129";"subkey"="2";"value"="Value #10"}; +{"key"="12";"subkey"="12";"value"="Value #13"}; +{"key"="130";"subkey"="4";"value"="Value #22"}; +{"key"="131";"subkey"="8";"value"="Value #43"}; +{"key"="132";"subkey"="18";"value"="Value #39"}; +{"key"="133";"subkey"="12";"value"="Value #48"}; +{"key"="134";"subkey"="0";"value"="Value #31"}; +{"key"="135";"subkey"="9";"value"="Value #21"}; +{"key"="136";"subkey"="8";"value"="Value #46"}; +{"key"="137";"subkey"="9";"value"="Value #20"}; +{"key"="138";"subkey"="2";"value"="Value #6"}; +{"key"="139";"subkey"="7";"value"="Value #44"}; +{"key"="13";"subkey"="3";"value"="Value #37"}; +{"key"="140";"subkey"="0";"value"="Value #30"}; +{"key"="141";"subkey"="16";"value"="Value #35"}; +{"key"="142";"subkey"="3";"value"="Value #5"}; +{"key"="143";"subkey"="14";"value"="Value #9"}; +{"key"="144";"subkey"="13";"value"="Value #28"}; +{"key"="145";"subkey"="15";"value"="Value #2"}; +{"key"="146";"subkey"="7";"value"="Value #18"}; +{"key"="147";"subkey"="19";"value"="Value #41"}; +{"key"="148";"subkey"="19";"value"="Value #0"}; +{"key"="149";"subkey"="7";"value"="Value #15"}; +{"key"="14";"subkey"="19";"value"="Value #0"}; +{"key"="150";"subkey"="11";"value"="Value #27"}; +{"key"="151";"subkey"="1";"value"="Value #55"}; +{"key"="152";"subkey"="1";"value"="Value #47"}; +{"key"="153";"subkey"="17";"value"="Value #24"}; +{"key"="154";"subkey"="7";"value"="Value #44"}; +{"key"="155";"subkey"="8";"value"="Value #43"}; +{"key"="156";"subkey"="14";"value"="Value #45"}; +{"key"="157";"subkey"="0";"value"="Value #31"}; +{"key"="158";"subkey"="3";"value"="Value #50"}; +{"key"="159";"subkey"="10";"value"="Value #52"}; +{"key"="15";"subkey"="17";"value"="Value #7"}; +{"key"="160";"subkey"="18";"value"="Value #39"}; +{"key"="161";"subkey"="9";"value"="Value #29"}; +{"key"="162";"subkey"="14";"value"="Value #45"}; +{"key"="163";"subkey"="3";"value"="Value #5"}; +{"key"="164";"subkey"="10";"value"="Value #52"}; +{"key"="165";"subkey"="13";"value"="Value #36"}; +{"key"="166";"subkey"="14";"value"="Value #4"}; +{"key"="167";"subkey"="16";"value"="Value #53"}; +{"key"="168";"subkey"="9";"value"="Value #29"}; +{"key"="169";"subkey"="6";"value"="Value #40"}; +{"key"="16";"subkey"="8";"value"="Value #46"}; +{"key"="170";"subkey"="15";"value"="Value #26"}; +{"key"="171";"subkey"="6";"value"="Value #40"}; +{"key"="172";"subkey"="16";"value"="Value #53"}; +{"key"="173";"subkey"="2";"value"="Value #10"}; +{"key"="174";"subkey"="5";"value"="Value #54"}; +{"key"="175";"subkey"="17";"value"="Value #7"}; +{"key"="176";"subkey"="10";"value"="Value #23"}; +{"key"="177";"subkey"="4";"value"="Value #51"}; +{"key"="178";"subkey"="17";"value"="Value #7"}; +{"key"="179";"subkey"="11";"value"="Value #19"}; +{"key"="17";"subkey"="11";"value"="Value #58"}; +{"key"="180";"subkey"="7";"value"="Value #44"}; +{"key"="181";"subkey"="2";"value"="Value #56"}; +{"key"="182";"subkey"="11";"value"="Value #58"}; +{"key"="183";"subkey"="12";"value"="Value #57"}; +{"key"="184";"subkey"="6";"value"="Value #16"}; +{"key"="185";"subkey"="11";"value"="Value #27"}; +{"key"="186";"subkey"="19";"value"="Value #17"}; +{"key"="187";"subkey"="12";"value"="Value #48"}; +{"key"="188";"subkey"="6";"value"="Value #8"}; +{"key"="189";"subkey"="1";"value"="Value #34"}; +{"key"="18";"subkey"="6";"value"="Value #40"}; +{"key"="190";"subkey"="15";"value"="Value #26"}; +{"key"="191";"subkey"="16";"value"="Value #53"}; +{"key"="192";"subkey"="2";"value"="Value #6"}; +{"key"="193";"subkey"="18";"value"="Value #11"}; +{"key"="194";"subkey"="9";"value"="Value #20"}; +{"key"="195";"subkey"="12";"value"="Value #57"}; +{"key"="196";"subkey"="3";"value"="Value #5"}; +{"key"="197";"subkey"="8";"value"="Value #33"}; +{"key"="198";"subkey"="4";"value"="Value #22"}; +{"key"="199";"subkey"="13";"value"="Value #25"}; +{"key"="19";"subkey"="11";"value"="Value #19"}; +{"key"="20";"subkey"="9";"value"="Value #29"}; +{"key"="21";"subkey"="1";"value"="Value #55"}; +{"key"="22";"subkey"="18";"value"="Value #12"}; +{"key"="23";"subkey"="7";"value"="Value #15"}; +{"key"="24";"subkey"="15";"value"="Value #14"}; +{"key"="25";"subkey"="8";"value"="Value #43"}; +{"key"="26";"subkey"="5";"value"="Value #54"}; +{"key"="27";"subkey"="13";"value"="Value #28"}; +{"key"="28";"subkey"="5";"value"="Value #54"}; +{"key"="29";"subkey"="10";"value"="Value #23"}; +{"key"="2";"subkey"="6";"value"="Value #8"}; +{"key"="30";"subkey"="7";"value"="Value #44"}; +{"key"="31";"subkey"="1";"value"="Value #34"}; +{"key"="32";"subkey"="17";"value"="Value #3"}; +{"key"="33";"subkey"="9";"value"="Value #21"}; +{"key"="34";"subkey"="6";"value"="Value #40"}; +{"key"="35";"subkey"="4";"value"="Value #51"}; +{"key"="36";"subkey"="16";"value"="Value #32"}; +{"key"="37";"subkey"="0";"value"="Value #31"}; +{"key"="38";"subkey"="0";"value"="Value #42"}; +{"key"="39";"subkey"="0";"value"="Value #30"}; +{"key"="3";"subkey"="18";"value"="Value #11"}; +{"key"="40";"subkey"="17";"value"="Value #7"}; +{"key"="41";"subkey"="19";"value"="Value #41"}; +{"key"="42";"subkey"="10";"value"="Value #52"}; +{"key"="43";"subkey"="8";"value"="Value #33"}; +{"key"="44";"subkey"="13";"value"="Value #36"}; +{"key"="45";"subkey"="17";"value"="Value #3"}; +{"key"="46";"subkey"="14";"value"="Value #4"}; +{"key"="47";"subkey"="8";"value"="Value #46"}; +{"key"="48";"subkey"="12";"value"="Value #13"}; +{"key"="49";"subkey"="0";"value"="Value #30"}; +{"key"="4";"subkey"="15";"value"="Value #2"}; +{"key"="50";"subkey"="2";"value"="Value #6"}; +{"key"="51";"subkey"="7";"value"="Value #18"}; +{"key"="52";"subkey"="14";"value"="Value #45"}; +{"key"="53";"subkey"="19";"value"="Value #41"}; +{"key"="54";"subkey"="4";"value"="Value #51"}; +{"key"="55";"subkey"="7";"value"="Value #15"}; +{"key"="56";"subkey"="3";"value"="Value #50"}; +{"key"="57";"subkey"="14";"value"="Value #4"}; +{"key"="58";"subkey"="3";"value"="Value #37"}; +{"key"="59";"subkey"="19";"value"="Value #41"}; +{"key"="5";"subkey"="13";"value"="Value #28"}; +{"key"="60";"subkey"="18";"value"="Value #12"}; +{"key"="61";"subkey"="15";"value"="Value #14"}; +{"key"="62";"subkey"="4";"value"="Value #1"}; +{"key"="63";"subkey"="18";"value"="Value #12"}; +{"key"="64";"subkey"="5";"value"="Value #38"}; +{"key"="65";"subkey"="17";"value"="Value #24"}; +{"key"="66";"subkey"="17";"value"="Value #3"}; +{"key"="67";"subkey"="5";"value"="Value #38"}; +{"key"="68";"subkey"="5";"value"="Value #49"}; +{"key"="69";"subkey"="5";"value"="Value #49"}; +{"key"="6";"subkey"="4";"value"="Value #1"}; +{"key"="70";"subkey"="16";"value"="Value #32"}; +{"key"="71";"subkey"="1";"value"="Value #55"}; +{"key"="72";"subkey"="1";"value"="Value #34"}; +{"key"="73";"subkey"="8";"value"="Value #33"}; +{"key"="74";"subkey"="10";"value"="Value #59"}; +{"key"="75";"subkey"="6";"value"="Value #8"}; +{"key"="76";"subkey"="0";"value"="Value #30"}; +{"key"="77";"subkey"="19";"value"="Value #17"}; +{"key"="78";"subkey"="13";"value"="Value #28"}; +{"key"="79";"subkey"="3";"value"="Value #50"}; +{"key"="7";"subkey"="17";"value"="Value #24"}; +{"key"="80";"subkey"="5";"value"="Value #49"}; +{"key"="81";"subkey"="14";"value"="Value #45"}; +{"key"="82";"subkey"="19";"value"="Value #0"}; +{"key"="83";"subkey"="15";"value"="Value #2"}; +{"key"="84";"subkey"="2";"value"="Value #56"}; +{"key"="85";"subkey"="2";"value"="Value #10"}; +{"key"="86";"subkey"="6";"value"="Value #16"}; +{"key"="87";"subkey"="15";"value"="Value #14"}; +{"key"="88";"subkey"="16";"value"="Value #35"}; +{"key"="89";"subkey"="4";"value"="Value #1"}; +{"key"="8";"subkey"="16";"value"="Value #35"}; +{"key"="90";"subkey"="4";"value"="Value #22"}; +{"key"="91";"subkey"="13";"value"="Value #25"}; +{"key"="92";"subkey"="0";"value"="Value #42"}; +{"key"="93";"subkey"="9";"value"="Value #20"}; +{"key"="94";"subkey"="10";"value"="Value #59"}; +{"key"="95";"subkey"="1";"value"="Value #34"}; +{"key"="96";"subkey"="12";"value"="Value #57"}; +{"key"="97";"subkey"="11";"value"="Value #19"}; +{"key"="98";"subkey"="9";"value"="Value #20"}; +{"key"="99";"subkey"="11";"value"="Value #58"}; +{"key"="9";"subkey"="0";"value"="Value #42"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/input1.txt b/yql/essentials/tests/sql/suites/optimizers/input1.txt new file mode 100644 index 0000000000..bd567557a0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input1.txt @@ -0,0 +1,19 @@ +{"Amount"=100;"Comment"="Test1";"Group"=1u;"Name"="Name1"}; +{"Amount"=101;"Comment"="Test1";"Group"=1u;"Name"="Name2"}; +{"Amount"=102;"Comment"="Test1";"Group"=1u;"Name"="Name3"}; +{"Amount"=103;"Comment"="Test1";"Group"=2u;"Name"="Name1"}; +{"Amount"=104;"Comment"="Test1";"Group"=3u;"Name"="Name3"}; +{"Amount"=105;"Comment"="Test1";"Group"=4u;"Name"="Name1"}; +{"Amount"=106;"Comment"="Test1";"Group"=4u;"Name"="Name4"}; +{"Amount"=107;"Comment"="Test1";"Group"=5u;"Name"="Name5"}; +{"Amount"=108;"Comment"="Test1";"Group"=6u;"Name"="Name1"}; +{"Amount"=109;"Comment"="Test1";"Group"=6u;"Name"="Name2"}; +{"Amount"=100;"Comment"="Test3";"Group"=10u;"Name"="Name1"}; +{"Amount"=101;"Comment"="Test3";"Group"=10u;"Name"="Name2"}; +{"Amount"=102;"Comment"="Test3";"Group"=10u;"Name"="Name3"}; +{"Amount"=103;"Comment"="Test3";"Group"=10u;"Name"="Name4"}; +{"Amount"=104;"Comment"="Test3";"Group"=10u;"Name"="Name5"}; +{"Amount"=105;"Comment"="Test3";"Group"=10u;"Name"="Name6"}; +{"Amount"=100;"Comment"="Test2";"Group"=100001u;"Name"="Name1"}; +{"Amount"=102;"Comment"="Test2";"Group"=100002u;"Name"="Name2"}; +{"Amount"=103;"Comment"="Test2";"Group"=100003u;"Name"="Name1"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/input1.txt.attr b/yql/essentials/tests/sql/suites/optimizers/input1.txt.attr new file mode 100644 index 0000000000..ffb9a19cab --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input1.txt.attr @@ -0,0 +1,42 @@ +{ + "_yql_row_spec" = { + "UniqueKeys"=%true; + "SortMembers"=["Group"; "Name"]; + "SortedBy"=["Group"; "Name"]; + "SortDirections"=[1; 1]; + "SortedByTypes"=[["DataType";"Uint64"]; ["DataType";"String"]]; + "Type" = [ + "StructType"; + [ + [ + "Group"; + [ + "DataType"; + "Uint64" + ] + ]; + [ + "Name"; + [ + "DataType"; + "String" + ] + ]; + [ + "Amount"; + [ + "DataType"; + "Int64" + ] + ]; + [ + "Comment"; + [ + "DataType"; + "String" + ] + ] + ] + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/input2.txt b/yql/essentials/tests/sql/suites/optimizers/input2.txt new file mode 100644 index 0000000000..375a3143fe --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input2.txt @@ -0,0 +1,4 @@ +{"key"="023,023";"subkey"="3,1";"value"="aaa"}; +{"key"="037,037";"subkey"="5,4";"value"="ddd"}; +{"key"="075,075,150";"subkey"="1,5";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/input3.txt b/yql/essentials/tests/sql/suites/optimizers/input3.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input3.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/optimizers/input3.txt.attr b/yql/essentials/tests/sql/suites/optimizers/input3.txt.attr new file mode 100644 index 0000000000..b6100e5fd0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input3.txt.attr @@ -0,0 +1,30 @@ +{ + "_yql_row_spec" = { + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/input4.txt b/yql/essentials/tests/sql/suites/optimizers/input4.txt new file mode 100644 index 0000000000..2c605925c1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input4.txt @@ -0,0 +1,4 @@ +{"key"="023";"info"="3"}; +{"key"="037";"info"="5"}; +{"key"="075";"info"="1"}; +{"key"="150";"info"="1"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/input4.txt.attr b/yql/essentials/tests/sql/suites/optimizers/input4.txt.attr new file mode 100644 index 0000000000..9678beab02 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input4.txt.attr @@ -0,0 +1,10 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["info";["DataType";"String"]]; + ]]; + "SortDirections"=[1;]; + "SortedBy"=["key"]; + "SortedByTypes"=[["DataType";"String";]]; + "SortMembers"=["key"]; +}} diff --git a/yql/essentials/tests/sql/suites/optimizers/input5.txt b/yql/essentials/tests/sql/suites/optimizers/input5.txt new file mode 100644 index 0000000000..b214aab0d9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input5.txt @@ -0,0 +1,10 @@ +{"key"="023";"subkey"="3";"value"="aaa"}; +{"key"="037";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; +{"key"="150";"subkey"="3";"value"="iii"}; +{"key"="150";"subkey"="8";"value"="zzz"}; +{"key"="200";"subkey"="7";"value"="qqq"}; +{"key"="527";"subkey"="4";"value"="bbb"}; +{"key"="761";"subkey"="6";"value"="ccc"}; +{"key"="911";"subkey"="2";"value"="kkk"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/input5.txt.attr b/yql/essentials/tests/sql/suites/optimizers/input5.txt.attr new file mode 100644 index 0000000000..ed13e20223 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input5.txt.attr @@ -0,0 +1,11 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"String"]] + ]]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} diff --git a/yql/essentials/tests/sql/suites/optimizers/input_other.txt b/yql/essentials/tests/sql/suites/optimizers/input_other.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input_other.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/optimizers/input_other.txt.attr b/yql/essentials/tests/sql/suites/optimizers/input_other.txt.attr new file mode 100644 index 0000000000..9d84682ce5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input_other.txt.attr @@ -0,0 +1,8 @@ +{ + "_read_schema" = + <"strict" = "false";> + [ + {"name"="key"; "type"="string"}; + {"name"="subkey"; "type"="string"}; + ] +} diff --git a/yql/essentials/tests/sql/suites/optimizers/input_tutorial_users.txt b/yql/essentials/tests/sql/suites/optimizers/input_tutorial_users.txt new file mode 100644 index 0000000000..4a18a0dd29 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input_tutorial_users.txt @@ -0,0 +1,12 @@ +{"key"="15";"subkey"="213";"value"="Anya"}; +{"key"="25";"subkey"="225";"value"="Petr"}; +{"key"="17";"subkey"="1";"value"="Masha"}; +{"key"="5";"subkey"="225";"value"="Alena"}; +{"key"="23";"subkey"="2";"value"="Irina"}; +{"key"="13";"subkey"="21";"value"="Inna"}; +{"key"="33";"subkey"="125";"value"="Ivan"}; +{"key"="45";"subkey"="225";"value"="Asya"}; +{"key"="27";"subkey"="125";"value"="German"}; +{"key"="41";"subkey"="225";"value"="Olya"}; +{"key"="35";"subkey"="2";"value"="Slava"}; +{"key"="56";"subkey"="2";"value"="Elena"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/input_view.txt b/yql/essentials/tests/sql/suites/optimizers/input_view.txt new file mode 100644 index 0000000000..962496f023 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input_view.txt @@ -0,0 +1,21 @@ +$p = @@ +def Parse(s): + return {'subkey': s, 'value': s} +@@; + +$pv = Python::Parse(Callable<(String)->Struct<subkey: String, value: String>>, $p); + +$i = ( + select + AsStruct(key as key), + $pv(info) as info + from ViewSource + where info != "" +); + +$j = ( + select * + from $i flatten columns +); + +select * from $j diff --git a/yql/essentials/tests/sql/suites/optimizers/input_view.txt.attr b/yql/essentials/tests/sql/suites/optimizers/input_view.txt.attr new file mode 100644 index 0000000000..41ea51e276 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/input_view.txt.attr @@ -0,0 +1,4 @@ +{ + "_yql_type"="view"; + "type"="document"; +} diff --git a/yql/essentials/tests/sql/suites/optimizers/instant_contains_lookup.sql b/yql/essentials/tests/sql/suites/optimizers/instant_contains_lookup.sql new file mode 100644 index 0000000000..0b74075205 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/instant_contains_lookup.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* syntax version 1 */ +SELECT ListHas([1,2,3], 2), DictContains({4:5,6:7,8:9}, 6), DictLookup({4:5,6:7,8:9}, 8); diff --git a/yql/essentials/tests/sql/suites/optimizers/length_over_merge.cfg b/yql/essentials/tests/sql/suites/optimizers/length_over_merge.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/length_over_merge.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/length_over_merge.sql b/yql/essentials/tests/sql/suites/optimizers/length_over_merge.sql new file mode 100644 index 0000000000..b102fdee47 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/length_over_merge.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +use plato; + +$input = (select key, key || subkey as subkey, value from Input); + +$total_count = (select count(1) from $input); + +$filtered = (select * from $input where key in ("023", "037", "075")); + +$filtered_cnt = (select count(1) from $filtered); + +select $filtered_cnt / cast($total_count as Double) as cnt; diff --git a/yql/essentials/tests/sql/suites/optimizers/length_over_merge_fs_multiusage.cfg b/yql/essentials/tests/sql/suites/optimizers/length_over_merge_fs_multiusage.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/length_over_merge_fs_multiusage.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/length_over_merge_fs_multiusage.sql b/yql/essentials/tests/sql/suites/optimizers/length_over_merge_fs_multiusage.sql new file mode 100644 index 0000000000..ff060f1ab3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/length_over_merge_fs_multiusage.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +pragma config.flags("OptimizerFlags", "FieldSubsetEnableMultiusage"); + +use plato; + +$input = (select key, key || subkey as subkey, value from Input); + +$total_count = (select count(1) from $input); + +$filtered = (select * from $input where key in ("023", "037", "075")); + +$filtered_cnt = (select count(1) from $filtered); + +select $filtered_cnt / cast($total_count as Double) as cnt; diff --git a/yql/essentials/tests/sql/suites/optimizers/multi_to_empty_constraint.cfg b/yql/essentials/tests/sql/suites/optimizers/multi_to_empty_constraint.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/multi_to_empty_constraint.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/multi_to_empty_constraint.sql b/yql/essentials/tests/sql/suites/optimizers/multi_to_empty_constraint.sql new file mode 100644 index 0000000000..d66d589ab8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/multi_to_empty_constraint.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ + +USE plato; + +select key, some(subkey) from (select * from Input where key > "010" and value in []) group by key +union all +select key, some(subkey) from (select * from Input where key > "020" and value in []) group by key +; diff --git a/yql/essentials/tests/sql/suites/optimizers/nonselected_direct_row.cfg b/yql/essentials/tests/sql/suites/optimizers/nonselected_direct_row.cfg new file mode 100644 index 0000000000..a6e1f32b6e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/nonselected_direct_row.cfg @@ -0,0 +1,3 @@ +in Input1 input3.txt +in Input2 input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/nonselected_direct_row.sql b/yql/essentials/tests/sql/suites/optimizers/nonselected_direct_row.sql new file mode 100644 index 0000000000..c0e6c1c34a --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/nonselected_direct_row.sql @@ -0,0 +1,8 @@ +use plato; + +select key, subkey, value +from ( + select TablePath() as tbl, key, subkey, value + from concat(Input1, Input2) +) +where tbl = "Input" and value != ""; diff --git a/yql/essentials/tests/sql/suites/optimizers/opt_key.txt b/yql/essentials/tests/sql/suites/optimizers/opt_key.txt new file mode 100644 index 0000000000..2a8e728cae --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/opt_key.txt @@ -0,0 +1,4 @@ +{"key"="023";"subkey"="3";"value"="aaa"}; +{"key"="037";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/opt_key.txt.attr b/yql/essentials/tests/sql/suites/optimizers/opt_key.txt.attr new file mode 100644 index 0000000000..72a3e1401c --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/opt_key.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";[OptionalType;["DataType";"String"]]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"String"]] + ]]; +}} diff --git a/yql/essentials/tests/sql/suites/optimizers/or_absorption.cfg b/yql/essentials/tests/sql/suites/optimizers/or_absorption.cfg new file mode 100644 index 0000000000..a1b36ede45 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/or_absorption.cfg @@ -0,0 +1,2 @@ +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/or_absorption.sql b/yql/essentials/tests/sql/suites/optimizers/or_absorption.sql new file mode 100644 index 0000000000..cbd7ba2db2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/or_absorption.sql @@ -0,0 +1,8 @@ +pragma config.flags("OptimizerFlags", "ExtractCommonPredicatesFromLogicalOps"); + +$a = 1 > 2; +$b = 3 < 4; +$c = 5 < 6; + +select ($a and $b or $b) == $b; +select ($c and ($b or $a) or $a or $b) == ($a or $b) diff --git a/yql/essentials/tests/sql/suites/optimizers/or_distributive.cfg b/yql/essentials/tests/sql/suites/optimizers/or_distributive.cfg new file mode 100644 index 0000000000..a1b36ede45 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/or_distributive.cfg @@ -0,0 +1,2 @@ +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/or_distributive.sql b/yql/essentials/tests/sql/suites/optimizers/or_distributive.sql new file mode 100644 index 0000000000..a44d871c85 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/or_distributive.sql @@ -0,0 +1,14 @@ +pragma config.flags("OptimizerFlags", "ExtractCommonPredicatesFromLogicalOps"); + +$a = 1 > 2; +$b = 3 < 4; +$c = 5 < 6; +$d = 7 > 8; +$e = 9 < 10; +$f = 11 > 12; + + +select (($a and $b) or ($b and $c)) == ($b and ($a or $c)); +select (($a and $b) or ($d and $e) or ($b and $c) or ($e and $f)) == + ($b and ($a or $c) or $e and ($d or $f)); + diff --git a/yql/essentials/tests/sql/suites/optimizers/passthrough_sortness_over_map.sql b/yql/essentials/tests/sql/suites/optimizers/passthrough_sortness_over_map.sql new file mode 100644 index 0000000000..0c6b58a563 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/passthrough_sortness_over_map.sql @@ -0,0 +1,31 @@ +/* postgres can not */ +/* kikimr can not - anon tables */ +USE plato; + +INSERT INTO @a +SELECT + * +FROM Input0 +ORDER BY key, subkey; + +commit; + +INSERT INTO @c +SELECT * FROM @a +WHERE key < "100" +ORDER BY key, subkey; + +INSERT INTO @d +SELECT + key as key, + "" as subkey, + "value:" || value as value +FROM @a +WHERE key < "100" +ORDER BY key; + +commit; + +select * from @c; + +select * from @d; diff --git a/yql/essentials/tests/sql/suites/optimizers/pushdown_nonsep_over_aggregate.cfg b/yql/essentials/tests/sql/suites/optimizers/pushdown_nonsep_over_aggregate.cfg new file mode 100644 index 0000000000..f3472752d8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/pushdown_nonsep_over_aggregate.cfg @@ -0,0 +1,3 @@ +in Input input5.txt +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/pushdown_nonsep_over_aggregate.sql b/yql/essentials/tests/sql/suites/optimizers/pushdown_nonsep_over_aggregate.sql new file mode 100644 index 0000000000..b3469e9a3b --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/pushdown_nonsep_over_aggregate.sql @@ -0,0 +1,12 @@ +USE plato; + +pragma config.flags("OptimizerFlags", "PushdownComplexFiltersOverAggregate"); + +SELECT * FROM ( + SELECT + key as key, + min(value) as mv + FROM Input + GROUP BY key +) +WHERE AssumeNonStrict(200 > 100) and (2000 > 1000) and key != "911" and (key < "150" and mv != "ddd" or key > "200"); diff --git a/yql/essentials/tests/sql/suites/optimizers/sort_by_nonstrict_const.cfg b/yql/essentials/tests/sql/suites/optimizers/sort_by_nonstrict_const.cfg new file mode 100644 index 0000000000..95d7627826 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sort_by_nonstrict_const.cfg @@ -0,0 +1,3 @@ +in Input input0.txt +providers yt +xfail diff --git a/yql/essentials/tests/sql/suites/optimizers/sort_by_nonstrict_const.sql b/yql/essentials/tests/sql/suites/optimizers/sort_by_nonstrict_const.sql new file mode 100644 index 0000000000..47abbabbd6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sort_by_nonstrict_const.sql @@ -0,0 +1,4 @@ +/* yt can not */ +use plato; + +select * from Input order by date, double; diff --git a/yql/essentials/tests/sql/suites/optimizers/sort_constraint_in_left.cfg b/yql/essentials/tests/sql/suites/optimizers/sort_constraint_in_left.cfg new file mode 100644 index 0000000000..3f5bebb5a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sort_constraint_in_left.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/sort_constraint_in_left.sql b/yql/essentials/tests/sql/suites/optimizers/sort_constraint_in_left.sql new file mode 100644 index 0000000000..e1262ecbeb --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sort_constraint_in_left.sql @@ -0,0 +1,19 @@ +/* postgres can not */ + +USE plato; + +$input_table = (SELECT * FROM Input); + +SELECT * FROM ( + SELECT 'total' AS key, + COUNT(*) AS count + FROM $input_table + WHERE key != "1" + UNION ALL + SELECT key, + COUNT(*) AS count + FROM $input_table + WHERE key != "1" + GROUP BY key +) +ORDER BY key, count; diff --git a/yql/essentials/tests/sql/suites/optimizers/sort_over_sorted_prefix_keys.sql b/yql/essentials/tests/sql/suites/optimizers/sort_over_sorted_prefix_keys.sql new file mode 100644 index 0000000000..6a1e4d9aab --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sort_over_sorted_prefix_keys.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +/* kikimr can not - anon tables */ +USE plato; + +insert into @a +SELECT + * +FROM Input0 +ORDER BY key ASC, subkey ASC; + +commit; + +select * from @a +ORDER BY key ASC; diff --git a/yql/essentials/tests/sql/suites/optimizers/sort_over_sorted_same_keys.sql b/yql/essentials/tests/sql/suites/optimizers/sort_over_sorted_same_keys.sql new file mode 100644 index 0000000000..d744ef5418 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sort_over_sorted_same_keys.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +/* kikimr can not - anon tables */ +USE plato; + +insert into @a +SELECT + * +FROM Input0 +ORDER BY key ASC, subkey ASC; + +commit; + +select * from @a +ORDER BY key ASC, subkey ASC; diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted.txt b/yql/essentials/tests/sql/suites/optimizers/sorted.txt new file mode 100644 index 0000000000..2a8e728cae --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted.txt @@ -0,0 +1,4 @@ +{"key"="023";"subkey"="3";"value"="aaa"}; +{"key"="037";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted.txt.attr b/yql/essentials/tests/sql/suites/optimizers/sorted.txt.attr new file mode 100644 index 0000000000..36f279e4a0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted.txt.attr @@ -0,0 +1,11 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"String"]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"String"]]] + ]; + "SortDirections"=[1;1;]; + "SortedBy"=["key";"subkey";]; + "SortedByTypes"=[["DataType";"String";];["DataType";"String";];]; + "SortMembers"=["key";"subkey";]; +}} diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted_desc.txt b/yql/essentials/tests/sql/suites/optimizers/sorted_desc.txt new file mode 100644 index 0000000000..235fc7cd49 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted_desc.txt @@ -0,0 +1,10 @@ +{"_yql_column_0"="\xE0\xC6\xCE\xCE\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="911";"subkey"="2";"value"="kkk"}; +{"_yql_column_0"="\xE0\xC8\xC9\xCE\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="761";"subkey"="6";"value"="ccc"}; +{"_yql_column_0"="\xE0\xCA\xCD\xC8\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="527";"subkey"="4";"value"="bbb"}; +{"_yql_column_0"="\xE0\xCD\xCF\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="200";"subkey"="7";"value"="qqq"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="1";"value"="aaa"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="3";"value"="iii"}; +{"_yql_column_0"="\xE0\xCE\xCA\xCF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="150";"subkey"="8";"value"="zzz"}; +{"_yql_column_0"="\xE0\xCF\xC8\xCA\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="075";"subkey"="1";"value"="abc"}; +{"_yql_column_0"="\xE0\xCF\xCC\xC8\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="037";"subkey"="5";"value"="ddd"}; +{"_yql_column_0"="\xE0\xCF\xCD\xCC\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFC";"key"="023";"subkey"="3";"value"="aaa"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted_desc.txt.attr b/yql/essentials/tests/sql/suites/optimizers/sorted_desc.txt.attr new file mode 100644 index 0000000000..5a6ca5761e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted_desc.txt.attr @@ -0,0 +1,47 @@ +{ + "_yql_row_spec" = { + "SortMembers" = [ + "key" + ]; + "SortDirections" = [ + 0 + ]; + "UniqueKeys" = %false; + "SortedByTypes" = [ + [ + "DataType"; + "String" + ] + ]; + "StrictSchema" = %true; + "Type" = [ + "StructType"; + [ + [ + "key"; + [ + "DataType"; + "String" + ] + ]; + [ + "subkey"; + [ + "DataType"; + "String" + ] + ]; + [ + "value"; + [ + "DataType"; + "String" + ] + ] + ] + ]; + "SortedBy" = [ + "_yql_column_0" + ] + } +}
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted_scalar_content.cfg b/yql/essentials/tests/sql/suites/optimizers/sorted_scalar_content.cfg new file mode 100644 index 0000000000..ac3bc7d1d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted_scalar_content.cfg @@ -0,0 +1,3 @@ +in Input sorted.txt +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted_scalar_content.sql b/yql/essentials/tests/sql/suites/optimizers/sorted_scalar_content.sql new file mode 100644 index 0000000000..df6d3131d7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted_scalar_content.sql @@ -0,0 +1,13 @@ +use plato; + +$ou = select * from Input; + +$a = select key from $ou where key > '0'; + +insert into @a +select * from $a order by key; + +select * from $ou +where subkey > "0" + and key != $a +order by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted_sql_in.cfg b/yql/essentials/tests/sql/suites/optimizers/sorted_sql_in.cfg new file mode 100644 index 0000000000..ac3bc7d1d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted_sql_in.cfg @@ -0,0 +1,3 @@ +in Input sorted.txt +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/sorted_sql_in.sql b/yql/essentials/tests/sql/suites/optimizers/sorted_sql_in.sql new file mode 100644 index 0000000000..c0debae4be --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/sorted_sql_in.sql @@ -0,0 +1,13 @@ +use plato; + +$ou = select * from Input; + +$a = select * from $ou where key > '0'; + +insert into @a +select * from $a order by key; + +select * from $ou +where subkey > "0" + and key not in compact (select key from $a) +order by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/test_fuse_map_predicate_limit.sql b/yql/essentials/tests/sql/suites/optimizers/test_fuse_map_predicate_limit.sql new file mode 100644 index 0000000000..f7a96e9e48 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/test_fuse_map_predicate_limit.sql @@ -0,0 +1,12 @@ +/* postgres can not */ + +$data = ( + SELECT key AS Key, YQL::Substring(key, 1, 1) AS Category FROM plato.Input0 WHERE length(key) > 2 LIMIT 20 +); + +SELECT + Category, + COUNT(*) +FROM $data +GROUP BY Category +ORDER BY Category ASC;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/test_fuse_map_take.sql b/yql/essentials/tests/sql/suites/optimizers/test_fuse_map_take.sql new file mode 100644 index 0000000000..eeb0cfcf24 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/test_fuse_map_take.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +$data = ( + SELECT key AS Name, value AS Value FROM plato.Input0 +); + +$filtered = ( + SELECT * FROM $data WHERE Name != "BadName" LIMIT 10 +); + +SELECT Name, Avg(Length(Value)) AS Len FROM $filtered GROUP BY Name ORDER BY Name; diff --git a/yql/essentials/tests/sql/suites/optimizers/test_lmap_opts.cfg b/yql/essentials/tests/sql/suites/optimizers/test_lmap_opts.cfg new file mode 100644 index 0000000000..1aff987614 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/test_lmap_opts.cfg @@ -0,0 +1,5 @@ +in Input0 input0.txt +udf streaming_udf +udf python2_udf +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/test_lmap_opts.sql b/yql/essentials/tests/sql/suites/optimizers/test_lmap_opts.sql new file mode 100644 index 0000000000..90c0f8a2a7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/test_lmap_opts.sql @@ -0,0 +1,47 @@ +/* syntax version 1 */ +-- not supported on windows +/* postgres can not */ +$udfScript = @@ + +import collections; + +def processRows(prefix, rowList, separator): + ResultRow = collections.namedtuple("ResultRow", ["Result"]); + + result = []; + for row in rowList: + resultValue = prefix + row.Name + separator + row.Value; + resultItem = ResultRow(Result=resultValue); + result.append(resultItem); + + return result; +@@; + +$udf = Python::processRows( + Callable<(String, List<Struct<Name:String, Value:String>>, String)->List<Struct<Result:String>>>, + $udfScript +); + +$data = ( + SELECT key AS Name, value AS Value FROM plato.Input0 +); + +$prefix = ">>"; + +$p1 = ( + PROCESS $data USING $udf($prefix, TableRows(), "=") WHERE Name != "foo" +); + +$p2 = ( + SELECT Result AS Data FROM $p1 +); + +$p3 = ( + PROCESS $p2 USING Streaming::Process(TableRows(), "grep", AsList("180")) +); + +$p4 = ( + SELECT Data AS FinalResult FROM $p3 +); + +SELECT Avg(Length(FinalResult)) AS AvgResultLength FROM $p4; diff --git a/yql/essentials/tests/sql/suites/optimizers/test_no_aggregate_split.cfg b/yql/essentials/tests/sql/suites/optimizers/test_no_aggregate_split.cfg new file mode 100644 index 0000000000..8ca23afed0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/test_no_aggregate_split.cfg @@ -0,0 +1,2 @@ +in Input1 input1.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/test_no_aggregate_split.sql b/yql/essentials/tests/sql/suites/optimizers/test_no_aggregate_split.sql new file mode 100644 index 0000000000..a5baec070e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/test_no_aggregate_split.sql @@ -0,0 +1,15 @@ +/* postgres can not */ + +$aggregated = ( + SELECT Group, Name, SUM(Amount) AS TotalAmount + FROM plato.Input1 + GROUP BY Group, Name +); + +SELECT t.Comment, a.TotalAmount +FROM plato.Input1 AS t +INNER JOIN $aggregated AS a +ON t.Group == a.Group AND t.Name == a.Name +ORDER BY t.Comment, a.TotalAmount; + +SELECT TotalAmount FROM $aggregated ORDER BY TotalAmount; diff --git a/yql/essentials/tests/sql/suites/optimizers/total_order.sql b/yql/essentials/tests/sql/suites/optimizers/total_order.sql new file mode 100644 index 0000000000..c9b185a101 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/total_order.sql @@ -0,0 +1,29 @@ +/* postgres can not */ +$x = AsTuple(Double("nan"),42); + +select $x = $x; +select $x < $x; +select $x <= $x; +select $x > $x; +select $x >= $x; +select $x != $x; + +$x = AsStruct(Double("nan") as a,42 as b); +select $x = $x; +select $x != $x; + +$x = AsTuple(Nothing(ParseType("Int32?")), 1); +select $x = $x; +select $x < $x; +select $x <= $x; +select $x > $x; +select $x >= $x; +select $x != $x; + +$x = Nothing(ParseType("Int32?")); +select $x = $x; +select $x < $x; +select $x <= $x; +select $x > $x; +select $x >= $x; +select $x != $x; diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_group.cfg b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group.cfg new file mode 100644 index 0000000000..93653023e1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group.cfg @@ -0,0 +1,2 @@ +in Input unused_columns_window.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_group.sql b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group.sql new file mode 100644 index 0000000000..0fb9467dc7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group.sql @@ -0,0 +1,9 @@ +USE plato; + +select a,bb from ( +select a,count(distinct b) as bb,max(c) as cc,median(c) as cc1,percentile(c,0.8) as cc2 from ( +select a,b,cast(c as int32) as c,d from Input +) +group by a +) +order by a
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_group_one_of_multi.cfg b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group_one_of_multi.cfg new file mode 100644 index 0000000000..2b2bac89e3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group_one_of_multi.cfg @@ -0,0 +1,3 @@ +in Input unused_columns_window.txt +udf stat_udf +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_group_one_of_multi.sql b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group_one_of_multi.sql new file mode 100644 index 0000000000..eac1ef2643 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_group_one_of_multi.sql @@ -0,0 +1,8 @@ +USE plato; + +select a,cc1 from ( +select a,count(distinct b) as bb,max(c) as cc,median(c) as cc1,percentile(c,0.8) as cc2 from ( +select a,b,cast(c as int32) as c,d from Input +) +group by a +) diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.cfg b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.cfg new file mode 100644 index 0000000000..93653023e1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.cfg @@ -0,0 +1,2 @@ +in Input unused_columns_window.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.sql b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.sql new file mode 100644 index 0000000000..ee5e54df18 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.sql @@ -0,0 +1,9 @@ +USE plato; + +SELECT + a, + lag(a) over w as prev_a, + min(a) over w as min_a +FROM Input +WINDOW w AS (PARTITION BY b ORDER by c) +ORDER BY a; diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.txt b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.txt new file mode 100644 index 0000000000..15c7031586 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.txt @@ -0,0 +1,4 @@ +{"a"="1";"b"="2";"c"="1";"d"="5"}; +{"a"="2";"b"="2";"c"="2";"d"="6"}; +{"a"="3";"b"="3";"c"="1";"d"="7"}; +{"a"="4";"b"="3";"c"="2";"d"="8"}; diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.txt.attr b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.txt.attr new file mode 100644 index 0000000000..2b156c3232 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window.txt.attr @@ -0,0 +1,8 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["a";["DataType";"String"]]; + ["b";["DataType";"String"]]; + ["c";["DataType";"String"]]; + ["d";["DataType";"String"]] + ]]; +}} diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_window_no_payloads.cfg b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window_no_payloads.cfg new file mode 100644 index 0000000000..93653023e1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window_no_payloads.cfg @@ -0,0 +1,2 @@ +in Input unused_columns_window.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/unused_columns_window_no_payloads.sql b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window_no_payloads.sql new file mode 100644 index 0000000000..65c7a1bf64 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/unused_columns_window_no_payloads.sql @@ -0,0 +1,9 @@ +USE plato; + +select b from ( +SELECT + b, + lag(a) over w as prev_a +FROM Input +WINDOW w AS (PARTITION BY b ORDER by c) +) diff --git a/yql/essentials/tests/sql/suites/optimizers/wide_if_present_over_double_just.sql b/yql/essentials/tests/sql/suites/optimizers/wide_if_present_over_double_just.sql new file mode 100644 index 0000000000..d8da491aa4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/wide_if_present_over_double_just.sql @@ -0,0 +1 @@ +select ListFromRange(Date("2022-01-01"), Just(Date("2022-03-01")), Interval("P1D") * 2U); diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-10042_disable_flow_fuse_depends_on.sql b/yql/essentials/tests/sql/suites/optimizers/yql-10042_disable_flow_fuse_depends_on.sql new file mode 100644 index 0000000000..a828dc1cff --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-10042_disable_flow_fuse_depends_on.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +select RandomNumber(a) as first, RandomNumber(b) as second from ( + select key as a, key as b from Input0 +) order by first, second; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-10042_disable_fuse_depends_on.sql b/yql/essentials/tests/sql/suites/optimizers/yql-10042_disable_fuse_depends_on.sql new file mode 100644 index 0000000000..421ded4a1f --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-10042_disable_fuse_depends_on.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +/* syntax version 1 */ +$data = AsList((1 as a, 1 as b)); +select RandomNumber(a), RandomNumber(b) from AS_TABLE($data); diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-10070_extract_members_over_calcoverwindow.sql b/yql/essentials/tests/sql/suites/optimizers/yql-10070_extract_members_over_calcoverwindow.sql new file mode 100644 index 0000000000..9b0e18cc0b --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-10070_extract_members_over_calcoverwindow.sql @@ -0,0 +1,23 @@ +/* postgres can not */ +USE plato; + +INSERT INTO @source +SELECT Date('2019-03-04') AS `Date`, + 1l AS `Permalink`, + 1l AS `ClusterPermalink`, + False AS `IsHead`, + False AS `WasHead`, + 23 as dummy1; +COMMIT; + +SELECT + IF( + NOT `WasHead` AND NOT `IsHead`, + aggregate_list(AsStruct(`Permalink` AS `Permalink`, `Date` AS `Date`, `ClusterPermalink` AS ClusterPermalink)) OVER `w` + ) AS `Occurence` +FROM + @source +WINDOW `w` AS ( + PARTITION BY `Permalink` + ORDER BY `Date` +) diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-10074_dont_inline_lists_depends_on.sql b/yql/essentials/tests/sql/suites/optimizers/yql-10074_dont_inline_lists_depends_on.sql new file mode 100644 index 0000000000..7a1dd02e70 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-10074_dont_inline_lists_depends_on.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* syntax version 1 */ +SELECT a, b, c, RandomNumber(a) AS r FROM ( + SELECT 1 AS a, 2 AS b, 3 AS c + UNION ALL + SELECT 1 AS a, 2 AS b, 3 AS c +); diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-10737_lost_passthrough.sql b/yql/essentials/tests/sql/suites/optimizers/yql-10737_lost_passthrough.sql new file mode 100644 index 0000000000..92271a89b1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-10737_lost_passthrough.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* multirun can not */ +/* syntax version 1 */ +/* kikimr can not - table truncate */ +USE plato; + +insert into @a +select "1" as Text, ["a", "b"] as Attachments; + +commit; + +SELECT x.*, "" AS Text, ListCreate(TypeOf(Attachments)) AS Attachments +WITHOUT x.Text, x.Attachments +FROM @a AS x +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-11171_unordered_over_sorted_fill.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-11171_unordered_over_sorted_fill.cfg new file mode 100644 index 0000000000..a3de89f626 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-11171_unordered_over_sorted_fill.cfg @@ -0,0 +1,4 @@ +in Input sorted.txt +res result.txt +providers yt + diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-11171_unordered_over_sorted_fill.sql b/yql/essentials/tests/sql/suites/optimizers/yql-11171_unordered_over_sorted_fill.sql new file mode 100644 index 0000000000..d76011161b --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-11171_unordered_over_sorted_fill.sql @@ -0,0 +1,18 @@ +/* syntax version 1 */ + +USE plato; +select + key, + some(value) +from ( + select + key, + TableName() as value + from Input with inline + union all + select + key, + value + from Input +) +group compact by key; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-12620_stage_multiuse.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-12620_stage_multiuse.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-12620_stage_multiuse.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-12620_stage_multiuse.sql b/yql/essentials/tests/sql/suites/optimizers/yql-12620_stage_multiuse.sql new file mode 100644 index 0000000000..cae154861d --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-12620_stage_multiuse.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +$a=select * from Input; +select count(*) from $a; +select count(*) from $a where key != '075'; +select * from $a where key != '075';
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-14279_keyextract_with_world_dep.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-14279_keyextract_with_world_dep.cfg new file mode 100644 index 0000000000..745508f188 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-14279_keyextract_with_world_dep.cfg @@ -0,0 +1,4 @@ +in Input1 sorted.txt +in Input2 sorted.txt +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-14279_keyextract_with_world_dep.sql b/yql/essentials/tests/sql/suites/optimizers/yql-14279_keyextract_with_world_dep.sql new file mode 100644 index 0000000000..3cdbaeb766 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-14279_keyextract_with_world_dep.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +use plato; + +$input = select * from range("", "Input1", "Input2"); + +$key = select min(key) from $input; + +select key, subkey, value +from $input +where subkey > '1' and key > $key +order by key +; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-14581_fuseflatmaps_with_external_lambda.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-14581_fuseflatmaps_with_external_lambda.cfg new file mode 100644 index 0000000000..0df3369260 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-14581_fuseflatmaps_with_external_lambda.cfg @@ -0,0 +1,3 @@ +res result.txt +providers yt +udf unicode_udf
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-14581_fuseflatmaps_with_external_lambda.sql b/yql/essentials/tests/sql/suites/optimizers/yql-14581_fuseflatmaps_with_external_lambda.sql new file mode 100644 index 0000000000..1302d6dde0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-14581_fuseflatmaps_with_external_lambda.sql @@ -0,0 +1,28 @@ +/* postgres can not */ +USE plato; + +$improve_low = ($val) -> { + RETURN CAST($val AS Utf8); +}; + +$names_intersection = ($org_names, $db_names) -> { + RETURN ListLength( + ListFlatten( + ListMap( + $org_names, + ($org_name) -> { + RETURN ListFilter( + $db_names, + ($db_name) -> { + $org_name = $improve_low($org_name); + $db_name = $improve_low($db_name); + RETURN Unicode::LevensteinDistance($org_name, $db_name) < 0.2 * Unicode::GetLength($org_name); + } + ); + } + ) + ) + ) > 0; +}; + +select $names_intersection(['1', '2'], ['nets']) diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-15210_sqlin.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-15210_sqlin.cfg new file mode 100644 index 0000000000..7492954c20 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-15210_sqlin.cfg @@ -0,0 +1,3 @@ +in Input input3.txt +res result.txt +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-15210_sqlin.sql b/yql/essentials/tests/sql/suites/optimizers/yql-15210_sqlin.sql new file mode 100644 index 0000000000..7054019fb5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-15210_sqlin.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +USE plato; + +$max = select max(key) from Input; +$list = select key from Input where subkey > "1"; + +select * from ( + select if(key = $max, "max", key) as key, value from Input +) +where key in compact $list
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-16134.sql b/yql/essentials/tests/sql/suites/optimizers/yql-16134.sql new file mode 100644 index 0000000000..8eeb54afe9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-16134.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select Just(Just(true)) = true, Just(false) != Just(Just(false))
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-17413-topsort.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-17413-topsort.cfg new file mode 100644 index 0000000000..c85115a8e4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-17413-topsort.cfg @@ -0,0 +1,2 @@ +in Input sorted_desc.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-17413-topsort.sql b/yql/essentials/tests/sql/suites/optimizers/yql-17413-topsort.sql new file mode 100644 index 0000000000..646b84b1dd --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-17413-topsort.sql @@ -0,0 +1,10 @@ +-- ignore runonopt plan diff +USE plato; + +$filtered = select * from Input where value != "xxx"; + +select distinct(subkey) as subkey +from (select * from $filtered order by key desc limit 3) +order by subkey; + +select sum(cast(subkey as int32)) as c from $filtered; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-17715_concat_sort_desc.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-17715_concat_sort_desc.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-17715_concat_sort_desc.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-17715_concat_sort_desc.sql b/yql/essentials/tests/sql/suites/optimizers/yql-17715_concat_sort_desc.sql new file mode 100644 index 0000000000..10cae73c84 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-17715_concat_sort_desc.sql @@ -0,0 +1,43 @@ +use plato; + +$min_ts_for_stat_calculation = DateTime::ToSeconds(CurrentUtcDate() - Interval("P1D")); + +insert into @a +select * from ( + select 1ul as puid, CurrentUtcTimestamp() as timestamp, [1, 2] as segments, "a" as dummy1 +) +assume order by puid, timestamp desc; + +insert into @b +select * from ( + select 4ul as puid, CurrentUtcTimestamp() as timestamp, [3, 2] as segments, "a" as dummy1 +) +assume order by puid, timestamp desc; + +insert into @c +select * from ( + select 2ul as puid, Just(CurrentUtcTimestamp()) as timestamp, [2, 3] as segments, "a" as dummy2 +) +assume order by puid, timestamp desc; + +commit; + +$target_events = ( + SELECT + puid, + segments + FROM CONCAT(@a, @b, @c) + where DateTime::ToSeconds(`timestamp`) > $min_ts_for_stat_calculation +); + +$target_events = ( + SELECT DISTINCT * + FROM ( + SELECT * + FROM $target_events + FLATTEN LIST BY segments + ) + FLATTEN COLUMNS +); + +SELECT * FROM $target_events ORDER BY puid, segments; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-18300-flatmap-over-extend.sql b/yql/essentials/tests/sql/suites/optimizers/yql-18300-flatmap-over-extend.sql new file mode 100644 index 0000000000..7bd19b7bee --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-18300-flatmap-over-extend.sql @@ -0,0 +1,19 @@ +USE plato; + +insert into @tmp with truncate +select "dummy" as a, "1" as b, ["b", "s"] as data +order by a; + +commit; + + +SELECT a, id +FROM ( + SELECT + a, + ListExtend( + [String::AsciiToLower(b)], + ListMap(data, String::AsciiToLower) + ) AS joins + FROM @tmp +) FLATTEN LIST BY joins AS id; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-18408_filter_multiusage_pushdown.sql b/yql/essentials/tests/sql/suites/optimizers/yql-18408_filter_multiusage_pushdown.sql new file mode 100644 index 0000000000..ad3afa8596 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-18408_filter_multiusage_pushdown.sql @@ -0,0 +1,84 @@ +use plato; +pragma AnsiOptionalAs; +pragma config.flags("OptimizerFlags", + "FieldSubsetEnableMultiusage", + "FilterPushdownEnableMultiusage", + "EarlyExpandSkipNull"); + + +$date_dim = select * from as_table([ + <|d_year:Just(1999), d_date_sk:Just(10001)|>, + <|d_year:Just(1999), d_date_sk:Just(10002)|>, + <|d_year:Just(1999), d_date_sk:Just(10003)|>, + <|d_year:Just(2000), d_date_sk:Just(10001)|>, + <|d_year:Just(2000), d_date_sk:Just(10002)|>, + <|d_year:Just(2000), d_date_sk:Just(10003)|>, +]); + +$customer = select * from as_table([ + <|c_customer_sk:Just(1), c_customer_id:Just(1), c_first_name:Just("Vasya"), c_last_name:Just("Ivanov"), c_preferred_cust_flag:Just("aaa"), c_birth_country:Just("RU"), c_login:Just("ivanov"), c_email_address:Just("foo@bar.com")|>, + <|c_customer_sk:Just(2), c_customer_id:Just(2), c_first_name:Just("Petya"), c_last_name:Just("Ivanov"), c_preferred_cust_flag:Just("bbb"), c_birth_country:Just("RU"), c_login:Just("ivanov1"), c_email_address:Just("foo1@bar.com")|>, + <|c_customer_sk:Just(3), c_customer_id:null, c_first_name:null, c_last_name:null, c_preferred_cust_flag:null, c_birth_country:null, c_login:Just("ivanov1"), c_email_address:Just("foo2@bar.com")|>, +]); + +$store_sales = select * from as_table([ + <|ss_sold_date_sk:Just(10001), ss_customer_sk:Just(1), ss_ext_list_price:Just(12345), ss_ext_discount_amt:Just(1234)|>, + <|ss_sold_date_sk:Just(10002), ss_customer_sk:Just(2), ss_ext_list_price:Just(12346), ss_ext_discount_amt:Just(123)|>, + <|ss_sold_date_sk:Just(10003), ss_customer_sk:Just(3), ss_ext_list_price:Just(12347), ss_ext_discount_amt:Just(1235)|>, +]); + +insert into @date_dim +select * from $date_dim; + +insert into @customer +select * from $customer; + +insert into @store_sales +select * from $store_sales; + +commit; + + +$year_total = ( + select customer.c_customer_id customer_id + ,customer.c_first_name customer_first_name + ,customer.c_last_name customer_last_name + ,customer.c_preferred_cust_flag customer_preferred_cust_flag + ,customer.c_birth_country customer_birth_country + ,customer.c_login customer_login + ,customer.c_email_address customer_email_address + ,date_dim.d_year dyear + ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total + ,'s' sale_type + from @date_dim date_dim + cross join @store_sales store_sales + cross join @customer customer + where ss_sold_date_sk = d_date_sk and c_customer_sk = ss_customer_sk + group by customer.c_customer_id + ,customer.c_first_name + ,customer.c_last_name + ,customer.c_preferred_cust_flag + ,customer.c_birth_country + ,customer.c_login + ,customer.c_email_address + ,date_dim.d_year + ); + + + select + t_s_secyear.customer_id + ,t_s_secyear.customer_first_name + ,t_s_secyear.customer_last_name + ,t_s_secyear.customer_birth_country + from $year_total t_s_firstyear + cross join $year_total t_s_secyear + where t_s_secyear.customer_id = t_s_firstyear.customer_id + and t_s_firstyear.sale_type = 's' + and t_s_secyear.sale_type = 's' + and t_s_firstyear.dyear = 1999 + and t_s_secyear.dyear = 1999+1 + order by t_s_secyear.customer_id + ,t_s_secyear.customer_first_name + ,t_s_secyear.customer_last_name + ,t_s_secyear.customer_birth_country +limit 100; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-18733_no_filter_multiusage_pushdown.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-18733_no_filter_multiusage_pushdown.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-18733_no_filter_multiusage_pushdown.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-18733_no_filter_multiusage_pushdown.sql b/yql/essentials/tests/sql/suites/optimizers/yql-18733_no_filter_multiusage_pushdown.sql new file mode 100644 index 0000000000..56f9ddfde9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-18733_no_filter_multiusage_pushdown.sql @@ -0,0 +1,6 @@ +pragma config.flags("OptimizerFlags", "FilterPushdownEnableMultiusage"); +USE plato; + +$src = select distinct key from Input where value = 'ddd'; + +select * from Input where key = $src; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-2171_aggregate_desc_sort_and_extract.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-2171_aggregate_desc_sort_and_extract.cfg new file mode 100644 index 0000000000..208adc1e38 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-2171_aggregate_desc_sort_and_extract.cfg @@ -0,0 +1,4 @@ +in Input input_tutorial_users.txt +res result.txt +providers yt + diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-2171_aggregate_desc_sort_and_extract.sql b/yql/essentials/tests/sql/suites/optimizers/yql-2171_aggregate_desc_sort_and_extract.sql new file mode 100644 index 0000000000..0cce1f9889 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-2171_aggregate_desc_sort_and_extract.sql @@ -0,0 +1,16 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +$top_users_by_age_dec = (SELECT + age_dec, + COUNT(1) as age_dec_count +FROM $data +GROUP BY age / 10 as age_dec +ORDER BY age_dec_count DESC +LIMIT 2); + +--INSERT INTO Output +SELECT age_dec, info.* FROM $top_users_by_age_dec AS top JOIN $data AS info ON top.age_dec = info.age / 10 ORDER BY name; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input.cfg new file mode 100644 index 0000000000..a6e1f32b6e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input.cfg @@ -0,0 +1,3 @@ +in Input1 input3.txt +in Input2 input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input.sql b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input.sql new file mode 100644 index 0000000000..8887d13fcd --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +USE plato; + +$data = (SELECT * FROM Input1 WHERE key < "700" LIMIT 10); + +SELECT * FROM $data LIMIT 100; + +SELECT a.key AS key, b.subkey AS subkey, b.value AS value +FROM $data AS a +INNER JOIN Input2 AS b ON a.key = b.key +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input_other.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input_other.cfg new file mode 100644 index 0000000000..fd0ec5927e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input_other.cfg @@ -0,0 +1,3 @@ +in Input1 input_other.txt +in Input2 input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input_other.sql b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input_other.sql new file mode 100644 index 0000000000..a7c735a9ce --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-2582_limit_for_join_input_other.sql @@ -0,0 +1,12 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$data = (SELECT * FROM Input1 LIMIT 10); + +SELECT key, subkey, _other["value"] FROM $data LIMIT 100; + +SELECT a.key AS key, b.subkey AS subkey, b.value AS value +FROM $data AS a +INNER JOIN Input2 AS b ON a.key = b.key +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-3455_filter_sorted.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-3455_filter_sorted.cfg new file mode 100644 index 0000000000..3f5bebb5a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-3455_filter_sorted.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-3455_filter_sorted.sql b/yql/essentials/tests/sql/suites/optimizers/yql-3455_filter_sorted.sql new file mode 100644 index 0000000000..99d000cfa0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-3455_filter_sorted.sql @@ -0,0 +1,18 @@ +/* postgres can not */ +USE plato; + +SELECT + subkey +FROM Input +WHERE subkey < "100"; + +SELECT + value +FROM Input +LIMIT 3; + +SELECT + key +FROM + (SELECT * FROM Input ORDER BY -CAST(subkey as Int32) LIMIT 5) +; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-4240-aggregate_whole_struct.sql b/yql/essentials/tests/sql/suites/optimizers/yql-4240-aggregate_whole_struct.sql new file mode 100644 index 0000000000..bf646532d4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-4240-aggregate_whole_struct.sql @@ -0,0 +1,16 @@ +/* postgres can not */ +use plato; + +$data = (select value as attr, key as urlBase, cast(subkey as int32) as dupsCount from Input0); + + SELECT + urlBase, + SUM(dupsCount) as allDocs, + MAX_BY(AsStruct(dupsCount as dupsCount, attr as attr), dupsCount) as best + FROM ( + SELECT urlBase, attr, count(*) as dupsCount + FROM $data + GROUP BY urlBase, attr + ) + GROUP BY urlBase + ORDER BY urlBase diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-5833-table_content.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-5833-table_content.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-5833-table_content.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-5833-table_content.sql b/yql/essentials/tests/sql/suites/optimizers/yql-5833-table_content.sql new file mode 100644 index 0000000000..4ca893b99b --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-5833-table_content.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +use plato; + +$max_key = ( +select +max(key) +from Input +); + +select +cast(count(*) as String) || ' (' || cast($max_key as String) ||'/24)' +from Input +where key = $max_key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-5978_fill_multi_usage.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-5978_fill_multi_usage.cfg new file mode 100644 index 0000000000..551221cf56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-5978_fill_multi_usage.cfg @@ -0,0 +1,3 @@ +in Input input3.txt +out Output output.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-5978_fill_multi_usage.sql b/yql/essentials/tests/sql/suites/optimizers/yql-5978_fill_multi_usage.sql new file mode 100644 index 0000000000..1671816eec --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-5978_fill_multi_usage.sql @@ -0,0 +1,29 @@ +/* postgres can not */ +USE plato; + +$ctl = ( + SELECT 1 AS join_col + , MAX(key) AS max + FROM Input + ); + +INSERT INTO Output WITH TRUNCATE +SELECT * FROM $ctl; + +$in = ( + SELECT 1 AS join_col + , key + , subkey + , value + FROM Input + ); + +SELECT + a.key AS key + , a.subkey AS subkey + , a.value AS value +FROM $in AS a +LEFT JOIN $ctl AS ctl + USING (join_col) +WHERE key < max +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-6008_limit_after_map.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-6008_limit_after_map.cfg new file mode 100644 index 0000000000..66737248b8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-6008_limit_after_map.cfg @@ -0,0 +1,2 @@ +in Input sorted.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-6008_limit_after_map.sql b/yql/essentials/tests/sql/suites/optimizers/yql-6008_limit_after_map.sql new file mode 100644 index 0000000000..53001bf902 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-6008_limit_after_map.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* multirun can not */ +USE plato; + +insert into Output +select * from plato.Input where value != "111" limit 3; + +commit; + +insert into Output +select * from plato.Input order by value; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-6038_direct_row.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-6038_direct_row.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-6038_direct_row.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-6038_direct_row.sql b/yql/essentials/tests/sql/suites/optimizers/yql-6038_direct_row.sql new file mode 100644 index 0000000000..05c6779782 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-6038_direct_row.sql @@ -0,0 +1,25 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$queries_0 = (select distinct key from Input); + +$queries = (select TableRecordIndex() as j, key from $queries_0); + +$count = (select count(*) from $queries); + +$users_0 = ( + select ListFromRange(0, 3) as lst, TableRecordIndex() as idx, subkey from Input as t +); + +$users = ( + select + cast(Random(idx + x) as Uint64) % $count as j, + subkey + from $users_0 + flatten by lst as x +); + +select * +from $queries as queries join $users as users using(j) +order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-6133_skip_deps.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-6133_skip_deps.cfg new file mode 100644 index 0000000000..e934bc69c9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-6133_skip_deps.cfg @@ -0,0 +1,4 @@ +in Input input3.txt +out Output output.txt +providers yt + diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-6133_skip_deps.sql b/yql/essentials/tests/sql/suites/optimizers/yql-6133_skip_deps.sql new file mode 100644 index 0000000000..6d8c66947a --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-6133_skip_deps.sql @@ -0,0 +1,28 @@ +/* postgres can not */ +/* multirun can not */ +USE plato; + +$out = ( +SELECT + * +FROM + `Input` +WHERE + value != "111" +); + +$row_count = ( + SELECT + COUNT(*) + FROM + $out +); + +$needed_row = COALESCE(CAST(CAST($row_count as float) * 0.5 as Uint64), 1); + +INSERT INTO Output WITH TRUNCATE +SELECT + * +FROM $out ORDER BY key DESC +LIMIT 1 OFFSET $needed_row; + diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-7324_duplicate_arg.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-7324_duplicate_arg.cfg new file mode 100644 index 0000000000..0781bebbd5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-7324_duplicate_arg.cfg @@ -0,0 +1,3 @@ +in Input input2.txt +res result.txt +udf strings_udf diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-7324_duplicate_arg.sql b/yql/essentials/tests/sql/suites/optimizers/yql-7324_duplicate_arg.sql new file mode 100644 index 0000000000..df26751a74 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-7324_duplicate_arg.sql @@ -0,0 +1,23 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; + +$to_int = ($x) -> { return cast($x as Int32) }; +$to_in_list = ($col) -> { return ListMap(String::SplitToList($col, ","), $to_int) }; + +$input = ( + SELECT + $to_in_list(key) AS event_ids, + $to_in_list(subkey) AS test_ids + FROM + Input + WHERE + value = "aaa" +); + +SELECT + event_id, + test_ids +FROM + $input +FLATTEN BY event_ids AS event_id; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-7532_wrong_field_subset_for_calcoverwindow.sql b/yql/essentials/tests/sql/suites/optimizers/yql-7532_wrong_field_subset_for_calcoverwindow.sql new file mode 100644 index 0000000000..5d8a388f94 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-7532_wrong_field_subset_for_calcoverwindow.sql @@ -0,0 +1,23 @@ +/* postgres can not */ +USE plato; + +INSERT INTO @source +SELECT Date('2019-03-04') AS `Date`, + 1l AS `Permalink`, + 1l AS `ClusterPermalink`, + False AS `IsHead`, + False AS `WasHead`; +COMMIT; + +SELECT + IF( + NOT `WasHead` AND NOT `IsHead`, + aggregate_list(AsStruct(`Permalink` AS `Permalink`, `Date` AS `Date`, `ClusterPermalink` AS ClusterPermalink)) OVER `w` + ) AS `Occurence` +FROM + @source +WINDOW `w` AS ( + PARTITION BY `Permalink` + ORDER BY `Date` +) + diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-7767_key_filter_with_view.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-7767_key_filter_with_view.cfg new file mode 100644 index 0000000000..7ed2bf2270 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-7767_key_filter_with_view.cfg @@ -0,0 +1,5 @@ +in ViewSource input4.txt +in Input1 sorted.txt +in Input2 input_view.txt +udf python3_udf +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-7767_key_filter_with_view.sql b/yql/essentials/tests/sql/suites/optimizers/yql-7767_key_filter_with_view.sql new file mode 100644 index 0000000000..56c915118a --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-7767_key_filter_with_view.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +select + key, + value || "_y" ?? "" as value +from range("", "Input1", "Input2") +where key > "010" +order by key, value; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-8041-fuse_with_desc_map.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-8041-fuse_with_desc_map.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-8041-fuse_with_desc_map.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-8041-fuse_with_desc_map.sql b/yql/essentials/tests/sql/suites/optimizers/yql-8041-fuse_with_desc_map.sql new file mode 100644 index 0000000000..2231c46900 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-8041-fuse_with_desc_map.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +USE plato; + +$i = ( + SELECT + cast(key as Double) as key, + value + FROM Input + WHERE key < "100" + ORDER BY key DESC + LIMIT 1000 +); + +select distinct key +from $i +where value != "" +order by key; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-8223_direct_row_and_skipnullmembers.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-8223_direct_row_and_skipnullmembers.cfg new file mode 100644 index 0000000000..676c920ddf --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-8223_direct_row_and_skipnullmembers.cfg @@ -0,0 +1,2 @@ +in Input opt_key.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-8223_direct_row_and_skipnullmembers.sql b/yql/essentials/tests/sql/suites/optimizers/yql-8223_direct_row_and_skipnullmembers.sql new file mode 100644 index 0000000000..aaf5c9187e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-8223_direct_row_and_skipnullmembers.sql @@ -0,0 +1,25 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +pragma AnsiInForEmptyOrNullableItemsCollections; + +$x = ( + SELECT + TableName() AS path, + key, subkey, value + FROM CONCAT(Input, Input) +); +$y = ( + SELECT + t.path AS path, + t.key as key, + info.value as value + FROM $x AS t + INNER JOIN ( + SELECT key, subkey, value FROM Input + WHERE key != "" + ) AS info + USING (key) + WHERE t.key in ("023", "150") +); +select distinct path from $y; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-8953_logical_fuse_with_table_props.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-8953_logical_fuse_with_table_props.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-8953_logical_fuse_with_table_props.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-8953_logical_fuse_with_table_props.sql b/yql/essentials/tests/sql/suites/optimizers/yql-8953_logical_fuse_with_table_props.sql new file mode 100644 index 0000000000..610f2ef729 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-8953_logical_fuse_with_table_props.sql @@ -0,0 +1,10 @@ +use plato; + +select key, subkey, TableName() as name from Input +where value == 'q'; + +select key, count(*) as subkeys from ( + select distinct key, subkey from Input + where value == 'q' +) +group by key;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-9297_publish_ytcopy.cfg b/yql/essentials/tests/sql/suites/optimizers/yql-9297_publish_ytcopy.cfg new file mode 100644 index 0000000000..551221cf56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-9297_publish_ytcopy.cfg @@ -0,0 +1,3 @@ +in Input input3.txt +out Output output.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql-9297_publish_ytcopy.sql b/yql/essentials/tests/sql/suites/optimizers/yql-9297_publish_ytcopy.sql new file mode 100644 index 0000000000..d0a183b3ff --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql-9297_publish_ytcopy.sql @@ -0,0 +1,22 @@ +/* postgres can not */ +/* multirun can not */ +/* kikimr can not - table truncate */ +USE plato; + +INSERT INTO @a +SELECT * +FROM Input +WHERE key < "100" +ORDER BY key DESC; + +COMMIT; + +INSERT INTO Output +SELECT * +FROM @a +ORDER BY key DESC; + +COMMIT; + +SELECT * +FROM Output;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/optimizers/yql_5830_fuse_outer_with_extra_deps.cfg b/yql/essentials/tests/sql/suites/optimizers/yql_5830_fuse_outer_with_extra_deps.cfg new file mode 100644 index 0000000000..0474ee88ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql_5830_fuse_outer_with_extra_deps.cfg @@ -0,0 +1,2 @@ +in Input input3.txt +res result.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql_5830_fuse_outer_with_extra_deps.sql b/yql/essentials/tests/sql/suites/optimizers/yql_5830_fuse_outer_with_extra_deps.sql new file mode 100644 index 0000000000..4ab7aaf95e --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql_5830_fuse_outer_with_extra_deps.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +USE plato; + +$data = (select max_by(key, subkey) +from Input where value > "a"); + +select + a.key, + $data as max_key, + b.value +from Input as a +left join (select * from Input where key > "050") as b +on a.key = b.key +order by a.key; diff --git a/yql/essentials/tests/sql/suites/optimizers/yql_6179_merge_chunks_of_outputs.cfg b/yql/essentials/tests/sql/suites/optimizers/yql_6179_merge_chunks_of_outputs.cfg new file mode 100644 index 0000000000..1dc9a1e3a7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql_6179_merge_chunks_of_outputs.cfg @@ -0,0 +1 @@ +in Input input0.txt diff --git a/yql/essentials/tests/sql/suites/optimizers/yql_6179_merge_chunks_of_outputs.sql b/yql/essentials/tests/sql/suites/optimizers/yql_6179_merge_chunks_of_outputs.sql new file mode 100644 index 0000000000..fcf21d7b36 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yql_6179_merge_chunks_of_outputs.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ +/* kikimr can not - yt pragma */ + +PRAGMA yt.MinPublishedAvgChunkSize="0"; +PRAGMA yt.MinTempAvgChunkSize="0"; + +USE plato; + +$i = (select subkey as s from Input where key = "112" limit 1); +$j = (select subkey as s from Input where key = "113" limit 1); + +select * from Input where cast(TableRecordIndex() as String) == $i or + cast(TableRecordIndex() as String) == $j; + diff --git a/yql/essentials/tests/sql/suites/optimizers/yson_dup_serialize.cfg b/yql/essentials/tests/sql/suites/optimizers/yson_dup_serialize.cfg new file mode 100644 index 0000000000..40b3e4c9a1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yson_dup_serialize.cfg @@ -0,0 +1,3 @@ +res result.txt +udf yson_udf + diff --git a/yql/essentials/tests/sql/suites/optimizers/yson_dup_serialize.sql b/yql/essentials/tests/sql/suites/optimizers/yson_dup_serialize.sql new file mode 100644 index 0000000000..ead013f3b6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yson_dup_serialize.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +/* syntax version 1 */ +$l = ($x)->( ListMap($x, Yson::Serialize) ); +$d = ($x)->( ToDict(ListMap(DictItems($x),($i)->(($i.0,Yson::Serialize($i.1)))))); + +select $l($l(Yson::ConvertToList(Yson("[1;2;3]")))); +select ListSort(DictItems($d($d(Yson::ConvertToDict(Yson("{a=1;b=2}"))))), ($x)->($x.0)); diff --git a/yql/essentials/tests/sql/suites/optimizers/yt_shuffle_by_keys.cfg b/yql/essentials/tests/sql/suites/optimizers/yt_shuffle_by_keys.cfg new file mode 100644 index 0000000000..4ba549ce28 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yt_shuffle_by_keys.cfg @@ -0,0 +1,4 @@ +in Input input0.txt +providers yt + + diff --git a/yql/essentials/tests/sql/suites/optimizers/yt_shuffle_by_keys.sql b/yql/essentials/tests/sql/suites/optimizers/yt_shuffle_by_keys.sql new file mode 100644 index 0000000000..b4bf37fa34 --- /dev/null +++ b/yql/essentials/tests/sql/suites/optimizers/yt_shuffle_by_keys.sql @@ -0,0 +1,23 @@ +--Test, that YT optimizer can rewrite ShuffleByKeys with PartitionsByKes +USE plato; + +$input = PROCESS Input; + +SELECT YQL::ShuffleByKeys( + $input, + ($_)->("dsdsa"), + ($_)->([1]) -- list + ); + +SELECT YQL::ShuffleByKeys( + $input, + ($_)->(12), + ($_)->(Just(2)) -- optional + ); + +SELECT YQL::ShuffleByKeys( + $input, + ($_)->(true), + ($_)->(YQL::ToStream([3])) -- stream + ); + |