Intermediate changes

commit_hash:a876b0522ba7aa9c9d4af22e749605f0a57343c2

13 files changed, 338 insertions, 86 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;
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q47.cfg b/yt/yql/tests/sql/suites/yql-tpcds/q47.cfg
deleted file mode 100644
index 8a6e28fba2a..00000000000
--- a/yt/yql/tests/sql/suites/yql-tpcds/q47.cfg
+++ /dev/null
@@ -1,5 +0,0 @@
-xfail
-in item item.txt
-in store_sales store_sales.txt
-in date_dim date_dim.txt
-in store store.txt
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q47.yql b/yt/yql/tests/sql/suites/yql-tpcds/q47.yql
index 212ca1bd57c..fa9654b11e2 100644
--- a/yt/yql/tests/sql/suites/yql-tpcds/q47.yql
+++ b/yt/yql/tests/sql/suites/yql-tpcds/q47.yql
@@ -1,35 +1,34 @@
 /* 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,
+        item.i_category AS i_category,
+        item.i_brand AS i_brand,
+        store.s_store_name AS s_store_name,
+        store.s_company_name AS s_company_name,
+        date_dim.d_year AS d_year,
+        date_dim.d_moy AS 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
+                item.i_category,
+                item.i_brand,
+                store.s_store_name,
+                store.s_company_name,
+                date_dim.d_year
         ) AS avg_monthly_sales,
         Rank() OVER (
             PARTITION BY
-                i_category,
-                i_brand,
-                s_store_name,
-                s_company_name
+                item.i_category,
+                item.i_brand,
+                store.s_store_name,
+                store.s_company_name
             ORDER BY
-                d_year,
-                d_moy
+                date_dim.d_year,
+                date_dim.d_moy
         ) AS rn
     FROM
         plato.item
@@ -49,22 +48,22 @@ $v1 = (
             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
+        item.i_category,
+        item.i_brand,
+        store.s_store_name,
+        store.s_company_name,
+        date_dim.d_year,
+        date_dim.d_moy
 );
 
 $v2 = (
     SELECT
-        v1.i_category,
-        v1.i_brand,
-        v1.d_year,
-        v1.d_moy,
-        v1.avg_monthly_sales,
-        v1.sum_sales,
+        v1.i_category AS i_category,
+        v1.i_brand AS i_brand,
+        v1.d_year AS d_year,
+        v1.d_moy AS d_moy,
+        v1.avg_monthly_sales AS avg_monthly_sales,
+        v1.sum_sales AS sum_sales,
         v1_lag.sum_sales AS psum,
         v1_lead.sum_sales AS nsum
     FROM
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q57.cfg b/yt/yql/tests/sql/suites/yql-tpcds/q57.cfg
deleted file mode 100644
index 2af62aa4495..00000000000
--- a/yt/yql/tests/sql/suites/yql-tpcds/q57.cfg
+++ /dev/null
@@ -1,5 +0,0 @@
-xfail
-in item item.txt
-in catalog_sales catalog_sales.txt
-in date_dim date_dim.txt
-in call_center call_center.txt
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q57.yql b/yt/yql/tests/sql/suites/yql-tpcds/q57.yql
index 7132d2a9a85..3458096540e 100644
--- a/yt/yql/tests/sql/suites/yql-tpcds/q57.yql
+++ b/yt/yql/tests/sql/suites/yql-tpcds/q57.yql
@@ -1,32 +1,31 @@
 /* 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,
-        cc_name,
-        d_year,
-        d_moy,
+        item.i_category AS i_category,
+        item.i_brand AS i_brand,
+        call_center.cc_name AS cc_name,
+        date_dim.d_year AS d_year,
+        date_dim.d_moy AS d_moy,
         Sum(cs_sales_price) AS sum_sales,
         Avg(Sum(cs_sales_price)) OVER (
             PARTITION BY
-                i_category,
-                i_brand,
-                cc_name,
-                d_year
+                item.i_category,
+                item.i_brand,
+                call_center.cc_name,
+                date_dim.d_year
         ) AS avg_monthly_sales,
         Rank() OVER (
             PARTITION BY
-                i_category,
-                i_brand,
-                cc_name
+                item.i_category,
+                item.i_brand,
+                call_center.cc_name
             ORDER BY
-                d_year,
-                d_moy
+                date_dim.d_year,
+                date_dim.d_moy
         ) AS rn
     FROM
         plato.item
@@ -46,20 +45,20 @@ $v1 = (
             OR (d_year == 2000 + 1 AND d_moy == 1)
         )
     GROUP BY
-        i_category,
-        i_brand,
-        cc_name,
-        d_year,
-        d_moy
+        item.i_category,
+        item.i_brand,
+        call_center.cc_name,
+        date_dim.d_year,
+        date_dim.d_moy
 );
 
 $v2 = (
     SELECT
-        v1.cc_name,
-        v1.d_year,
-        v1.d_moy,
-        v1.avg_monthly_sales,
-        v1.sum_sales,
+        v1.cc_name AS cc_name,
+        v1.d_year AS d_year,
+        v1.d_moy AS d_moy,
+        v1.avg_monthly_sales AS avg_monthly_sales,
+        v1.sum_sales AS sum_sales,
         v1_lag.sum_sales AS psum,
         v1_lead.sum_sales AS nsum
     FROM
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q79.cfg b/yt/yql/tests/sql/suites/yql-tpcds/q79.cfg
deleted file mode 100644
index c7e7731b641..00000000000
--- a/yt/yql/tests/sql/suites/yql-tpcds/q79.cfg
+++ /dev/null
@@ -1,6 +0,0 @@
-xfail
-in store_sales store_sales.txt
-in date_dim date_dim.txt
-in store store.txt
-in household_demographics household_demographics.txt
-in customer customer.txt
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q79.yql b/yt/yql/tests/sql/suites/yql-tpcds/q79.yql
index 7a0fe0bff31..49e0cc1c1cc 100644
--- a/yt/yql/tests/sql/suites/yql-tpcds/q79.yql
+++ b/yt/yql/tests/sql/suites/yql-tpcds/q79.yql
@@ -1,6 +1,5 @@
 /* dqfile can not */
 /* hybridfile can not - missing langver support */
-/* custom error: No such column: s_city */
 PRAGMA YqlSelect = 'force';
 PRAGMA AnsiImplicitCrossJoin;
 
@@ -13,9 +12,9 @@ SELECT
     profit
 FROM (
     SELECT
-        ss_ticket_number,
-        ss_customer_sk,
-        store.s_city,
+        store_sales.ss_ticket_number AS ss_ticket_number,
+        store_sales.ss_customer_sk AS ss_customer_sk,
+        store.s_city AS s_city,
         Sum(ss_coupon_amt) AS amt,
         Sum(ss_net_profit) AS profit
     FROM
@@ -35,9 +34,9 @@ FROM (
         AND date_dim.d_year IN (1998, 1998 + 1, 1998 + 2)
         AND store.s_number_employees BETWEEN 200 AND 295
     GROUP BY
-        ss_ticket_number,
-        ss_customer_sk,
-        ss_addr_sk,
+        store_sales.ss_ticket_number,
+        store_sales.ss_customer_sk,
+        store_sales.ss_addr_sk,
         store.s_city
 ) AS ms
 ,
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q95.cfg b/yt/yql/tests/sql/suites/yql-tpcds/q95.cfg
deleted file mode 100644
index f603b9d9f48..00000000000
--- a/yt/yql/tests/sql/suites/yql-tpcds/q95.cfg
+++ /dev/null
@@ -1,6 +0,0 @@
-xfail
-in web_sales web_sales.txt
-in date_dim date_dim.txt
-in customer_address customer_address.txt
-in web_site web_site.txt
-in web_returns web_returns.txt
diff --git a/yt/yql/tests/sql/suites/yql-tpcds/q95.yql b/yt/yql/tests/sql/suites/yql-tpcds/q95.yql
index 3d9b158b3b9..f866dbd9bdb 100644
--- a/yt/yql/tests/sql/suites/yql-tpcds/q95.yql
+++ b/yt/yql/tests/sql/suites/yql-tpcds/q95.yql
@@ -1,12 +1,11 @@
 /* dqfile can not */
 /* hybridfile can not - missing langver support */
-/* custom error: No such column: ws_order_number */
 PRAGMA YqlSelect = 'force';
 PRAGMA AnsiImplicitCrossJoin;
 
 $ws_wh = (
     SELECT
-        ws1.ws_order_number,
+        ws1.ws_order_number AS ws_order_number,
         ws1.ws_warehouse_sk AS wh1,
         ws2.ws_warehouse_sk AS wh2
     FROM