TPC-DS queries

Started by Tatsuo Ishiiabout 7 years ago4 messagesgeneral

t-ishii@sra.co.jp

about 7 years ago

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

Noname

reg_pg_stefanz@perfexpert.ch

about 7 years ago

In reply to: Tatsuo Ishii (#1)

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.

Show quoted text

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

Tatsuo Ishii

t-ishii@sra.co.jp

about 7 years ago

In reply to: Noname (#2)

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.

Thanks. Yes, you are correct. The line should be something like:

,case when grouping(i_category)+grouping(i_class) = 0 then i_category end

Show quoted text

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

Mark Johnson

remi9898@gmail.com

about 7 years ago

In reply to: Tatsuo Ishii (#3)

Re: TPC-DS queries

I found this error in queries generated from templates query36.tpl,
query70.tpl, and query86.tpl. The problem is, lochierarchy is an alias
defined in the SELECT statement, and the alias isn't being recognized in
the CASE statement. PostgreSQL does not allow a column alias to be
referenced in a CASE statement, you have to use the actual column name.
Modify each of the queries throwing errors, and replace the lochierarchy
alias with the actual column name you see in the SELECT statement.
-Mark

On Mon, Mar 11, 2019 at 4:00 AM Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

Show quoted text

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.

Thanks. Yes, you are correct. The line should be something like:

,case when grouping(i_category)+grouping(i_class) = 0 then i_category
end

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