You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "Omega359 (via GitHub)" <gi...@apache.org> on 2026/05/23 16:25:26 UTC

[PR] Cleaned up TPC-DS queries [datafusion]

Omega359 opened a new pull request, #22474:
URL: https://github.com/apache/datafusion/pull/22474

   ## Which issue does this PR close?
   
   - Closes #.
   
   ## Rationale for this change
   
   Cleaned up formatting, updated queries so that they all return at least 1 result. Fixed a few queries that did not match the shape/or fields from the tpc-ds 3.1.0 spec (query 44, query 49)
   
   ## What changes are included in this PR?
   
   sql file updates only.
   
   ## Are these changes tested?
   
   Yes. `cd benchmarks;./bench.sh run tpcds`
   
   ## Are there any user-facing changes?
   
   Query timings may change because previous queries that returned no results may have had a different query execution path.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 closed pull request #22474: Cleanup TPC-DS queries
URL: https://github.com/apache/datafusion/pull/22474


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313545995


##########
datafusion/core/tests/tpc-ds/78.sql:
##########
@@ -1,59 +1,90 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ws as
-  (select d_year AS ws_sold_year, ws_item_sk,
-    ws_bill_customer_sk ws_customer_sk,
-    sum(ws_quantity) ws_qty,
-    sum(ws_wholesale_cost) ws_wc,
-    sum(ws_sales_price) ws_sp
-   from web_sales
-   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
-   join date_dim on ws_sold_date_sk = d_date_sk
-   where wr_order_number is null
-   group by d_year, ws_item_sk, ws_bill_customer_sk
-   ),
-cs as
-  (select d_year AS cs_sold_year, cs_item_sk,
-    cs_bill_customer_sk cs_customer_sk,
-    sum(cs_quantity) cs_qty,
-    sum(cs_wholesale_cost) cs_wc,
-    sum(cs_sales_price) cs_sp
-   from catalog_sales
-   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
-   join date_dim on cs_sold_date_sk = d_date_sk
-   where cr_order_number is null
-   group by d_year, cs_item_sk, cs_bill_customer_sk
-   ),
-ss as
-  (select d_year AS ss_sold_year, ss_item_sk,
+WITH
+    ws AS (
+        SELECT
+            d_year AS ws_sold_year,
+            ws_item_sk,
+            ws_bill_customer_sk ws_customer_sk,
+            sum(ws_quantity) ws_qty,
+            sum(ws_wholesale_cost) ws_wc,
+            sum(ws_sales_price) ws_sp
+        FROM web_sales
+        LEFT JOIN
+            web_returns ON wr_order_number = ws_order_number AND ws_item_sk = wr_item_sk
+        JOIN date_dim ON ws_sold_date_sk = d_date_sk
+        WHERE wr_order_number IS NULL
+        GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
+    ),
+    cs AS (
+        SELECT
+            d_year AS cs_sold_year,
+            cs_item_sk,
+            cs_bill_customer_sk cs_customer_sk,
+            sum(cs_quantity) cs_qty,
+            sum(cs_wholesale_cost) cs_wc,
+            sum(cs_sales_price) cs_sp
+        FROM catalog_sales
+        LEFT JOIN
+            catalog_returns
+            ON cr_order_number = cs_order_number
+            AND cs_item_sk = cr_item_sk
+        JOIN date_dim ON cs_sold_date_sk = d_date_sk
+        WHERE cr_order_number IS NULL
+        GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
+    ),
+    ss AS (
+        SELECT
+            d_year AS ss_sold_year,
+            ss_item_sk,
+            ss_customer_sk,
+            sum(ss_quantity) ss_qty,
+            sum(ss_wholesale_cost) ss_wc,
+            sum(ss_sales_price) ss_sp
+        FROM store_sales
+        LEFT JOIN
+            store_returns
+            ON sr_ticket_number = ss_ticket_number
+            AND ss_item_sk = sr_item_sk
+        JOIN date_dim ON ss_sold_date_sk = d_date_sk
+        WHERE sr_ticket_number IS NULL
+        GROUP BY d_year, ss_item_sk, ss_customer_sk
+    )
+SELECT
+    ss_sold_year,
+    ss_item_sk,
     ss_customer_sk,
-    sum(ss_quantity) ss_qty,
-    sum(ss_wholesale_cost) ss_wc,
-    sum(ss_sales_price) ss_sp
-   from store_sales
-   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
-   join date_dim on ss_sold_date_sk = d_date_sk
-   where sr_ticket_number is null
-   group by d_year, ss_item_sk, ss_customer_sk
-   )
- select 
-ss_sold_year, ss_item_sk, ss_customer_sk,
-round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
-ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
-coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
-coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
-coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
-from ss
-left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
-left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
-where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
-order by 
-  ss_sold_year, ss_item_sk, ss_customer_sk,
-  ss_qty desc, ss_wc desc, ss_sp desc,
-  other_chan_qty,
-  other_chan_wholesale_cost,
-  other_chan_sales_price,
-  ratio
-limit 100;
-
-
+    round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
+    ss_qty store_qty,
+    ss_wc store_wholesale_cost,
+    ss_sp store_sales_price,
+    coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
+    coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
+    coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
+FROM ss
+LEFT JOIN
+    ws
+    ON (
+        ws_sold_year = ss_sold_year
+        AND ws_item_sk = ss_item_sk
+        AND ws_customer_sk = ss_customer_sk
+    )
+LEFT JOIN
+    cs
+    ON (
+        cs_sold_year = ss_sold_year
+        AND cs_item_sk = ss_item_sk
+        AND cs_customer_sk = ss_customer_sk
+    )
+WHERE (coalesce(ws_qty, 0) > 0 OR coalesce(cs_qty, 0) > 0) AND ss_sold_year = 2000
+ORDER BY
+    ss_sold_year,
+    ss_item_sk,
+    ss_customer_sk,
+    ss_qty desc,
+    ss_wc desc,
+    ss_sp desc,
+    other_chan_qty,
+    other_chan_wholesale_cost,
+    other_chan_sales_price,
+    round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2)

Review Comment:
   I think this should actually be just 'ratio' 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4564206848

   @alamb FWIW I had Claude double check these queries against the 3.1 spec and it only found the few minor inconsistencies that I pointed out. (4.0 has the same queries as 3.1.)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313512795


##########
datafusion/core/tests/tpc-ds/5.sql:
##########
@@ -1,129 +1,146 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ssr as
- (select s_store_id,
-        sum(sales_price) as sales,
-        sum(profit) as profit,
-        sum(return_amt) as returns,
-        sum(net_loss) as profit_loss
- from
-  ( select  ss_store_sk as store_sk,
-            ss_sold_date_sk  as date_sk,
-            ss_ext_sales_price as sales_price,
-            ss_net_profit as profit,
-            cast(0 as decimal(7,2)) as return_amt,
-            cast(0 as decimal(7,2)) as net_loss
-    from store_sales
-    union all
-    select sr_store_sk as store_sk,
-           sr_returned_date_sk as date_sk,
-           cast(0 as decimal(7,2)) as sales_price,
-           cast(0 as decimal(7,2)) as profit,
-           sr_return_amt as return_amt,
-           sr_net_loss as net_loss
-    from store_returns
-   ) salesreturns,
-     date_dim,
-     store
- where date_sk = d_date_sk
-       and d_date between cast('1998-08-04' as date) 
-                  and (cast('1998-08-04' as date) + INTERVAL '14 days')
-       and store_sk = s_store_sk
- group by s_store_id)
- ,
- csr as
- (select cp_catalog_page_id,
-        sum(sales_price) as sales,
-        sum(profit) as profit,
-        sum(return_amt) as returns,
-        sum(net_loss) as profit_loss
- from
-  ( select  cs_catalog_page_sk as page_sk,
-            cs_sold_date_sk  as date_sk,
-            cs_ext_sales_price as sales_price,
-            cs_net_profit as profit,
-            cast(0 as decimal(7,2)) as return_amt,
-            cast(0 as decimal(7,2)) as net_loss
-    from catalog_sales
-    union all
-    select cr_catalog_page_sk as page_sk,
-           cr_returned_date_sk as date_sk,
-           cast(0 as decimal(7,2)) as sales_price,
-           cast(0 as decimal(7,2)) as profit,
-           cr_return_amount as return_amt,
-           cr_net_loss as net_loss
-    from catalog_returns
-   ) salesreturns,
-     date_dim,
-     catalog_page
- where date_sk = d_date_sk
-       and d_date between cast('1998-08-04' as date)
-                  and (cast('1998-08-04' as date) + INTERVAL '14 days')
-       and page_sk = cp_catalog_page_sk
- group by cp_catalog_page_id)
- ,
- wsr as
- (select web_site_id,
-        sum(sales_price) as sales,
-        sum(profit) as profit,
-        sum(return_amt) as returns,
-        sum(net_loss) as profit_loss
- from
-  ( select  ws_web_site_sk as wsr_web_site_sk,
-            ws_sold_date_sk  as date_sk,
-            ws_ext_sales_price as sales_price,
-            ws_net_profit as profit,
-            cast(0 as decimal(7,2)) as return_amt,
-            cast(0 as decimal(7,2)) as net_loss
-    from web_sales
-    union all
-    select ws_web_site_sk as wsr_web_site_sk,
-           wr_returned_date_sk as date_sk,
-           cast(0 as decimal(7,2)) as sales_price,
-           cast(0 as decimal(7,2)) as profit,
-           wr_return_amt as return_amt,
-           wr_net_loss as net_loss
-    from web_returns left outer join web_sales on
-         ( wr_item_sk = ws_item_sk
-           and wr_order_number = ws_order_number)
-   ) salesreturns,
-     date_dim,
-     web_site
- where date_sk = d_date_sk
-       and d_date between cast('1998-08-04' as date)
-                  and (cast('1998-08-04' as date) + INTERVAL '14 days')
-       and wsr_web_site_sk = web_site_sk
- group by web_site_id)
-  select  channel
-        , id
-        , sum(sales) as sales
-        , sum(returns) as returns
-        , sum(profit) as profit
- from 
- (select 'store channel' as channel
-        , 'store' || s_store_id as id
-        , sales
-        , returns
-        , (profit - profit_loss) as profit
- from   ssr
- union all
- select 'catalog channel' as channel
-        , 'catalog_page' || cp_catalog_page_id as id
-        , sales
-        , returns
-        , (profit - profit_loss) as profit
- from  csr
- union all
- select 'web channel' as channel
-        , 'web_site' || web_site_id as id
-        , sales
-        , returns
-        , (profit - profit_loss) as profit
- from   wsr
- ) x
- group by rollup (channel, id)
- order by channel
-         ,id
- limit 100;
-
-
+WITH
+    ssr AS (
+        SELECT
+            s_store_id,
+            sum(sales_price) AS sales,
+            sum(profit) AS profit,
+            sum(return_amt) AS returns,
+            sum(net_loss) AS profit_loss
+        FROM
+            (
+                SELECT
+                    ss_store_sk AS store_sk,
+                    ss_sold_date_sk AS date_sk,
+                    ss_ext_sales_price AS sales_price,
+                    ss_net_profit AS profit,
+                    cast(0 AS decimal(7, 2)) AS return_amt,
+                    cast(0 AS decimal(7, 2)) AS net_loss
+                FROM store_sales
+                UNION ALL
+                SELECT
+                    sr_store_sk AS store_sk,
+                    sr_returned_date_sk AS date_sk,
+                    cast(0 AS decimal(7, 2)) AS sales_price,
+                    cast(0 AS decimal(7, 2)) AS profit,
+                    sr_return_amt AS return_amt,
+                    sr_net_loss AS net_loss
+                FROM store_returns
+            ) salesreturns,
+            date_dim,
+            store
+        WHERE
+            date_sk = d_date_sk
+            AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+                cast('2000-08-23' AS date) + INTERVAL '14 days'
+            )
+            AND store_sk = s_store_sk
+        GROUP BY s_store_id
+    ),
+    csr AS (
+        SELECT
+            cp_catalog_page_id,
+            sum(sales_price) AS sales,
+            sum(profit) AS profit,
+            sum(return_amt) AS returns,
+            sum(net_loss) AS profit_loss
+        FROM
+            (
+                SELECT
+                    cs_catalog_page_sk AS page_sk,
+                    cs_sold_date_sk AS date_sk,
+                    cs_ext_sales_price AS sales_price,
+                    cs_net_profit AS profit,
+                    cast(0 AS decimal(7, 2)) AS return_amt,
+                    cast(0 AS decimal(7, 2)) AS net_loss
+                FROM catalog_sales
+                UNION ALL
+                SELECT
+                    cr_catalog_page_sk AS page_sk,
+                    cr_returned_date_sk AS date_sk,
+                    cast(0 AS decimal(7, 2)) AS sales_price,
+                    cast(0 AS decimal(7, 2)) AS profit,
+                    cr_return_amount AS return_amt,
+                    cr_net_loss AS net_loss
+                FROM catalog_returns
+            ) salesreturns,
+            date_dim,
+            catalog_page
+        WHERE
+            date_sk = d_date_sk
+            AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+                cast('2000-08-23' AS date) + INTERVAL '14 days'
+            )
+            AND page_sk = cp_catalog_page_sk
+        GROUP BY cp_catalog_page_id
+    ),
+    wsr AS (
+        SELECT
+            web_site_id,
+            sum(sales_price) AS sales,
+            sum(profit) AS profit,
+            sum(return_amt) AS returns,
+            sum(net_loss) AS profit_loss
+        FROM
+            (
+                SELECT
+                    ws_web_site_sk AS wsr_web_site_sk,
+                    ws_sold_date_sk AS date_sk,
+                    ws_ext_sales_price AS sales_price,
+                    ws_net_profit AS profit,
+                    cast(0 AS decimal(7, 2)) AS return_amt,
+                    cast(0 AS decimal(7, 2)) AS net_loss
+                FROM web_sales
+                UNION ALL
+                SELECT
+                    ws_web_site_sk AS wsr_web_site_sk,
+                    wr_returned_date_sk AS date_sk,
+                    cast(0 AS decimal(7, 2)) AS sales_price,
+                    cast(0 AS decimal(7, 2)) AS profit,
+                    wr_return_amt AS return_amt,
+                    wr_net_loss AS net_loss
+                FROM web_returns
+                LEFT OUTER JOIN
+                    web_sales
+                    ON (wr_item_sk = ws_item_sk AND wr_order_number = ws_order_number)
+            ) salesreturns,
+            date_dim,
+            web_site
+        WHERE
+            date_sk = d_date_sk
+            AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+                cast('2000-08-23' AS date) + INTERVAL '14 days'
+            )
+            AND wsr_web_site_sk = web_site_sk
+        GROUP BY web_site_id
+    )
+SELECT channel, id, sum(sales) AS sales, sum(returns) AS returns, sum(profit) AS profit
+FROM
+    (
+        SELECT
+            'store channel' AS channel,
+            concat('store', s_store_id) AS id,
+            sales,
+            returns,
+            (profit - profit_loss) AS profit
+        FROM ssr
+        UNION ALL
+        SELECT
+            'catalog channel' AS channel,
+            concat('catalog_page', cp_catalog_page_id) AS id,

Review Comment:
   I wasn't aware that df supported ||. It likely boils down to the same function call but good to align.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4563613152

   @andygrove  since I think you did the original TPCDS queries, any chance you can take a look at this PR? 
   
   It would help me reviewing to know how you made these changes (aka how can I double check that the new files more closely match the spec compared to the old ones)? I do have a copy of `DSGen-software-code-4.0.0` locally 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318580060


##########
datafusion/core/tests/tpc-ds/74.sql:
##########
@@ -1,62 +1,65 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with year_total as (
- select c_customer_id customer_id
-       ,c_first_name customer_first_name
-       ,c_last_name customer_last_name
-       ,d_year as year
-       ,max(ss_net_paid) year_total
-       ,'s' sale_type
- from customer
-     ,store_sales
-     ,date_dim
- where c_customer_sk = ss_customer_sk
-   and ss_sold_date_sk = d_date_sk
-   and d_year in (2001,2001+1)
- group by c_customer_id
-         ,c_first_name
-         ,c_last_name
-         ,d_year
- union all
- select c_customer_id customer_id
-       ,c_first_name customer_first_name
-       ,c_last_name customer_last_name
-       ,d_year as year
-       ,max(ws_net_paid) year_total
-       ,'w' sale_type
- from customer
-     ,web_sales
-     ,date_dim
- where c_customer_sk = ws_bill_customer_sk
-   and ws_sold_date_sk = d_date_sk
-   and d_year in (2001,2001+1)
- group by c_customer_id
-         ,c_first_name
-         ,c_last_name
-         ,d_year
-         )
-  select 
-        t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name
- from year_total t_s_firstyear
-     ,year_total t_s_secyear
-     ,year_total t_w_firstyear
-     ,year_total t_w_secyear
- where t_s_secyear.customer_id = t_s_firstyear.customer_id
-         and t_s_firstyear.customer_id = t_w_secyear.customer_id
-         and t_s_firstyear.customer_id = t_w_firstyear.customer_id
-         and t_s_firstyear.sale_type = 's'
-         and t_w_firstyear.sale_type = 'w'
-         and t_s_secyear.sale_type = 's'
-         and t_w_secyear.sale_type = 'w'
-         and t_s_firstyear.year = 2001
-         and t_s_secyear.year = 2001+1
-         and t_w_firstyear.year = 2001
-         and t_w_secyear.year = 2001+1
-         and t_s_firstyear.year_total > 0
-         and t_w_firstyear.year_total > 0
-         and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
-           > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
- order by 2,1,3
-limit 100;
-
-
+WITH
+    year_total AS (
+        SELECT
+            c_customer_id customer_id,
+            c_first_name customer_first_name,
+            c_last_name customer_last_name,
+            d_year AS year,
+            sum(ss_net_paid) year_total,
+            's' sale_type
+        FROM customer, store_sales, date_dim
+        WHERE
+            c_customer_sk = ss_customer_sk
+            AND ss_sold_date_sk = d_date_sk
+            AND d_year IN (2001, 2001 + 1)
+        GROUP BY c_customer_id, c_first_name, c_last_name, d_year
+        UNION ALL
+        SELECT
+            c_customer_id customer_id,
+            c_first_name customer_first_name,
+            c_last_name customer_last_name,
+            d_year AS year,
+            sum(ws_net_paid) year_total,
+            'w' sale_type
+        FROM customer, web_sales, date_dim
+        WHERE
+            c_customer_sk = ws_bill_customer_sk
+            AND ws_sold_date_sk = d_date_sk
+            AND d_year IN (2001, 2001 + 1)
+        GROUP BY c_customer_id, c_first_name, c_last_name, d_year
+    )
+SELECT
+    t_s_secyear.customer_id,
+    t_s_secyear.customer_first_name,
+    t_s_secyear.customer_last_name
+FROM
+    year_total t_s_firstyear,
+    year_total t_s_secyear,
+    year_total t_w_firstyear,
+    year_total t_w_secyear
+WHERE
+    t_s_secyear.customer_id = t_s_firstyear.customer_id
+    AND t_s_firstyear.customer_id = t_w_secyear.customer_id
+    AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
+    AND t_s_firstyear.sale_type = 's'
+    AND t_w_firstyear.sale_type = 'w'
+    AND t_s_secyear.sale_type = 's'
+    AND t_w_secyear.sale_type = 'w'
+    AND t_s_firstyear.year = 2001
+    AND t_s_secyear.year = 2001 + 1
+    AND t_w_firstyear.year = 2001
+    AND t_w_secyear.year = 2001 + 1
+    AND t_s_firstyear.year_total > 0
+    AND t_w_firstyear.year_total > 0
+    AND CASE
+        WHEN t_w_firstyear.year_total > 0
+        THEN t_w_secyear.year_total / t_w_firstyear.year_total
+        ELSE null
+    END > CASE
+        WHEN t_s_firstyear.year_total > 0
+        THEN t_s_secyear.year_total / t_s_firstyear.year_total
+        ELSE null
+    END
+ORDER BY 1, 1, 1

Review Comment:
   This seems to be unintentional?
   
   ```suggestion
   ORDER BY 2, 1, 3
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318748536


##########
datafusion/core/tests/tpc-ds/74.sql:
##########
@@ -1,62 +1,65 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with year_total as (
- select c_customer_id customer_id
-       ,c_first_name customer_first_name
-       ,c_last_name customer_last_name
-       ,d_year as year
-       ,max(ss_net_paid) year_total
-       ,'s' sale_type
- from customer
-     ,store_sales
-     ,date_dim
- where c_customer_sk = ss_customer_sk
-   and ss_sold_date_sk = d_date_sk
-   and d_year in (2001,2001+1)
- group by c_customer_id
-         ,c_first_name
-         ,c_last_name
-         ,d_year
- union all
- select c_customer_id customer_id
-       ,c_first_name customer_first_name
-       ,c_last_name customer_last_name
-       ,d_year as year
-       ,max(ws_net_paid) year_total
-       ,'w' sale_type
- from customer
-     ,web_sales
-     ,date_dim
- where c_customer_sk = ws_bill_customer_sk
-   and ws_sold_date_sk = d_date_sk
-   and d_year in (2001,2001+1)
- group by c_customer_id
-         ,c_first_name
-         ,c_last_name
-         ,d_year
-         )
-  select 
-        t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name
- from year_total t_s_firstyear
-     ,year_total t_s_secyear
-     ,year_total t_w_firstyear
-     ,year_total t_w_secyear
- where t_s_secyear.customer_id = t_s_firstyear.customer_id
-         and t_s_firstyear.customer_id = t_w_secyear.customer_id
-         and t_s_firstyear.customer_id = t_w_firstyear.customer_id
-         and t_s_firstyear.sale_type = 's'
-         and t_w_firstyear.sale_type = 'w'
-         and t_s_secyear.sale_type = 's'
-         and t_w_secyear.sale_type = 'w'
-         and t_s_firstyear.year = 2001
-         and t_s_secyear.year = 2001+1
-         and t_w_firstyear.year = 2001
-         and t_w_secyear.year = 2001+1
-         and t_s_firstyear.year_total > 0
-         and t_w_firstyear.year_total > 0
-         and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
-           > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
- order by 2,1,3
-limit 100;
-
-
+WITH
+    year_total AS (
+        SELECT
+            c_customer_id customer_id,
+            c_first_name customer_first_name,
+            c_last_name customer_last_name,
+            d_year AS year,
+            sum(ss_net_paid) year_total,

Review Comment:
   `sum` makes more sense to me, but the 4.0.0 tool uses `max`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3317479072


##########
datafusion/core/tests/tpc-ds/1.sql:
##########
@@ -1,26 +1,24 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with customer_total_return as
-(select sr_customer_sk as ctr_customer_sk
-,sr_store_sk as ctr_store_sk
-,sum(SR_FEE) as ctr_total_return
-from store_returns
-,date_dim
-where sr_returned_date_sk = d_date_sk
-and d_year =2000
-group by sr_customer_sk
-,sr_store_sk)
- select  c_customer_id
-from customer_total_return ctr1
-,store
-,customer
-where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
-from customer_total_return ctr2
-where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
-and s_store_sk = ctr1.ctr_store_sk
-and s_state = 'SD'
-and ctr1.ctr_customer_sk = c_customer_sk
-order by c_customer_id
-limit 100;
-
-
+WITH

Review Comment:
   The original 1.tpl file in DSGen-software-code-4.0.0 looks more similar to what is currently checked in
   
   Specifically, it is like this
   
   ```sql
   define COUNTY = random(1, rowcount("active_counties", "store"), uniform);
   define STATE = distmember(fips_county, [COUNTY], 3); 
   define YEAR = random(1998, 2002, uniform);
   define AGG_FIELD = text({"SR_RETURN_AMT",1},{"SR_FEE",1},{"SR_REFUNDED_CASH",1},{"SR_RETURN_AMT_INC_TAX",1},{"SR_REVERSED_CHARGE",1},{"SR_STORE_CREDIT",1},{"SR_RETURN_TAX",1});
   define _LIMIT=100;
   
   with customer_total_return as
   (select sr_customer_sk as ctr_customer_sk
   ,sr_store_sk as ctr_store_sk
   ,sum([AGG_FIELD]) as ctr_total_return
   from store_returns
   ,date_dim
   where sr_returned_date_sk = d_date_sk
   and d_year =[YEAR]
   group by sr_customer_sk
   ,sr_store_sk)
   [_LIMITA] select [_LIMITB] c_customer_id
   from customer_total_return ctr1
   ,store
   ,customer
   where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
   from customer_total_return ctr2
   where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
   and s_store_sk = ctr1.ctr_store_sk
   and s_state = '[STATE]'
   and ctr1.ctr_customer_sk = c_customer_sk
   order by c_customer_id
   [_LIMITC];
   
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313273233


##########
datafusion/core/tests/tpc-ds/5.sql:
##########
@@ -1,129 +1,146 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ssr as
- (select s_store_id,
-        sum(sales_price) as sales,
-        sum(profit) as profit,
-        sum(return_amt) as returns,
-        sum(net_loss) as profit_loss
- from
-  ( select  ss_store_sk as store_sk,
-            ss_sold_date_sk  as date_sk,
-            ss_ext_sales_price as sales_price,
-            ss_net_profit as profit,
-            cast(0 as decimal(7,2)) as return_amt,
-            cast(0 as decimal(7,2)) as net_loss
-    from store_sales
-    union all
-    select sr_store_sk as store_sk,
-           sr_returned_date_sk as date_sk,
-           cast(0 as decimal(7,2)) as sales_price,
-           cast(0 as decimal(7,2)) as profit,
-           sr_return_amt as return_amt,
-           sr_net_loss as net_loss
-    from store_returns
-   ) salesreturns,
-     date_dim,
-     store
- where date_sk = d_date_sk
-       and d_date between cast('1998-08-04' as date) 
-                  and (cast('1998-08-04' as date) + INTERVAL '14 days')
-       and store_sk = s_store_sk
- group by s_store_id)
- ,
- csr as
- (select cp_catalog_page_id,
-        sum(sales_price) as sales,
-        sum(profit) as profit,
-        sum(return_amt) as returns,
-        sum(net_loss) as profit_loss
- from
-  ( select  cs_catalog_page_sk as page_sk,
-            cs_sold_date_sk  as date_sk,
-            cs_ext_sales_price as sales_price,
-            cs_net_profit as profit,
-            cast(0 as decimal(7,2)) as return_amt,
-            cast(0 as decimal(7,2)) as net_loss
-    from catalog_sales
-    union all
-    select cr_catalog_page_sk as page_sk,
-           cr_returned_date_sk as date_sk,
-           cast(0 as decimal(7,2)) as sales_price,
-           cast(0 as decimal(7,2)) as profit,
-           cr_return_amount as return_amt,
-           cr_net_loss as net_loss
-    from catalog_returns
-   ) salesreturns,
-     date_dim,
-     catalog_page
- where date_sk = d_date_sk
-       and d_date between cast('1998-08-04' as date)
-                  and (cast('1998-08-04' as date) + INTERVAL '14 days')
-       and page_sk = cp_catalog_page_sk
- group by cp_catalog_page_id)
- ,
- wsr as
- (select web_site_id,
-        sum(sales_price) as sales,
-        sum(profit) as profit,
-        sum(return_amt) as returns,
-        sum(net_loss) as profit_loss
- from
-  ( select  ws_web_site_sk as wsr_web_site_sk,
-            ws_sold_date_sk  as date_sk,
-            ws_ext_sales_price as sales_price,
-            ws_net_profit as profit,
-            cast(0 as decimal(7,2)) as return_amt,
-            cast(0 as decimal(7,2)) as net_loss
-    from web_sales
-    union all
-    select ws_web_site_sk as wsr_web_site_sk,
-           wr_returned_date_sk as date_sk,
-           cast(0 as decimal(7,2)) as sales_price,
-           cast(0 as decimal(7,2)) as profit,
-           wr_return_amt as return_amt,
-           wr_net_loss as net_loss
-    from web_returns left outer join web_sales on
-         ( wr_item_sk = ws_item_sk
-           and wr_order_number = ws_order_number)
-   ) salesreturns,
-     date_dim,
-     web_site
- where date_sk = d_date_sk
-       and d_date between cast('1998-08-04' as date)
-                  and (cast('1998-08-04' as date) + INTERVAL '14 days')
-       and wsr_web_site_sk = web_site_sk
- group by web_site_id)
-  select  channel
-        , id
-        , sum(sales) as sales
-        , sum(returns) as returns
-        , sum(profit) as profit
- from 
- (select 'store channel' as channel
-        , 'store' || s_store_id as id
-        , sales
-        , returns
-        , (profit - profit_loss) as profit
- from   ssr
- union all
- select 'catalog channel' as channel
-        , 'catalog_page' || cp_catalog_page_id as id
-        , sales
-        , returns
-        , (profit - profit_loss) as profit
- from  csr
- union all
- select 'web channel' as channel
-        , 'web_site' || web_site_id as id
-        , sales
-        , returns
-        , (profit - profit_loss) as profit
- from   wsr
- ) x
- group by rollup (channel, id)
- order by channel
-         ,id
- limit 100;
-
-
+WITH
+    ssr AS (
+        SELECT
+            s_store_id,
+            sum(sales_price) AS sales,
+            sum(profit) AS profit,
+            sum(return_amt) AS returns,
+            sum(net_loss) AS profit_loss
+        FROM
+            (
+                SELECT
+                    ss_store_sk AS store_sk,
+                    ss_sold_date_sk AS date_sk,
+                    ss_ext_sales_price AS sales_price,
+                    ss_net_profit AS profit,
+                    cast(0 AS decimal(7, 2)) AS return_amt,
+                    cast(0 AS decimal(7, 2)) AS net_loss
+                FROM store_sales
+                UNION ALL
+                SELECT
+                    sr_store_sk AS store_sk,
+                    sr_returned_date_sk AS date_sk,
+                    cast(0 AS decimal(7, 2)) AS sales_price,
+                    cast(0 AS decimal(7, 2)) AS profit,
+                    sr_return_amt AS return_amt,
+                    sr_net_loss AS net_loss
+                FROM store_returns
+            ) salesreturns,
+            date_dim,
+            store
+        WHERE
+            date_sk = d_date_sk
+            AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+                cast('2000-08-23' AS date) + INTERVAL '14 days'
+            )
+            AND store_sk = s_store_sk
+        GROUP BY s_store_id
+    ),
+    csr AS (
+        SELECT
+            cp_catalog_page_id,
+            sum(sales_price) AS sales,
+            sum(profit) AS profit,
+            sum(return_amt) AS returns,
+            sum(net_loss) AS profit_loss
+        FROM
+            (
+                SELECT
+                    cs_catalog_page_sk AS page_sk,
+                    cs_sold_date_sk AS date_sk,
+                    cs_ext_sales_price AS sales_price,
+                    cs_net_profit AS profit,
+                    cast(0 AS decimal(7, 2)) AS return_amt,
+                    cast(0 AS decimal(7, 2)) AS net_loss
+                FROM catalog_sales
+                UNION ALL
+                SELECT
+                    cr_catalog_page_sk AS page_sk,
+                    cr_returned_date_sk AS date_sk,
+                    cast(0 AS decimal(7, 2)) AS sales_price,
+                    cast(0 AS decimal(7, 2)) AS profit,
+                    cr_return_amount AS return_amt,
+                    cr_net_loss AS net_loss
+                FROM catalog_returns
+            ) salesreturns,
+            date_dim,
+            catalog_page
+        WHERE
+            date_sk = d_date_sk
+            AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+                cast('2000-08-23' AS date) + INTERVAL '14 days'
+            )
+            AND page_sk = cp_catalog_page_sk
+        GROUP BY cp_catalog_page_id
+    ),
+    wsr AS (
+        SELECT
+            web_site_id,
+            sum(sales_price) AS sales,
+            sum(profit) AS profit,
+            sum(return_amt) AS returns,
+            sum(net_loss) AS profit_loss
+        FROM
+            (
+                SELECT
+                    ws_web_site_sk AS wsr_web_site_sk,
+                    ws_sold_date_sk AS date_sk,
+                    ws_ext_sales_price AS sales_price,
+                    ws_net_profit AS profit,
+                    cast(0 AS decimal(7, 2)) AS return_amt,
+                    cast(0 AS decimal(7, 2)) AS net_loss
+                FROM web_sales
+                UNION ALL
+                SELECT
+                    ws_web_site_sk AS wsr_web_site_sk,
+                    wr_returned_date_sk AS date_sk,
+                    cast(0 AS decimal(7, 2)) AS sales_price,
+                    cast(0 AS decimal(7, 2)) AS profit,
+                    wr_return_amt AS return_amt,
+                    wr_net_loss AS net_loss
+                FROM web_returns
+                LEFT OUTER JOIN
+                    web_sales
+                    ON (wr_item_sk = ws_item_sk AND wr_order_number = ws_order_number)
+            ) salesreturns,
+            date_dim,
+            web_site
+        WHERE
+            date_sk = d_date_sk
+            AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+                cast('2000-08-23' AS date) + INTERVAL '14 days'
+            )
+            AND wsr_web_site_sk = web_site_sk
+        GROUP BY web_site_id
+    )
+SELECT channel, id, sum(sales) AS sales, sum(returns) AS returns, sum(profit) AS profit
+FROM
+    (
+        SELECT
+            'store channel' AS channel,
+            concat('store', s_store_id) AS id,
+            sales,
+            returns,
+            (profit - profit_loss) AS profit
+        FROM ssr
+        UNION ALL
+        SELECT
+            'catalog channel' AS channel,
+            concat('catalog_page', cp_catalog_page_id) AS id,

Review Comment:
   The spec uses `||` here, not `concat`.



##########
datafusion/core/tests/tpc-ds/66.sql:
##########
@@ -1,221 +1,257 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-select   
-         w_warehouse_name
- 	,w_warehouse_sq_ft
- 	,w_city
- 	,w_county
- 	,w_state
- 	,w_country
-        ,ship_carriers
-        ,year
- 	,sum(jan_sales) as jan_sales
- 	,sum(feb_sales) as feb_sales
- 	,sum(mar_sales) as mar_sales
- 	,sum(apr_sales) as apr_sales
- 	,sum(may_sales) as may_sales
- 	,sum(jun_sales) as jun_sales
- 	,sum(jul_sales) as jul_sales
- 	,sum(aug_sales) as aug_sales
- 	,sum(sep_sales) as sep_sales
- 	,sum(oct_sales) as oct_sales
- 	,sum(nov_sales) as nov_sales
- 	,sum(dec_sales) as dec_sales
- 	,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot
- 	,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot
- 	,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot
- 	,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot
- 	,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot
- 	,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot
- 	,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot
- 	,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot
- 	,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot
- 	,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot
- 	,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot
- 	,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot
- 	,sum(jan_net) as jan_net
- 	,sum(feb_net) as feb_net
- 	,sum(mar_net) as mar_net
- 	,sum(apr_net) as apr_net
- 	,sum(may_net) as may_net
- 	,sum(jun_net) as jun_net
- 	,sum(jul_net) as jul_net
- 	,sum(aug_net) as aug_net
- 	,sum(sep_net) as sep_net
- 	,sum(oct_net) as oct_net
- 	,sum(nov_net) as nov_net
- 	,sum(dec_net) as dec_net
- from (
-     select 
- 	w_warehouse_name
- 	,w_warehouse_sq_ft
- 	,w_city
- 	,w_county
- 	,w_state
- 	,w_country
- 	,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
-       ,d_year as year
- 	,sum(case when d_moy = 1 
- 		then ws_sales_price* ws_quantity else 0 end) as jan_sales
- 	,sum(case when d_moy = 2 
- 		then ws_sales_price* ws_quantity else 0 end) as feb_sales
- 	,sum(case when d_moy = 3 
- 		then ws_sales_price* ws_quantity else 0 end) as mar_sales
- 	,sum(case when d_moy = 4 
- 		then ws_sales_price* ws_quantity else 0 end) as apr_sales
- 	,sum(case when d_moy = 5 
- 		then ws_sales_price* ws_quantity else 0 end) as may_sales
- 	,sum(case when d_moy = 6 
- 		then ws_sales_price* ws_quantity else 0 end) as jun_sales
- 	,sum(case when d_moy = 7 
- 		then ws_sales_price* ws_quantity else 0 end) as jul_sales
- 	,sum(case when d_moy = 8 
- 		then ws_sales_price* ws_quantity else 0 end) as aug_sales
- 	,sum(case when d_moy = 9 
- 		then ws_sales_price* ws_quantity else 0 end) as sep_sales
- 	,sum(case when d_moy = 10 
- 		then ws_sales_price* ws_quantity else 0 end) as oct_sales
- 	,sum(case when d_moy = 11
- 		then ws_sales_price* ws_quantity else 0 end) as nov_sales
- 	,sum(case when d_moy = 12
- 		then ws_sales_price* ws_quantity else 0 end) as dec_sales
- 	,sum(case when d_moy = 1 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net
- 	,sum(case when d_moy = 2
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net
- 	,sum(case when d_moy = 3 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net
- 	,sum(case when d_moy = 4 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net
- 	,sum(case when d_moy = 5 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net
- 	,sum(case when d_moy = 6 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net
- 	,sum(case when d_moy = 7 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net
- 	,sum(case when d_moy = 8 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net
- 	,sum(case when d_moy = 9 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net
- 	,sum(case when d_moy = 10 
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net
- 	,sum(case when d_moy = 11
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net
- 	,sum(case when d_moy = 12
- 		then ws_net_paid_inc_tax * ws_quantity else 0 end) as dec_net
-     from
-          web_sales
-         ,warehouse
-         ,date_dim
-         ,time_dim
- 	  ,ship_mode
-     where
-            ws_warehouse_sk =  w_warehouse_sk
-        and ws_sold_date_sk = d_date_sk
-        and ws_sold_time_sk = t_time_sk
- 	and ws_ship_mode_sk = sm_ship_mode_sk
-        and d_year = 2002
- 	and t_time between 49530 and 49530+28800 
- 	and sm_carrier in ('DIAMOND','AIRBORNE')
-     group by 
-        w_warehouse_name
- 	,w_warehouse_sq_ft
- 	,w_city
- 	,w_county
- 	,w_state
- 	,w_country
-       ,d_year
- union all
-     select 
- 	w_warehouse_name
- 	,w_warehouse_sq_ft
- 	,w_city
- 	,w_county
- 	,w_state
- 	,w_country
- 	,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
-       ,d_year as year
- 	,sum(case when d_moy = 1 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales
- 	,sum(case when d_moy = 2 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales
- 	,sum(case when d_moy = 3 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales
- 	,sum(case when d_moy = 4 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales
- 	,sum(case when d_moy = 5 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as may_sales
- 	,sum(case when d_moy = 6 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales
- 	,sum(case when d_moy = 7 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales
- 	,sum(case when d_moy = 8 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales
- 	,sum(case when d_moy = 9 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales
- 	,sum(case when d_moy = 10 
- 		then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales
- 	,sum(case when d_moy = 11
- 		then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales
- 	,sum(case when d_moy = 12
- 		then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales
- 	,sum(case when d_moy = 1 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jan_net
- 	,sum(case when d_moy = 2 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as feb_net
- 	,sum(case when d_moy = 3 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as mar_net
- 	,sum(case when d_moy = 4 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as apr_net
- 	,sum(case when d_moy = 5 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as may_net
- 	,sum(case when d_moy = 6 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jun_net
- 	,sum(case when d_moy = 7 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jul_net
- 	,sum(case when d_moy = 8 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as aug_net
- 	,sum(case when d_moy = 9 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as sep_net
- 	,sum(case when d_moy = 10 
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as oct_net
- 	,sum(case when d_moy = 11
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as nov_net
- 	,sum(case when d_moy = 12
- 		then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as dec_net
-     from
-          catalog_sales
-         ,warehouse
-         ,date_dim
-         ,time_dim
- 	 ,ship_mode
-     where
-            cs_warehouse_sk =  w_warehouse_sk
-        and cs_sold_date_sk = d_date_sk
-        and cs_sold_time_sk = t_time_sk
- 	and cs_ship_mode_sk = sm_ship_mode_sk
-        and d_year = 2002
- 	and t_time between 49530 AND 49530+28800 
- 	and sm_carrier in ('DIAMOND','AIRBORNE')
-     group by 
-        w_warehouse_name
- 	,w_warehouse_sq_ft
- 	,w_city
- 	,w_county
- 	,w_state
- 	,w_country
-       ,d_year
- ) x
- group by 
-        w_warehouse_name
- 	,w_warehouse_sq_ft
- 	,w_city
- 	,w_county
- 	,w_state
- 	,w_country
- 	,ship_carriers
-       ,year
- order by w_warehouse_name
- limit 100;
-
-
+SELECT
+    w_warehouse_name,
+    w_warehouse_sq_ft,
+    w_city,
+    w_county,
+    w_state,
+    w_country,
+    ship_carriers,
+    year,
+    sum(jan_sales) AS jan_sales,
+    sum(feb_sales) AS feb_sales,
+    sum(mar_sales) AS mar_sales,
+    sum(apr_sales) AS apr_sales,
+    sum(may_sales) AS may_sales,
+    sum(jun_sales) AS jun_sales,
+    sum(jul_sales) AS jul_sales,
+    sum(aug_sales) AS aug_sales,
+    sum(sep_sales) AS sep_sales,
+    sum(oct_sales) AS oct_sales,
+    sum(nov_sales) AS nov_sales,
+    sum(dec_sales) AS dec_sales,
+    sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
+    sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
+    sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
+    sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
+    sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
+    sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
+    sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
+    sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
+    sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
+    sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
+    sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
+    sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
+    sum(jan_net) AS jan_net,
+    sum(feb_net) AS feb_net,
+    sum(mar_net) AS mar_net,
+    sum(apr_net) AS apr_net,
+    sum(may_net) AS may_net,
+    sum(jun_net) AS jun_net,
+    sum(jul_net) AS jul_net,
+    sum(aug_net) AS aug_net,
+    sum(sep_net) AS sep_net,
+    sum(oct_net) AS oct_net,
+    sum(nov_net) AS nov_net,
+    sum(dec_net) AS dec_net
+FROM
+    (
+        SELECT
+            w_warehouse_name,
+            w_warehouse_sq_ft,
+            w_city,
+            w_county,
+            w_state,
+            w_country,
+            concat('DHL', ',', 'BARIAN') AS ship_carriers,

Review Comment:
   `||` here as well



##########
datafusion/core/tests/tpc-ds/78.sql:
##########
@@ -1,59 +1,90 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ws as
-  (select d_year AS ws_sold_year, ws_item_sk,
-    ws_bill_customer_sk ws_customer_sk,
-    sum(ws_quantity) ws_qty,
-    sum(ws_wholesale_cost) ws_wc,
-    sum(ws_sales_price) ws_sp
-   from web_sales
-   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
-   join date_dim on ws_sold_date_sk = d_date_sk
-   where wr_order_number is null
-   group by d_year, ws_item_sk, ws_bill_customer_sk
-   ),
-cs as
-  (select d_year AS cs_sold_year, cs_item_sk,
-    cs_bill_customer_sk cs_customer_sk,
-    sum(cs_quantity) cs_qty,
-    sum(cs_wholesale_cost) cs_wc,
-    sum(cs_sales_price) cs_sp
-   from catalog_sales
-   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
-   join date_dim on cs_sold_date_sk = d_date_sk
-   where cr_order_number is null
-   group by d_year, cs_item_sk, cs_bill_customer_sk
-   ),
-ss as
-  (select d_year AS ss_sold_year, ss_item_sk,
+WITH
+    ws AS (
+        SELECT
+            d_year AS ws_sold_year,
+            ws_item_sk,
+            ws_bill_customer_sk ws_customer_sk,
+            sum(ws_quantity) ws_qty,
+            sum(ws_wholesale_cost) ws_wc,
+            sum(ws_sales_price) ws_sp
+        FROM web_sales
+        LEFT JOIN
+            web_returns ON wr_order_number = ws_order_number AND ws_item_sk = wr_item_sk
+        JOIN date_dim ON ws_sold_date_sk = d_date_sk
+        WHERE wr_order_number IS NULL
+        GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
+    ),
+    cs AS (
+        SELECT
+            d_year AS cs_sold_year,
+            cs_item_sk,
+            cs_bill_customer_sk cs_customer_sk,
+            sum(cs_quantity) cs_qty,
+            sum(cs_wholesale_cost) cs_wc,
+            sum(cs_sales_price) cs_sp
+        FROM catalog_sales
+        LEFT JOIN
+            catalog_returns
+            ON cr_order_number = cs_order_number
+            AND cs_item_sk = cr_item_sk
+        JOIN date_dim ON cs_sold_date_sk = d_date_sk
+        WHERE cr_order_number IS NULL
+        GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
+    ),
+    ss AS (
+        SELECT
+            d_year AS ss_sold_year,
+            ss_item_sk,
+            ss_customer_sk,
+            sum(ss_quantity) ss_qty,
+            sum(ss_wholesale_cost) ss_wc,
+            sum(ss_sales_price) ss_sp
+        FROM store_sales
+        LEFT JOIN
+            store_returns
+            ON sr_ticket_number = ss_ticket_number
+            AND ss_item_sk = sr_item_sk
+        JOIN date_dim ON ss_sold_date_sk = d_date_sk
+        WHERE sr_ticket_number IS NULL
+        GROUP BY d_year, ss_item_sk, ss_customer_sk
+    )
+SELECT
+    ss_sold_year,
+    ss_item_sk,
     ss_customer_sk,
-    sum(ss_quantity) ss_qty,
-    sum(ss_wholesale_cost) ss_wc,
-    sum(ss_sales_price) ss_sp
-   from store_sales
-   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
-   join date_dim on ss_sold_date_sk = d_date_sk
-   where sr_ticket_number is null
-   group by d_year, ss_item_sk, ss_customer_sk
-   )
- select 
-ss_sold_year, ss_item_sk, ss_customer_sk,
-round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
-ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
-coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
-coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
-coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
-from ss
-left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
-left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
-where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
-order by 
-  ss_sold_year, ss_item_sk, ss_customer_sk,
-  ss_qty desc, ss_wc desc, ss_sp desc,
-  other_chan_qty,
-  other_chan_wholesale_cost,
-  other_chan_sales_price,
-  ratio
-limit 100;
-
-
+    round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
+    ss_qty store_qty,
+    ss_wc store_wholesale_cost,
+    ss_sp store_sales_price,
+    coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
+    coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
+    coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
+FROM ss
+LEFT JOIN
+    ws
+    ON (
+        ws_sold_year = ss_sold_year
+        AND ws_item_sk = ss_item_sk
+        AND ws_customer_sk = ss_customer_sk
+    )
+LEFT JOIN
+    cs
+    ON (
+        cs_sold_year = ss_sold_year
+        AND cs_item_sk = ss_item_sk
+        AND cs_customer_sk = ss_customer_sk
+    )
+WHERE (coalesce(ws_qty, 0) > 0 OR coalesce(cs_qty, 0) > 0) AND ss_sold_year = 2000
+ORDER BY
+    ss_sold_year,
+    ss_item_sk,
+    ss_customer_sk,
+    ss_qty desc,
+    ss_wc desc,
+    ss_sp desc,
+    other_chan_qty,
+    other_chan_wholesale_cost,
+    other_chan_sales_price,
+    round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2)

Review Comment:
   Looks like the spec wants a slightly different formula: `round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2)`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318448960


##########
datafusion/core/tests/tpc-ds/1.sql:
##########
@@ -1,26 +1,24 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with customer_total_return as
-(select sr_customer_sk as ctr_customer_sk
-,sr_store_sk as ctr_store_sk
-,sum(SR_FEE) as ctr_total_return
-from store_returns
-,date_dim
-where sr_returned_date_sk = d_date_sk
-and d_year =2000
-group by sr_customer_sk
-,sr_store_sk)
- select  c_customer_id
-from customer_total_return ctr1
-,store
-,customer
-where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
-from customer_total_return ctr2
-where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
-and s_store_sk = ctr1.ctr_store_sk
-and s_state = 'SD'
-and ctr1.ctr_customer_sk = c_customer_sk
-order by c_customer_id
-limit 100;
-
-
+WITH

Review Comment:
   I am not sure -- I haven't done the analysis to compare. Basically I was just trying to do some spot checks / double check in an automated way (eg.. `diff` against the `tpl` files)  that all 5k lines of this PR don't change the semantics



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318650186


##########
datafusion/core/tests/tpc-ds/10.sql:
##########
@@ -1,60 +1,75 @@
--- Copyright 2015 Transaction Processing Performance Council

Review Comment:
   Should we be removing the copyright notice?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4558704758

   Thanks for the review Neil - I've applied fixes for all the issues uncovered.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318739344


##########
datafusion/core/tests/tpc-ds/9.sql:
##########
@@ -1,52 +1,76 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-select case when (select count(*) 
-                  from store_sales 
-                  where ss_quantity between 1 and 20) > 409437
-            then (select avg(ss_ext_tax) 
-                  from store_sales 
-                  where ss_quantity between 1 and 20) 
-            else (select avg(ss_net_paid)
-                  from store_sales
-                  where ss_quantity between 1 and 20) end bucket1 ,
-       case when (select count(*)
-                  from store_sales
-                  where ss_quantity between 21 and 40) > 4595804
-            then (select avg(ss_ext_tax)
-                  from store_sales
-                  where ss_quantity between 21 and 40) 
-            else (select avg(ss_net_paid)
-                  from store_sales
-                  where ss_quantity between 21 and 40) end bucket2,
-       case when (select count(*)
-                  from store_sales
-                  where ss_quantity between 41 and 60) > 1333710
-            then (select avg(ss_ext_tax)
-                  from store_sales
-                  where ss_quantity between 41 and 60)
-            else (select avg(ss_net_paid)
-                  from store_sales
-                  where ss_quantity between 41 and 60) end bucket3,
-       case when (select count(*)
-                  from store_sales
-                  where ss_quantity between 61 and 80) > 2361102
-            then (select avg(ss_ext_tax)
-                  from store_sales
-                  where ss_quantity between 61 and 80)
-            else (select avg(ss_net_paid)
-                  from store_sales
-                  where ss_quantity between 61 and 80) end bucket4,
-       case when (select count(*)
-                  from store_sales
-                  where ss_quantity between 81 and 100) > 1517817
-            then (select avg(ss_ext_tax)
-                  from store_sales
-                  where ss_quantity between 81 and 100)
-            else (select avg(ss_net_paid)
-                  from store_sales
-                  where ss_quantity between 81 and 100) end bucket5
-from reason
-where r_reason_sk = 1
+SELECT CASE
+           WHEN
+               (SELECT count(*)
+                FROM store_sales
+                WHERE ss_quantity BETWEEN 1
+                          AND 20)
+                   > 74129
+               THEN
+               (SELECT avg(ss_ext_discount_amt)

Review Comment:
   Is it intentional that this changed from `ss_ext_tax` to `ss_ext_discount_amt`?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318312481


##########
datafusion/core/tests/tpc-ds/1.sql:
##########
@@ -1,26 +1,24 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with customer_total_return as
-(select sr_customer_sk as ctr_customer_sk
-,sr_store_sk as ctr_store_sk
-,sum(SR_FEE) as ctr_total_return
-from store_returns
-,date_dim
-where sr_returned_date_sk = d_date_sk
-and d_year =2000
-group by sr_customer_sk
-,sr_store_sk)
- select  c_customer_id
-from customer_total_return ctr1
-,store
-,customer
-where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
-from customer_total_return ctr2
-where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
-and s_store_sk = ctr1.ctr_store_sk
-and s_state = 'SD'
-and ctr1.ctr_customer_sk = c_customer_sk
-order by c_customer_id
-limit 100;
-
-
+WITH

Review Comment:
   If I'm reading this right, the differences are just
   
   1. Formatting
   2. Variable substitution -- the PR picks the qualification instantiation, we were previously using a different (legal) substitution
   
   Right?
   
   Using the qualification instantiation is probably better, unless we have some reason not to



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313514145


##########
datafusion/core/tests/tpc-ds/78.sql:
##########
@@ -1,59 +1,90 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ws as
-  (select d_year AS ws_sold_year, ws_item_sk,
-    ws_bill_customer_sk ws_customer_sk,
-    sum(ws_quantity) ws_qty,
-    sum(ws_wholesale_cost) ws_wc,
-    sum(ws_sales_price) ws_sp
-   from web_sales
-   left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
-   join date_dim on ws_sold_date_sk = d_date_sk
-   where wr_order_number is null
-   group by d_year, ws_item_sk, ws_bill_customer_sk
-   ),
-cs as
-  (select d_year AS cs_sold_year, cs_item_sk,
-    cs_bill_customer_sk cs_customer_sk,
-    sum(cs_quantity) cs_qty,
-    sum(cs_wholesale_cost) cs_wc,
-    sum(cs_sales_price) cs_sp
-   from catalog_sales
-   left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
-   join date_dim on cs_sold_date_sk = d_date_sk
-   where cr_order_number is null
-   group by d_year, cs_item_sk, cs_bill_customer_sk
-   ),
-ss as
-  (select d_year AS ss_sold_year, ss_item_sk,
+WITH
+    ws AS (
+        SELECT
+            d_year AS ws_sold_year,
+            ws_item_sk,
+            ws_bill_customer_sk ws_customer_sk,
+            sum(ws_quantity) ws_qty,
+            sum(ws_wholesale_cost) ws_wc,
+            sum(ws_sales_price) ws_sp
+        FROM web_sales
+        LEFT JOIN
+            web_returns ON wr_order_number = ws_order_number AND ws_item_sk = wr_item_sk
+        JOIN date_dim ON ws_sold_date_sk = d_date_sk
+        WHERE wr_order_number IS NULL
+        GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
+    ),
+    cs AS (
+        SELECT
+            d_year AS cs_sold_year,
+            cs_item_sk,
+            cs_bill_customer_sk cs_customer_sk,
+            sum(cs_quantity) cs_qty,
+            sum(cs_wholesale_cost) cs_wc,
+            sum(cs_sales_price) cs_sp
+        FROM catalog_sales
+        LEFT JOIN
+            catalog_returns
+            ON cr_order_number = cs_order_number
+            AND cs_item_sk = cr_item_sk
+        JOIN date_dim ON cs_sold_date_sk = d_date_sk
+        WHERE cr_order_number IS NULL
+        GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
+    ),
+    ss AS (
+        SELECT
+            d_year AS ss_sold_year,
+            ss_item_sk,
+            ss_customer_sk,
+            sum(ss_quantity) ss_qty,
+            sum(ss_wholesale_cost) ss_wc,
+            sum(ss_sales_price) ss_sp
+        FROM store_sales
+        LEFT JOIN
+            store_returns
+            ON sr_ticket_number = ss_ticket_number
+            AND ss_item_sk = sr_item_sk
+        JOIN date_dim ON ss_sold_date_sk = d_date_sk
+        WHERE sr_ticket_number IS NULL
+        GROUP BY d_year, ss_item_sk, ss_customer_sk
+    )
+SELECT
+    ss_sold_year,
+    ss_item_sk,
     ss_customer_sk,
-    sum(ss_quantity) ss_qty,
-    sum(ss_wholesale_cost) ss_wc,
-    sum(ss_sales_price) ss_sp
-   from store_sales
-   left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
-   join date_dim on ss_sold_date_sk = d_date_sk
-   where sr_ticket_number is null
-   group by d_year, ss_item_sk, ss_customer_sk
-   )
- select 
-ss_sold_year, ss_item_sk, ss_customer_sk,
-round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
-ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
-coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
-coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
-coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
-from ss
-left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
-left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
-where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
-order by 
-  ss_sold_year, ss_item_sk, ss_customer_sk,
-  ss_qty desc, ss_wc desc, ss_sp desc,
-  other_chan_qty,
-  other_chan_wholesale_cost,
-  other_chan_sales_price,
-  ratio
-limit 100;
-
-
+    round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
+    ss_qty store_qty,
+    ss_wc store_wholesale_cost,
+    ss_sp store_sales_price,
+    coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
+    coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
+    coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
+FROM ss
+LEFT JOIN
+    ws
+    ON (
+        ws_sold_year = ss_sold_year
+        AND ws_item_sk = ss_item_sk
+        AND ws_customer_sk = ss_customer_sk
+    )
+LEFT JOIN
+    cs
+    ON (
+        cs_sold_year = ss_sold_year
+        AND cs_item_sk = ss_item_sk
+        AND cs_customer_sk = ss_customer_sk
+    )
+WHERE (coalesce(ws_qty, 0) > 0 OR coalesce(cs_qty, 0) > 0) AND ss_sold_year = 2000
+ORDER BY
+    ss_sold_year,
+    ss_item_sk,
+    ss_customer_sk,
+    ss_qty desc,
+    ss_wc desc,
+    ss_sp desc,
+    other_chan_qty,
+    other_chan_wholesale_cost,
+    other_chan_sales_price,
+    round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2)

Review Comment:
   Thanks, I'll fix this.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4569743284

   Thank you for the review. Given the obvious issues that neither I nor Codex caught I'm going to move this back to draft till I've decided what to do with this PR.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318312481


##########
datafusion/core/tests/tpc-ds/1.sql:
##########
@@ -1,26 +1,24 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with customer_total_return as
-(select sr_customer_sk as ctr_customer_sk
-,sr_store_sk as ctr_store_sk
-,sum(SR_FEE) as ctr_total_return
-from store_returns
-,date_dim
-where sr_returned_date_sk = d_date_sk
-and d_year =2000
-group by sr_customer_sk
-,sr_store_sk)
- select  c_customer_id
-from customer_total_return ctr1
-,store
-,customer
-where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
-from customer_total_return ctr2
-where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
-and s_store_sk = ctr1.ctr_store_sk
-and s_state = 'SD'
-and ctr1.ctr_customer_sk = c_customer_sk
-order by c_customer_id
-limit 100;
-
-
+WITH

Review Comment:
   If I'm reading this right, the differences are just
   
   * Formatting
   * Variable substitution -- the PR picks the qualification instantiation, we were previously using a different (legal) substitution. Using the qualification instantiation is probably better, unless we have some reason not to
   
   Right? 



##########
datafusion/core/tests/tpc-ds/1.sql:
##########
@@ -1,26 +1,24 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with customer_total_return as
-(select sr_customer_sk as ctr_customer_sk
-,sr_store_sk as ctr_store_sk
-,sum(SR_FEE) as ctr_total_return
-from store_returns
-,date_dim
-where sr_returned_date_sk = d_date_sk
-and d_year =2000
-group by sr_customer_sk
-,sr_store_sk)
- select  c_customer_id
-from customer_total_return ctr1
-,store
-,customer
-where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
-from customer_total_return ctr2
-where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
-and s_store_sk = ctr1.ctr_store_sk
-and s_state = 'SD'
-and ctr1.ctr_customer_sk = c_customer_sk
-order by c_customer_id
-limit 100;
-
-
+WITH

Review Comment:
   If I'm reading this right, the differences are just
   
   * Formatting
   * Variable substitution -- the PR picks the qualification instantiation, we were previously using a different (legal) substitution.
   
   Right?
   
   Using the qualification instantiation is probably better, unless we have some reason not to



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4564792740

   > @andygrove since I think you did the original TPCDS queries, any chance you can take a look at this PR?
   > 
   > It would help me reviewing to know how you made these changes (aka how can I double check that the new files more closely match the spec compared to the old ones)? I do have a copy of `DSGen-software-code-4.0.0` locally
   
   I'm fairly sure that I used version 2.7.0 to generate the queries and I would not have modified them other than any minor syntactic changes to work with DataFusion's default SQL dialect at the time.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4565287267

   I'm going to generate the queries locally with version 4.0.0 at scale factor 1 and will burn some tokens doing an additional review.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#issuecomment-4565474441

   Hi @Omega359 — I ran an LLM-assisted comparison of the updated queries in this PR against the official TPC-DS 4.0.0 reference queries (`DSGen-software-code-4.0.0/generated_queries/queryN.sql`). The methodology was: normalize whitespace, casing, comments, interval-vs-integer date arithmetic, and all string/numeric literals (since `dsqgen` produces different literal values per instance), then flag remaining semantic differences. Flagging that this was done with LLM assistance — please verify any finding before acting on it.
   
   Out of 99 queries: **80 match the spec semantically. 19 diverge; 10 of those look high-risk** (wrong column in an aggregate, wrong aggregate function, missing/extra projection column, or different ORDER BY key). The remaining 9 are DataFusion-specific workarounds that look acceptable but are noted at the bottom for completeness.
   
   ### High-risk findings (wrong column / wrong aggregate vs spec)
   
   | Q | PR | Spec |
   |---|----|------|
   | 1 | `sum(sr_return_amt)` | `sum(sr_fee)` |
   | 4 | final SELECT/ORDER BY uses `customer_preferred_cust_flag` | uses `customer_email_address` |
   | 9 | `avg(ss_net_paid)` (×5 CASE branches) | `avg(ss_net_profit)` |
   | 11 | same as Q4 | |
   | 14 | second statement uses `SELECT *` over join with `_2`-suffixed aliases | explicit `ty_*`/`ly_*` aliased projection |
   | 24 | `sum(ss_net_paid) netpaid` (×2) | `sum(ss_sales_price) netpaid` |
   | 35 | per group: `min(x), max(x), avg(x)` | `avg(x), max(x), sum(x)` (with `aggone/two/three` aliases) |
   | 45 | `ca_city` in projection, GROUP BY, ORDER BY | `ca_county` |
   | 47 | v2 CTE adds `i_category, i_brand`; ORDER BY positional `3` | smaller v2; ORDER BY `nsum` by name |
   | 66 | web aggregates over `ws_ext_sales_price`/`ws_net_paid`; catalog over `cs_sales_price`/`cs_net_paid_inc_tax` (×48 expressions) | web: `ws_sales_price`/`ws_net_paid_inc_tax`; catalog: `cs_ext_sales_price`/`cs_net_paid_inc_ship_tax`. Looks like the web/catalog column families got swapped. |
   | 74 | `sum(*_net_paid) year_total` (×2) | `max(*_net_paid) year_total` |
   
   ### Medium risk — ORDER BY tie-breaker dropped
   
   - **Q6**: `ORDER BY cnt` (spec: `cnt, a.ca_state`)
   - **Q31**: `ORDER BY ss1.ca_county` (spec: `ss1.d_year`)
   - **Q56**: `ORDER BY total_sales` (spec: `total_sales, i_item_id`)
   - **Q75**: `ORDER BY sales_cnt_diff` (spec: `sales_cnt_diff, sales_amt_diff`)
   - **Q57**: v2 CTE has extra `i_category, i_brand` that leak through `SELECT *`
   
   ### DataFusion-specific workarounds (noted for completeness, look acceptable)
   
   - **Q39, Q64**: `… AS x_2` aliases added to dodge duplicate output column names
   - **Q90**: subquery alias `at` → `at1` (reserved-word workaround)
   - **Q2**: alias `x` added to inner UNION subquery (parser requirement)
   
   Q44 and Q49 (the two the PR description calls out as spec-conformance fixes) verified clean.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [PR] Cleanup TPC-DS queries [datafusion]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3318743696


##########
datafusion/core/tests/tpc-ds/74.sql:
##########
@@ -1,62 +1,65 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with year_total as (
- select c_customer_id customer_id
-       ,c_first_name customer_first_name
-       ,c_last_name customer_last_name
-       ,d_year as year
-       ,max(ss_net_paid) year_total
-       ,'s' sale_type
- from customer
-     ,store_sales
-     ,date_dim
- where c_customer_sk = ss_customer_sk
-   and ss_sold_date_sk = d_date_sk
-   and d_year in (2001,2001+1)
- group by c_customer_id
-         ,c_first_name
-         ,c_last_name
-         ,d_year
- union all
- select c_customer_id customer_id
-       ,c_first_name customer_first_name
-       ,c_last_name customer_last_name
-       ,d_year as year
-       ,max(ws_net_paid) year_total
-       ,'w' sale_type
- from customer
-     ,web_sales
-     ,date_dim
- where c_customer_sk = ws_bill_customer_sk
-   and ws_sold_date_sk = d_date_sk
-   and d_year in (2001,2001+1)
- group by c_customer_id
-         ,c_first_name
-         ,c_last_name
-         ,d_year
-         )
-  select 
-        t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name
- from year_total t_s_firstyear
-     ,year_total t_s_secyear
-     ,year_total t_w_firstyear
-     ,year_total t_w_secyear
- where t_s_secyear.customer_id = t_s_firstyear.customer_id
-         and t_s_firstyear.customer_id = t_w_secyear.customer_id
-         and t_s_firstyear.customer_id = t_w_firstyear.customer_id
-         and t_s_firstyear.sale_type = 's'
-         and t_w_firstyear.sale_type = 'w'
-         and t_s_secyear.sale_type = 's'
-         and t_w_secyear.sale_type = 'w'
-         and t_s_firstyear.year = 2001
-         and t_s_secyear.year = 2001+1
-         and t_w_firstyear.year = 2001
-         and t_w_secyear.year = 2001+1
-         and t_s_firstyear.year_total > 0
-         and t_w_firstyear.year_total > 0
-         and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
-           > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
- order by 2,1,3
-limit 100;
-
-
+WITH
+    year_total AS (
+        SELECT
+            c_customer_id customer_id,
+            c_first_name customer_first_name,
+            c_last_name customer_last_name,
+            d_year AS year,
+            sum(ss_net_paid) year_total,

Review Comment:
   Is it intentional that this changed from `max` to `sum`?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org