Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

1 files changed, 68 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/csee/yql-7237.sql b/yql/essentials/tests/sql/suites/csee/yql-7237.sql
new file mode 100644
index 0000000000..52a0a68ea7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/csee/yql-7237.sql
@@ -0,0 +1,68 @@
+/* syntax version 1 */
+USE plato;
+
+$push_final_data = AsList(
+    AsStruct("manufacturer" AS manufacturer, "state" AS state));
+
+INSERT INTO @push_final
+SELECT * FROM AS_TABLE($push_final_data);
+COMMIT;
+
+$manufacturer_name_fix = ($manufacturer) -> {
+    $lowered_manufacturer = CAST(Unicode::ToLower(CAST(String::Strip($manufacturer) AS Utf8)) AS String);
+    $in = AsList(
+        "oysters", -- bullshit in naming
+        "qumo", -- bullshit in naming
+        "texet", -- bullshit in naming
+        "alcatel", -- bullshit in naming
+        "dexp", -- bullshit in naming
+        "haier", -- bullshit in naming
+        "dexp", -- bullshit in naming
+        "asus", -- ASUSTek Computer Inc & ASUS both usable
+        "yota", -- Yota Devices & Yota Devices Limited ...
+        "ark" -- "ark" & "ark electronic technology" & "ark_electronic_technology"
+    );
+    $lambda = ($substring) -> {
+        RETURN FIND($lowered_manufacturer, $substring) IS NULL;
+    };
+    $list = ListSkipWhile($in, $lambda);
+    RETURN IF(ListHasItems($list), $list[0], $lowered_manufacturer);
+};
+
+$manufacturers_whitelist = (
+    SELECT
+        man as manufacturer
+    FROM
+    (
+        SELECT
+            man, COUNT(*) as cnt
+        FROM
+            @push_final
+        GROUP BY
+            $manufacturer_name_fix(manufacturer) as man
+    )
+    WHERE
+        cnt > 1000
+);
+
+$push_final_preprocessing = (
+    SELECT
+        $manufacturer_name_fix(manufacturer) as manufacturer,
+        state
+    FROM
+        @push_final
+);
+
+SELECT
+    COALESCE(fixed_manufacturer, "other") as manufacturer,
+    L.* WITHOUT L.manufacturer
+FROM
+    $push_final_preprocessing as L
+    LEFT JOIN
+    (
+        SELECT
+            manufacturer as fixed_manufacturer
+        FROM
+            $manufacturers_whitelist
+    ) as R
+    ON(L.manufacturer = R.fixed_manufacturer);