Intermediate changes

commit_hash:a876b0522ba7aa9c9d4af22e749605f0a57343c2

5 files changed, 278 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 417eccd042d..24f0b7d251a 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -10002,6 +10002,13 @@
             "uri": "https://{canondata_backend}/1881367/3620f00662dba64d30bf3c93837b76b97c3a5f1f/resource.tar.gz#test_sql2yql.test_select_yql-window_sum_partition_by_order_by_/sql.yql"
         }
     ],
+    "test_sql2yql.test[select_yql_tpcds-q47]": [
+        {
+            "checksum": "7ffae3ff21ff930453ad7a1e99d2ebbb",
+            "size": 8128,
+            "uri": "https://{canondata_backend}/1936997/509acb0996960a8b27e0aeee1f12ae7f4bb1f80d/resource.tar.gz#test_sql2yql.test_select_yql_tpcds-q47_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[select_yql_tpch-q04]": [
         {
             "checksum": "b3aee1e97df6ca03d303cc2eb1965f8b",
@@ -17427,6 +17434,11 @@
             "uri": "file://test_sql_format.test_select_yql-window_sum_partition_by_order_by_/formatted.sql"
         }
     ],
+    "test_sql_format.test[select_yql_tpcds-q47]": [
+        {
+            "uri": "file://test_sql_format.test_select_yql_tpcds-q47_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[select_yql_tpch-q04]": [
         {
             "uri": "file://test_sql_format.test_select_yql_tpch-q04_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql_tpcds-q47_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql_tpcds-q47_/formatted.sql
new file mode 100644
index 00000000000..ed677271c70
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql_tpcds-q47_/formatted.sql
@@ -0,0 +1,132 @@
+/* dqfile can not */
+/* hybridfile can not - missing langver support */
+/* custom error: No such column: avg_monthly_sales */
+PRAGMA YqlSelect = 'force';
+PRAGMA AnsiImplicitCrossJoin;
+
+$v1 = (
+    SELECT
+        i_category,
+        i_brand,
+        s_store_name,
+        s_company_name,
+        d_year,
+        d_moy,
+        Sum(ss_sales_price) AS sum_sales,
+        Avg(Sum(ss_sales_price)) OVER (
+            PARTITION BY
+                i_category,
+                i_brand,
+                s_store_name,
+                s_company_name,
+                d_year
+        ) AS avg_monthly_sales,
+        Rank() OVER (
+            PARTITION BY
+                i_category,
+                i_brand,
+                s_store_name,
+                s_company_name
+            ORDER BY
+                d_year,
+                d_moy
+        ) AS rn
+    FROM (
+        VALUES
+            (1, 'cat1', 'brand1')
+    ) AS item (
+        i_item_sk,
+        i_category,
+        i_brand
+    )
+    , (
+        VALUES
+            (1, 10, 1, 100),
+            (1, 11, 1, 200),
+            (1, 12, 1, 300)
+    ) AS store_sales (
+        ss_item_sk,
+        ss_sold_date_sk,
+        ss_store_sk,
+        ss_sales_price
+    )
+    , (
+        VALUES
+            (10, 1999, 12),
+            (11, 2000, 1),
+            (12, 2001, 1)
+    ) AS date_dim (
+        d_date_sk,
+        d_year,
+        d_moy
+    )
+    , (
+        VALUES
+            (1, 'storeA', 'companyA')
+    ) AS store (
+        s_store_sk,
+        s_store_name,
+        s_company_name
+    )
+    WHERE
+        ss_item_sk == i_item_sk
+        AND ss_sold_date_sk == d_date_sk
+        AND ss_store_sk == s_store_sk
+        AND (
+            d_year == 2000
+            OR (d_year == 2000 - 1 AND d_moy == 12)
+            OR (d_year == 2000 + 1 AND d_moy == 1)
+        )
+    GROUP BY
+        i_category,
+        i_brand,
+        s_store_name,
+        s_company_name,
+        d_year,
+        d_moy
+);
+
+$v2 = (
+    SELECT
+        v1.i_category,
+        v1.i_brand,
+        v1.d_year,
+        v1.d_moy,
+        v1.avg_monthly_sales,
+        v1.sum_sales,
+        v1_lag.sum_sales AS psum,
+        v1_lead.sum_sales AS nsum
+    FROM
+        $v1 AS v1
+    ,
+        $v1 AS v1_lag
+    ,
+        $v1 AS v1_lead
+    WHERE
+        v1.i_category == v1_lag.i_category
+        AND v1.i_category == v1_lead.i_category
+        AND v1.i_brand == v1_lag.i_brand
+        AND v1.i_brand == v1_lead.i_brand
+        AND v1.s_store_name == v1_lag.s_store_name
+        AND v1.s_store_name == v1_lead.s_store_name
+        AND v1.s_company_name == v1_lag.s_company_name
+        AND v1.s_company_name == v1_lead.s_company_name
+        AND v1.rn == v1_lag.rn + 1
+        AND v1.rn == v1_lead.rn - 1
+);
+
+SELECT
+    *
+FROM
+    $v2
+WHERE
+    d_year == 2000
+    AND avg_monthly_sales > 0
+    AND CASE
+        WHEN avg_monthly_sales > 0 THEN Abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
+        ELSE NULL
+    END > 0.1
+ORDER BY
+    sum_sales - avg_monthly_sales,
+    nsum
+LIMIT 100;
diff --git a/yql/essentials/tests/sql/suites/select_yql_tpcds/default.cfg b/yql/essentials/tests/sql/suites/select_yql_tpcds/default.cfg
new file mode 100644
index 00000000000..e6f92ba64fc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/select_yql_tpcds/default.cfg
@@ -0,0 +1 @@
+langver YqlSelect
diff --git a/yql/essentials/tests/sql/suites/select_yql_tpcds/q47.cfg b/yql/essentials/tests/sql/suites/select_yql_tpcds/q47.cfg
new file mode 100644
index 00000000000..5dae597903c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/select_yql_tpcds/q47.cfg
@@ -0,0 +1 @@
+xfail
diff --git a/yql/essentials/tests/sql/suites/select_yql_tpcds/q47.yql b/yql/essentials/tests/sql/suites/select_yql_tpcds/q47.yql
new file mode 100644
index 00000000000..ed677271c70
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/select_yql_tpcds/q47.yql
@@ -0,0 +1,132 @@
+/* dqfile can not */
+/* hybridfile can not - missing langver support */
+/* custom error: No such column: avg_monthly_sales */
+PRAGMA YqlSelect = 'force';
+PRAGMA AnsiImplicitCrossJoin;
+
+$v1 = (
+    SELECT
+        i_category,
+        i_brand,
+        s_store_name,
+        s_company_name,
+        d_year,
+        d_moy,
+        Sum(ss_sales_price) AS sum_sales,
+        Avg(Sum(ss_sales_price)) OVER (
+            PARTITION BY
+                i_category,
+                i_brand,
+                s_store_name,
+                s_company_name,
+                d_year
+        ) AS avg_monthly_sales,
+        Rank() OVER (
+            PARTITION BY
+                i_category,
+                i_brand,
+                s_store_name,
+                s_company_name
+            ORDER BY
+                d_year,
+                d_moy
+        ) AS rn
+    FROM (
+        VALUES
+            (1, 'cat1', 'brand1')
+    ) AS item (
+        i_item_sk,
+        i_category,
+        i_brand
+    )
+    , (
+        VALUES
+            (1, 10, 1, 100),
+            (1, 11, 1, 200),
+            (1, 12, 1, 300)
+    ) AS store_sales (
+        ss_item_sk,
+        ss_sold_date_sk,
+        ss_store_sk,
+        ss_sales_price
+    )
+    , (
+        VALUES
+            (10, 1999, 12),
+            (11, 2000, 1),
+            (12, 2001, 1)
+    ) AS date_dim (
+        d_date_sk,
+        d_year,
+        d_moy
+    )
+    , (
+        VALUES
+            (1, 'storeA', 'companyA')
+    ) AS store (
+        s_store_sk,
+        s_store_name,
+        s_company_name
+    )
+    WHERE
+        ss_item_sk == i_item_sk
+        AND ss_sold_date_sk == d_date_sk
+        AND ss_store_sk == s_store_sk
+        AND (
+            d_year == 2000
+            OR (d_year == 2000 - 1 AND d_moy == 12)
+            OR (d_year == 2000 + 1 AND d_moy == 1)
+        )
+    GROUP BY
+        i_category,
+        i_brand,
+        s_store_name,
+        s_company_name,
+        d_year,
+        d_moy
+);
+
+$v2 = (
+    SELECT
+        v1.i_category,
+        v1.i_brand,
+        v1.d_year,
+        v1.d_moy,
+        v1.avg_monthly_sales,
+        v1.sum_sales,
+        v1_lag.sum_sales AS psum,
+        v1_lead.sum_sales AS nsum
+    FROM
+        $v1 AS v1
+    ,
+        $v1 AS v1_lag
+    ,
+        $v1 AS v1_lead
+    WHERE
+        v1.i_category == v1_lag.i_category
+        AND v1.i_category == v1_lead.i_category
+        AND v1.i_brand == v1_lag.i_brand
+        AND v1.i_brand == v1_lead.i_brand
+        AND v1.s_store_name == v1_lag.s_store_name
+        AND v1.s_store_name == v1_lead.s_store_name
+        AND v1.s_company_name == v1_lag.s_company_name
+        AND v1.s_company_name == v1_lead.s_company_name
+        AND v1.rn == v1_lag.rn + 1
+        AND v1.rn == v1_lead.rn - 1
+);
+
+SELECT
+    *
+FROM
+    $v2
+WHERE
+    d_year == 2000
+    AND avg_monthly_sales > 0
+    AND CASE
+        WHEN avg_monthly_sales > 0 THEN Abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
+        ELSE NULL
+    END > 0.1
+ORDER BY
+    sum_sales - avg_monthly_sales,
+    nsum
+LIMIT 100;