Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/794#discussion_r85456105
--- Diff: core/sql/regress/compGeneral/EXPECTED045 ---
@@ -0,0 +1,1606 @@
+>>obey TEST045(setup);
+>>
+>>-- general setup
+>>cqd hist_missing_stats_warning_level '0';
+
+--- SQL operation complete.
+>>cqd hive_max_string_length '32';
+
+--- SQL operation complete.
+>>cqd mode_special_4 'on';
+
+--- SQL operation complete.
+>>
+>>create schema T045_CSES;
+
+--- SQL operation complete.
+>>set schema T045_CSES;
+
+--- SQL operation complete.
+>>
+>>prepare show_cses from
++>select case when operator in ('BLOCKED_UNION', 'HIVE_INSERT')
++> then operator
++> else 'SCAN TEMP' end as operator,
++> count(*) as how_many
++>from table(explain(null, 'S'))
++>where operator = 'BLOCKED_UNION'
++> or tname like '%CSE_TEMP_%'
++>group by 1
++>order by 1;
+
+--- SQL command prepared.
+>>
+>>obey TEST045(ddl);
+>>--------------------------------------------------------------------
+>>
+>>create table store_sales
++>(
++> ss_sold_date_sk int,
++> ss_sold_time_sk int,
++> ss_item_sk int not null,
++> ss_customer_sk int,
++> ss_cdemo_sk int,
++> ss_hdemo_sk int,
++> ss_addr_sk int,
++> ss_store_sk int,
++> ss_promo_sk int,
++> ss_ticket_number int not null,
++> ss_quantity int,
++> ss_wholesale_cost float,
++> ss_list_price float,
++> ss_sales_price float,
++> ss_ext_discount_amt float,
++> ss_ext_sales_price float,
++> ss_ext_wholesale_cost float,
++> ss_ext_list_price float,
++> ss_ext_tax float,
++> ss_coupon_amt float,
++> ss_net_paid float,
++> ss_net_paid_inc_tax float,
++> ss_net_profit float,
++> primary key(ss_item_sk,ss_ticket_number)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table store_returns
++>(
++> sr_returned_date_sk int,
++> sr_return_time_sk int,
++> sr_item_sk int not null,
++> sr_customer_sk int,
++> sr_cdemo_sk int,
++> sr_hdemo_sk int,
++> sr_addr_sk int,
++> sr_store_sk int,
++> sr_reason_sk int,
++> sr_ticket_number int not null,
++> sr_return_quantity int,
++> sr_return_amt float,
++> sr_return_tax float,
++> sr_return_amt_inc_tax float,
++> sr_fee float,
++> sr_return_ship_cost float,
++> sr_refunded_cash float,
++> sr_reversed_charge float,
++> sr_store_credit float,
++> sr_net_loss float,
++> primary key(sr_item_sk,sr_ticket_number)
++>
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table catalog_sales
++>(
++> cs_sold_date_sk int,
++> cs_sold_time_sk int,
++> cs_ship_date_sk int,
++> cs_bill_customer_sk int,
++> cs_bill_cdemo_sk int,
++> cs_bill_hdemo_sk int,
++> cs_bill_addr_sk int,
++> cs_ship_customer_sk int,
++> cs_ship_cdemo_sk int,
++> cs_ship_hdemo_sk int,
++> cs_ship_addr_sk int,
++> cs_call_center_sk int,
++> cs_catalog_page_sk int,
++> cs_ship_mode_sk int,
++> cs_warehouse_sk int,
++> cs_item_sk int not null,
++> cs_promo_sk int,
++> cs_order_number int not null,
++> cs_quantity int,
++> cs_wholesale_cost float,
++> cs_list_price float,
++> cs_sales_price float,
++> cs_ext_discount_amt float,
++> cs_ext_sales_price float,
++> cs_ext_wholesale_cost float,
++> cs_ext_list_price float,
++> cs_ext_tax float,
++> cs_coupon_amt float,
++> cs_ext_ship_cost float,
++> cs_net_paid float,
++> cs_net_paid_inc_tax float,
++> cs_net_paid_inc_ship float,
++> cs_net_paid_inc_ship_tax float,
++> cs_net_profit float,
++> primary key(cs_item_sk,cs_order_number)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table catalog_returns
++>(
++> cr_returned_date_sk int,
++> cr_returned_time_sk int,
++> cr_item_sk int not null,
++> cr_refunded_customer_sk int,
++> cr_refunded_cdemo_sk int,
++> cr_refunded_hdemo_sk int,
++> cr_refunded_addr_sk int,
++> cr_returning_customer_sk int,
++> cr_returning_cdemo_sk int,
++> cr_returning_hdemo_sk int,
++> cr_returning_addr_sk int,
++> cr_call_center_sk int,
++> cr_catalog_page_sk int,
++> cr_ship_mode_sk int,
++> cr_warehouse_sk int,
++> cr_reason_sk int,
++> cr_order_number int not null,
++> cr_return_quantity int,
++> cr_return_amount float,
++> cr_return_tax float,
++> cr_return_amt_inc_tax float,
++> cr_fee float,
++> cr_return_ship_cost float,
++> cr_refunded_cash float,
++> cr_reversed_charge float,
++> cr_store_credit float,
++> cr_net_loss float,
++>
++> primary key(cr_item_sk,cr_order_number)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table web_sales
++>(
++> ws_sold_date_sk int,
++> ws_sold_time_sk int,
++> ws_ship_date_sk int,
++> ws_item_sk int not null,
++> ws_bill_customer_sk int,
++> ws_bill_cdemo_sk int,
++> ws_bill_hdemo_sk int,
++> ws_bill_addr_sk int,
++> ws_ship_customer_sk int,
++> ws_ship_cdemo_sk int,
++> ws_ship_hdemo_sk int,
++> ws_ship_addr_sk int,
++> ws_web_page_sk int,
++> ws_web_site_sk int,
++> ws_ship_mode_sk int,
++> ws_warehouse_sk int,
++> ws_promo_sk int,
++> ws_order_number int not null,
++> ws_quantity int,
++> ws_wholesale_cost float,
++> ws_list_price float,
++> ws_sales_price float,
++> ws_ext_discount_amt float,
++> ws_ext_sales_price float,
++> ws_ext_wholesale_cost float,
++> ws_ext_list_price float,
++> ws_ext_tax float,
++> ws_coupon_amt float,
++> ws_ext_ship_cost float,
++> ws_net_paid float,
++> ws_net_paid_inc_tax float,
++> ws_net_paid_inc_ship float,
++> ws_net_paid_inc_ship_tax float,
++> ws_net_profit float,
++>
++> primary key (ws_item_sk,ws_order_number)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table web_returns
++>(
++> wr_returned_date_sk int,
++> wr_returned_time_sk int,
++> wr_item_sk int not null,
++> wr_refunded_customer_sk int,
++> wr_refunded_cdemo_sk int,
++> wr_refunded_hdemo_sk int,
++> wr_refunded_addr_sk int,
++> wr_returning_customer_sk int,
++> wr_returning_cdemo_sk int,
++> wr_returning_hdemo_sk int,
++> wr_returning_addr_sk int,
++> wr_web_page_sk int,
++> wr_reason_sk int,
++> wr_order_number int not null,
++> wr_return_quantity int,
++> wr_return_amt float,
++> wr_return_tax float,
++> wr_return_amt_inc_tax float,
++> wr_fee float,
++> wr_return_ship_cost float,
++> wr_refunded_cash float,
++> wr_reversed_charge float,
++> wr_account_credit float,
++> wr_net_loss float,
++> primary key ( wr_order_number, wr_item_sk )
++>
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table store
++>(
++> s_store_sk int not null,
++> s_store_id char(16),
++> s_rec_start_date date,
++> s_rec_end_date date,
++> s_closed_date_sk int,
++> s_store_name varchar(50),
++> s_number_employees int,
++> s_floor_space int,
++> s_hours char(20),
++> S_manager varchar(40),
++> S_market_id int,
++> S_geography_class varchar(100),
++> S_market_desc varchar(100),
++> s_market_manager varchar(40),
++> s_division_id int,
++> s_division_name varchar(50),
++> s_company_id int,
++> s_company_name varchar(50),
++> s_street_number varchar(10),
++> s_street_name varchar(60),
++> s_street_type char(15),
++> s_suite_number char(10), --fix bug
++> s_city varchar(60),
++> s_county varchar(30),
++> s_state char(2),
++> s_zip char(10),
++> s_country varchar(20),
++> s_gmt_offset float,
++> s_tax_percentage float,
++>
++> primary key(s_store_sk)
++>
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table Customer
++>(
++> c_customer_sk int not null,
++> c_customer_id char(16) CHARACTER SET UTF8 not
null,
++> c_current_cdemo_sk int,
++> c_current_hdemo_sk int,
++> c_current_addr_sk int,
++> c_first_shipto_date_sk int,
++> c_first_sales_date_sk int,
++> c_salutation char(10) CHARACTER SET UTF8,
++> c_first_name char(20) CHARACTER SET UTF8,
++> c_last_name char(30) CHARACTER SET UTF8,
++> c_preferred_cust_flag char(1),
++> c_birth_day integer,
++> c_birth_month integer,
++> c_birth_year integer,
++> c_birth_country varchar(20) CHARACTER SET UTF8,
++> c_login char(13) CHARACTER SET UTF8,
++> c_email_address char(50) CHARACTER SET UTF8,
++> c_last_review_date_sk int,
++> primary key (c_customer_sk)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table Customer_address
++>(
++> ca_address_sk int not null,
++> ca_address_id char(16) not null,
++> ca_street_number char(10),
++> ca_street_name varchar(60),
++> ca_street_type char(15),
++> ca_suite_number char(10),
++> ca_city varchar(60),
++> ca_county varchar(30),
++> ca_state char(2),
++> ca_zip char(10),
++> ca_country varchar(20),
++> ca_gmt_offset decimal(5,2),
++> ca_location_type char(20),
++> primary key (ca_address_sk)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table Date_dim
++>(
++> d_date_sk int not null,
++> d_date_id char(16) not null,
++> d_date date,
++> d_month_seq integer,
++> d_week_seq integer,
++> d_quarter_seq integer,
++> d_year integer,
++> d_dow integer,
++> d_moy integer,
++> d_dom integer,
++> d_qoy integer,
++> d_fy_year integer,
++> d_fy_quarter_seq integer,
++> d_fy_week_seq integer,
++> d_day_name char(9),
++> d_quarter_name char(6),
++> d_holiday char(1),
++> d_weekend char(1),
++> d_following_holiday char(1),
++> d_first_dom integer,
++> d_last_dom integer,
++> d_same_day_ly integer,
++> d_same_day_lq integer,
++> d_current_day char(1),
++> d_current_week char(1),
++> d_current_month char(1),
++> d_current_quarter char(1),
++> d_current_year char(1),
++> primary key (d_date_sk)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>create table item
++>(
++> i_item_sk int not null,
++> i_item_id char(16) not null,
++> i_rec_start_date date,
++> i_rec_end_date date,
++> i_item_desc varchar(200),
++> i_current_price decimal(7,2),
++> i_wholesale_cost decimal(7,2),
++> i_brand_id integer,
++> i_brand char(50),
++> i_class_id integer,
++> i_class char(50),
++> i_category_id integer,
++> i_category char(50),
++> i_manufact_id integer,
++> i_manufact char(50),
++> i_size char(20),
++> i_formulation char(20),
++> i_color char(20),
++> i_units char(10),
++> i_container char(10),
++> i_manager_id integer,
++> i_product_name char(50),
++> primary key(i_item_sk)
++>)
++>--SALT USING $PARTITION_NUM PARTITIONS
++>-- HBASE_OPTIONS
++>-- (
++>-- DATA_BLOCK_ENCODING = 'FAST_DIFF',
++>-- COMPRESSION = 'SNAPPY'
++>-- )
++>;
+
+--- SQL operation complete.
+>>
+>>
+>>--------------------------------------------------------------------
+>>obey TEST045(queries);
+>>--------------------------------------------------------------------
+>>
+>>obey TEST045(enable_cses);
+>>cqd cse_for_with 'on';
+
+--- SQL operation complete.
+>>cqd cse_use_temp 'on';
+
+--- SQL operation complete.
+>>cqd cse_hive_temp_table 'on';
+
+--- SQL operation complete.
+>>cqd cse_debug_warnings 'on';
+
+--- SQL operation complete.
+>>
+>>
+>>--------------------------------------------------------------------
+>>-- test some queries on populated Hive tables that we already have
+>>--------------------------------------------------------------------
+>>
+>>set schema hive.hive;
+
+--- SQL operation complete.
+>>
+>>prepare s from
++>with cse1 as (select count(*) from date_dim where d_dow = ?)
++>select * from cse1
++>union all
++>select * from cse1;
+
+--- SQL command prepared.
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 2
+
+--- 3 row(s) selected.
+>>execute s using 1;
+
+(EXPR)
+--------------------
+
+ 10436
+ 10436
+
+--- SQL operation complete.
+>>-- execute a second time
+>>execute s using 2;
+
+(EXPR)
+--------------------
+
+ 10436
+ 10436
+
+--- SQL operation complete.
+>>
+>>-- prepare a second time, so far not using query cache
+>>prepare s from
++>with cse1 as (select count(*) from date_dim where d_dow = ?)
++>select * from cse1
++>union all
++>select * from cse1;
+
+--- SQL command prepared.
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 2
+
+--- 3 row(s) selected.
+>>execute s using 3;
+
+(EXPR)
+--------------------
+
+ 10435
+ 10435
+
+--- SQL operation complete.
+>>-- execute a second time
+>>execute s using 4;
+
+(EXPR)
+--------------------
+
+ 10437
+ 10437
+
+--- SQL operation complete.
+>>
+>>-- test subquery unnesting
+>>cqd subquery_unnesting 'debug';
+
+--- SQL operation complete.
+>>
+>>prepare s from
++>with cse1 as (select * from date_dim)
++>select count(*)
++>from cse1 x
++>where d_date_sk > (select avg(d_date_sk) from cse1 y where x.d_moy >
y.d_moy);
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: Left subtree cannot
produce output values required for grouping.)
+
+--- SQL command prepared.
+>>-- currently not unnested
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 2
+
+--- 3 row(s) selected.
+>>
+>>--------------------------------------------------------------------
+>>-- test some TPC-DS queries
+>>--------------------------------------------------------------------
+>>
+>>set schema trafodion.t045_cses;
+
+--- SQL operation complete.
+>>
+>>--QID: 1
+>>
+>>prepare s from
++>with customer_total_return as
++> (select sr_customer_sk as ctr_customer_sk
++> ,sr_store_sk as ctr_store_sk
++> ,sum(SR_REVERSED_CHARGE) 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;
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+--- SQL command prepared.
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 2
+
+--- 3 row(s) selected.
+>>execute s;
+
+--- SQL operation complete.
+>>
+>>
+>>--QID: 2
+>>
+>>prepare s from
++> with wscs as
++> (select sold_date_sk
++> ,sales_price
++> from (select ws_sold_date_sk sold_date_sk
++> ,ws_ext_sales_price sales_price
++> from web_sales
++> union all
++> select cs_sold_date_sk sold_date_sk
++> ,cs_ext_sales_price sales_price
++> from catalog_sales) t),
++> wswscs as
++> (select d_week_seq,
++> sum(case when (d_day_name='Sunday') then sales_price else null
end) sun_sales,
++> sum(case when (d_day_name='Monday') then sales_price else null
end) mon_sales,
++> sum(case when (d_day_name='Tuesday') then sales_price else null
end) tue_sales,
++> sum(case when (d_day_name='Wednesday') then sales_price else
null end) wed_sales,
++> sum(case when (d_day_name='Thursday') then sales_price else null
end) thu_sales,
++> sum(case when (d_day_name='Friday') then sales_price else null
end) fri_sales,
++> sum(case when (d_day_name='Saturday') then sales_price else null
end) sat_sales
++> from wscs
++> ,date_dim
++> where d_date_sk = sold_date_sk
++> group by d_week_seq)
++> select d_week_seq1
++> ,round(sun_sales1/sun_sales2,2)
++> ,round(mon_sales1/mon_sales2,2)
++> ,round(tue_sales1/tue_sales2,2)
++> ,round(wed_sales1/wed_sales2,2)
++> ,round(thu_sales1/thu_sales2,2)
++> ,round(fri_sales1/fri_sales2,2)
++> ,round(sat_sales1/sat_sales2,2)
++> from
++> (select wswscs.d_week_seq d_week_seq1
++> ,sun_sales sun_sales1
++> ,mon_sales mon_sales1
++> ,tue_sales tue_sales1
++> ,wed_sales wed_sales1
++> ,thu_sales thu_sales1
++> ,fri_sales fri_sales1
++> ,sat_sales sat_sales1
++> from wswscs,date_dim
++> where date_dim.d_week_seq = wswscs.d_week_seq and
++> d_year = 1999) y,
++> (select wswscs.d_week_seq d_week_seq2
++> ,sun_sales sun_sales2
++> ,mon_sales mon_sales2
++> ,tue_sales tue_sales2
++> ,wed_sales wed_sales2
++> ,thu_sales thu_sales2
++> ,fri_sales fri_sales2
++> ,sat_sales sat_sales2
++> from wswscs
++> ,date_dim
++> where date_dim.d_week_seq = wswscs.d_week_seq and
++> d_year = 1999+1) z
++> where d_week_seq1=d_week_seq2-53
++> order by d_week_seq1;
+
+*** WARNING[5001] Common subexpression WSWSCS cannot be shared among
multiple consumers. Reason: Operator map_value_ids not supported.
+
+--- SQL command prepared.
+>>-- use temp for wscs only, not wswscs, due to MapValueIds
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 2
+
+--- 3 row(s) selected.
+>>execute s;
+
+--- SQL operation complete.
+>>
+>>
+>>--QID: 4
+>>
+>>prepare s from
++>with year_total as (
++> select c_customer_id customer_id
++> ,c_first_name customer_first_name
++> ,c_last_name customer_last_name
++> ,c_preferred_cust_flag customer_preferred_cust_flag
++> ,c_birth_country customer_birth_country
++> ,c_login customer_login
++> ,c_email_address customer_email_address
++> ,d_year dyear
++>
,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2)
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
++> group by c_customer_id
++> ,c_first_name
++> ,c_last_name
++> ,c_preferred_cust_flag
++> ,c_birth_country
++> ,c_login
++> ,c_email_address
++> ,d_year
++> union all
++> select c_customer_id customer_id
++> ,c_first_name customer_first_name
++> ,c_last_name customer_last_name
++> ,c_preferred_cust_flag customer_preferred_cust_flag
++> ,c_birth_country customer_birth_country
++> ,c_login customer_login
++> ,c_email_address customer_email_address
++> ,d_year dyear
++>
,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2)
) year_total
++> ,'c' sale_type
++> from customer
++> ,catalog_sales
++> ,date_dim
++> where c_customer_sk = cs_bill_customer_sk
++> and cs_sold_date_sk = d_date_sk
++> group by c_customer_id
++> ,c_first_name
++> ,c_last_name
++> ,c_preferred_cust_flag
++> ,c_birth_country
++> ,c_login
++> ,c_email_address
++> ,d_year
++>union all
++> select c_customer_id customer_id
++> ,c_first_name customer_first_name
++> ,c_last_name customer_last_name
++> ,c_preferred_cust_flag customer_preferred_cust_flag
++> ,c_birth_country customer_birth_country
++> ,c_login customer_login
++> ,c_email_address customer_email_address
++> ,d_year dyear
++>
,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2)
) 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
++> group by c_customer_id
++> ,c_first_name
++> ,c_last_name
++> ,c_preferred_cust_flag
++> ,c_birth_country
++> ,c_login
++> ,c_email_address
++> ,d_year
++> )
++>select
++> t_s_secyear.customer_id
++> ,t_s_secyear.customer_first_name
++> ,t_s_secyear.customer_last_name
++> ,t_s_secyear.customer_birth_country
++> from year_total t_s_firstyear
++> ,year_total t_s_secyear
++> ,year_total t_c_firstyear
++> ,year_total t_c_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_c_secyear.customer_id
++> and t_s_firstyear.customer_id = t_c_firstyear.customer_id
++> and t_s_firstyear.customer_id = t_w_firstyear.customer_id
++> and t_s_firstyear.customer_id = t_w_secyear.customer_id
++> and t_s_firstyear.sale_type = 's'
++> and t_c_firstyear.sale_type = 'c'
++> and t_w_firstyear.sale_type = 'w'
++> and t_s_secyear.sale_type = 's'
++> and t_c_secyear.sale_type = 'c'
++> and t_w_secyear.sale_type = 'w'
++> and t_s_firstyear.dyear = 1998
++> and t_s_secyear.dyear = 1998+1
++> and t_c_firstyear.dyear = 1998
++> and t_c_secyear.dyear = 1998+1
++> and t_w_firstyear.dyear = 1998
++> and t_w_secyear.dyear = 1998+1
++> and t_s_firstyear.year_total > 0
++> and t_c_firstyear.year_total > 0
++> and t_w_firstyear.year_total > 0
++> and case when t_c_firstyear.year_total > 0 then
t_c_secyear.year_total / t_c_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
++> and case when t_c_firstyear.year_total > 0 then
t_c_secyear.year_total / t_c_firstyear.year_total else null end
++> > case when t_w_firstyear.year_total > 0 then
t_w_secyear.year_total / t_w_firstyear.year_total else null end
++> order by t_s_secyear.customer_id
++> ,t_s_secyear.customer_first_name
++> ,t_s_secyear.customer_last_name
++> ,t_s_secyear.customer_birth_country
++>limit 100;
+
+--- SQL command prepared.
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 6
+
+--- 3 row(s) selected.
+>>execute s;
+
+--- SQL operation complete.
+>>
+>>
+>>--QID: 11
+>>
+>>prepare s from
++>with year_total as (
++> select c_customer_id customer_id
++> ,c_first_name customer_first_name
++> ,c_last_name customer_last_name
++> ,c_preferred_cust_flag customer_preferred_cust_flag
++> ,c_birth_country customer_birth_country
++> ,c_login customer_login
++> ,c_email_address customer_email_address
++> ,d_year dyear
++> ,sum(ss_ext_list_price-ss_ext_discount_amt) 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
++> group by c_customer_id
++> ,c_first_name
++> ,c_last_name
++> ,c_preferred_cust_flag
++> ,c_birth_country
++> ,c_login
++> ,c_email_address
++> ,d_year
++> union all
++> select c_customer_id customer_id
++> ,c_first_name customer_first_name
++> ,c_last_name customer_last_name
++> ,c_preferred_cust_flag customer_preferred_cust_flag
++> ,c_birth_country customer_birth_country
++> ,c_login customer_login
++> ,c_email_address customer_email_address
++> ,d_year dyear
++> ,sum(ws_ext_list_price-ws_ext_discount_amt) 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
++> group by c_customer_id
++> ,c_first_name
++> ,c_last_name
++> ,c_preferred_cust_flag
++> ,c_birth_country
++> ,c_login
++> ,c_email_address
++> ,d_year
++> )
++>select
++> t_s_secyear.customer_id
++> ,t_s_secyear.customer_first_name
++> ,t_s_secyear.customer_last_name
++> ,t_s_secyear.customer_preferred_cust_flag
++> 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.dyear = 2001
++> and t_s_secyear.dyear = 2001+1
++> and t_w_firstyear.dyear = 2001
++> and t_w_secyear.dyear = 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 0.0 end
++> > case when t_s_firstyear.year_total > 0 then
t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end
++> order by t_s_secyear.customer_id
++> ,t_s_secyear.customer_first_name
++> ,t_s_secyear.customer_last_name
++> ,t_s_secyear.customer_preferred_cust_flag
++>limit 100;
+
+--- SQL command prepared.
+>>execute show_cses;
+
+OPERATOR HOW_MANY
+------------------------------ --------------------
+
+BLOCKED_UNION 2
+HIVE_INSERT 1
+SCAN TEMP 4
+
+--- 3 row(s) selected.
+>>execute s;
+
+--- SQL operation complete.
+>>
+>>--QID: 14a
+>>
+>>prepare s from
++>-- **************
++>-- VARIANT/ALTERNATIVE Version from TPC-DS (Rollout) is used
++>-- **************
++>
++>-- NOTE: THIS QUERY HAS A COMPILER ISSUE. See Mantis 1048
++>-- BOTH MULTI-QUERIES have the same issue. ******************
++>
++>with cross_items as
++> (select i_item_sk ss_item_sk
++> from item,
++> (select iss.i_brand_id brand_id
++> ,iss.i_class_id class_id
++> ,iss.i_category_id category_id
++> from store_sales
++> ,item iss
++> ,date_dim d1
++> where ss_item_sk = iss.i_item_sk
++> and ss_sold_date_sk = d1.d_date_sk
++> and d1.d_year between 1999 AND 1999 + 2
++> intersect
++> select ics.i_brand_id
++> ,ics.i_class_id
++> ,ics.i_category_id
++> from catalog_sales
++> ,item ics
++> ,date_dim d2
++> where cs_item_sk = ics.i_item_sk
++> and cs_sold_date_sk = d2.d_date_sk
++> and d2.d_year between 1999 AND 1999 + 2
++> intersect
++> select iws.i_brand_id
++> ,iws.i_class_id
++> ,iws.i_category_id
++> from web_sales
++> ,item iws
++> ,date_dim d3
++> where ws_item_sk = iws.i_item_sk
++> and ws_sold_date_sk = d3.d_date_sk
++> and d3.d_year between 1999 AND 1999 + 2) x
++> where i_brand_id = brand_id
++> and i_class_id = class_id
++> and i_category_id = category_id
++>)
++>,
++> avg_sales as
++> (select avg(quantity*list_price) average_sales
++> from (select ss_quantity quantity
++> ,ss_list_price list_price
++> from store_sales
++> ,date_dim
++> where ss_sold_date_sk = d_date_sk
++> and d_year between 1999 and 2001
++> union all
++> select cs_quantity quantity
++> ,cs_list_price list_price
++> from catalog_sales
++> ,date_dim
++> where cs_sold_date_sk = d_date_sk
++> and d_year between 1999 AND 1999 + 2
++> union all
++> select ws_quantity quantity
++> ,ws_list_price list_price
++> from web_sales
++> ,date_dim
++> where ws_sold_date_sk = d_date_sk
++> and d_year between 1999 AND 1999 + 2) x)
++>,
++> results AS
++>(select channel, i_brand_id, i_class_id, i_category_id, sum(sales)
sum_sales, sum(number_sales) number_sales
++> from (
++> select 'store' channel, i_brand_id,i_class_id
++> ,i_category_id,sum(ss_quantity*ss_list_price) sales
++> , count(*) number_sales
++> from store_sales
++> ,item
++> ,date_dim
++> where ss_item_sk in (select ss_item_sk from cross_items)
++> and ss_item_sk = i_item_sk
++> and ss_sold_date_sk = d_date_sk
++> and d_year = 1999 + 2
++> and d_moy = 11
++> group by i_brand_id,i_class_id,i_category_id
++> having sum(ss_quantity*ss_list_price) > (select average_sales
from avg_sales)
++> union all
++> select 'catalog' channel, i_brand_id,i_class_id,i_category_id,
sum(cs_quantity*cs_list_price) sales, count(*) number_sales
++> from catalog_sales
++> ,item
++> ,date_dim
++> where cs_item_sk in (select ss_item_sk from cross_items)
++> and cs_item_sk = i_item_sk
++> and cs_sold_date_sk = d_date_sk
++> and d_year = 1999 + 2
++> and d_moy = 11
++> group by i_brand_id,i_class_id,i_category_id
++> having sum(cs_quantity*cs_list_price) > (select average_sales
from avg_sales)
++> union all
++> select 'web' channel, i_brand_id,i_class_id,i_category_id,
sum(ws_quantity*ws_list_price) sales , count(*) number_sales
++> from web_sales
++> ,item
++> ,date_dim
++> where ws_item_sk in (select ss_item_sk from cross_items)
++> and ws_item_sk = i_item_sk
++> and ws_sold_date_sk = d_date_sk
++> and d_year = 1999 + 2
++> and d_moy = 11
++> group by i_brand_id,i_class_id,i_category_id
++> having sum(ws_quantity*ws_list_price) > (select average_sales
from avg_sales)
++> ) y
++> group by channel, i_brand_id,i_class_id,i_category_id)
++>
++>select channel, i_brand_id, i_class_id, i_category_id, sum_sales,
number_sales
++>from (
++> select channel, i_brand_id, i_class_id, i_category_id, sum_sales,
number_sales from results
++> union
++> select channel, i_brand_id, i_class_id, null as i_category_id,
sum(sum_sales), sum(number_sales) from results
++> group by channel, i_brand_id, i_class_id
++> union
++> select channel, i_brand_id, null as i_class_id, null as
i_category_id, sum(sum_sales), sum(number_sales) from results
++> group by channel, i_brand_id
++> union
++> select channel, null as i_brand_id, null as i_class_id, null as
i_category_id, sum(sum_sales), sum(number_sales) from results
++> group by channel
++> union
++> select null as channel, null as i_brand_id, null as i_class_id,
null as i_category_id, sum(sum_sales), sum(number_sales) from results) z
++>order by channel, i_brand_id, i_class_id, i_category_id
++> limit 100 ;
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Attempting to unnest Subquery)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation
found)
+
+*** WARNING[1214] Error Error in creating temp table or temp table insert
encountered when executing HiveQL statement ../optimizer/NormRelExpr.cpp.
--- End diff --
Wow, you win three bonus points :-)
The first for reviewing the expected file, the second for finding a bug
with ComDiagsArea::negateAllErrors(). The error message and the parameters are
mixed up here, the text belongs to another error entry.
Third bonus point for pointing out another issue with the Hive table - Hive
doesn't support DECIMAL data types with precision greater than 38, and in this
case we want a DECIMAL(57,19).
So, thanks very much for pointing out 3 issues-in-1!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---
