Re: TPC-DS queries

Hi,

I think that the sql is not valid. Based on the order by documentation,
a column label cannot be used in an expression.

from    https://www.postgresql.org/docs/11/queries-order.html
     > Note that an output column name has to stand alone, that is, it
cannot be used in an expression.

Regards
s.

On 11.03.2019 06:30, Tatsuo Ishii wrote:
> I played with TPC-DS and found some of them can't be executed because
> of SQL errors and I am not sure why.
>
> For example with query 36:
>
> select
> sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
> ,i_category
> ,i_class
> ,grouping(i_category)+grouping(i_class) as lochierarchy
> ,rank() over (
> partition by grouping(i_category)+grouping(i_class),
> case when grouping(i_class) = 0 then i_category end
> order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent
> from
> store_sales
> ,date_dim d1
> ,item
> ,store
> where
> d1.d_year = 2000
> and d1.d_date_sk = ss_sold_date_sk
> and i_item_sk = ss_item_sk
> and s_store_sk = ss_store_sk
> and s_state in ('TN','TN','TN','TN',
> 'TN','TN','TN','TN')
> group by rollup(i_category,i_class)
> order by
> lochierarchy desc
> ,case when lochierarchy = 0 then i_category end -- line 25 is here.
> ,rank_within_parent
> limit 100;
> psql:query_0.sql:1935: ERROR: column "lochierarchy" does not exist
> LINE 25: ,case when lochierarchy = 0 then i_category end
>
> I have follwed the instruction here.
> https://ankane.org/tpc-ds
>
> PostgreSQL is master branch HEAD. For me, the SQL above looks to be
> valid.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>