http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b90dc334/core/sql/regress/compGeneral/EXPECTED045 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED045 b/core/sql/regress/compGeneral/EXPECTED045 new file mode 100644 index 0000000..5aa6304 --- /dev/null +++ b/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. + +--- SQL command prepared. +>>execute show_cses; + +--- 0 row(s) selected. +>>execute s; + +CHANNEL I_BRAND_ID I_CLASS_ID I_CATEGORY_ID SUM_SALES NUMBER_SALES +------- ----------- ----------- ------------- -------------------- -------------------- + +? ? ? ? ? ? + +--- SQL operation complete. +>> +>>--QID: 23a +>> +>>prepare s from ++> with frequent_ss_items as ++> (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt ++> from store_sales ++> ,date_dim ++> ,item ++> where ss_sold_date_sk = d_date_sk ++> and ss_item_sk = i_item_sk ++> and d_year in (2000,2000+1,2000+2,2000+3) ++> group by substr(i_item_desc,1,30),i_item_sk,d_date ++> having count(*) >4), ++> max_store_sales as ++> (select max(csales) tpcds_cmax ++> from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales ++> from store_sales ++> ,customer ++> ,date_dim ++> where ss_customer_sk = c_customer_sk ++> and ss_sold_date_sk = d_date_sk ++> and d_year in (2000,2000+1,2000+2,2000+3) ++> group by c_customer_sk)), ++> best_ss_customer as ++> (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales ++> from store_sales ++> ,customer ++> where ss_customer_sk = c_customer_sk ++> group by c_customer_sk ++> having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select ++> * ++>from ++> max_store_sales)) ++> select sum(sales) ++> from (select cs_quantity*cs_list_price sales ++> from catalog_sales ++> ,date_dim ++> where d_year = 2000 ++> and d_moy = 6 ++> and cs_sold_date_sk = d_date_sk ++> and cs_item_sk in (select item_sk from frequent_ss_items) ++> and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) ++> union all ++> select ws_quantity*ws_list_price sales ++> from web_sales ++> ,date_dim ++> where d_year = 2000 ++> and d_moy = 6 ++> and ws_sold_date_sk = d_date_sk ++> and ws_item_sk in (select item_sk from frequent_ss_items) ++> and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) ++>limit 100; + +*** 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[5001] Common subexpression FREQUENT_SS_ITEMS cannot be shared among multiple consumers. Reason: Differing inputs in CTE references, try CQD QUERY_CACHE '0'. + +*** WARNING[5001] Common subexpression BEST_SS_CUSTOMER cannot be shared among multiple consumers. Reason: Differing inputs in CTE references, try CQD QUERY_CACHE '0'. + +--- SQL command prepared. +>>execute show_cses; + +OPERATOR HOW_MANY +------------------------------ -------------------- + +BLOCKED_UNION 2 +HIVE_INSERT 1 +SCAN TEMP 2 + +--- 3 row(s) selected. +>>execute s; + +(EXPR) +-------------------- + + ? + +--- SQL operation complete. +>> +>>--QID: 23b +>> +>>prepare s from ++> with frequent_ss_items as ++> (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt ++> from store_sales ++> ,date_dim ++> ,item ++> where ss_sold_date_sk = d_date_sk ++> and ss_item_sk = i_item_sk ++> and d_year in (2000,2000+1,2000+2,2000+3) ++> group by substr(i_item_desc,1,30),i_item_sk,d_date ++> having count(*) >4), ++> max_store_sales as ++> (select max(csales) tpcds_cmax ++> from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales ++> from store_sales ++> ,customer ++> ,date_dim ++> where ss_customer_sk = c_customer_sk ++> and ss_sold_date_sk = d_date_sk ++> and d_year in (2000,2000+1,2000+2,2000+3) ++> group by c_customer_sk)), ++> best_ss_customer as ++> (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales ++> from store_sales ++> ,customer ++> where ss_customer_sk = c_customer_sk ++> group by c_customer_sk ++> having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select ++> * ++> from max_store_sales)) ++> select c_last_name,c_first_name,sales ++> from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales ++> from catalog_sales ++> ,customer ++> ,date_dim ++> where d_year = 2000 ++> and d_moy = 6 ++> and cs_sold_date_sk = d_date_sk ++> and cs_item_sk in (select item_sk from frequent_ss_items) ++> and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) ++> and cs_bill_customer_sk = c_customer_sk ++> group by c_last_name,c_first_name ++> union all ++> select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales ++> from web_sales ++> ,customer ++> ,date_dim ++> where d_year = 2000 ++> and d_moy = 6 ++> and ws_sold_date_sk = d_date_sk ++> and ws_item_sk in (select item_sk from frequent_ss_items) ++> and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer) ++> and ws_bill_customer_sk = c_customer_sk ++> group by c_last_name,c_first_name) ++> order by c_last_name,c_first_name,sales ++>limit 100; + +*** 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[5001] Common subexpression FREQUENT_SS_ITEMS cannot be shared among multiple consumers. Reason: Differing inputs in CTE references, try CQD QUERY_CACHE '0'. + +*** WARNING[5001] Common subexpression BEST_SS_CUSTOMER cannot be shared among multiple consumers. Reason: Differing inputs in CTE references, try CQD QUERY_CACHE '0'. + +--- 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: 24a +>> +>>prepare s from ++>with ssales as ++>(select c_last_name ++> ,c_first_name ++> ,s_store_name ++> ,ca_state ++> ,s_state ++> ,i_color ++> ,i_current_price ++> ,i_manager_id ++> ,i_units ++> ,i_size ++> ,sum(ss_net_paid) netpaid ++>from store_sales ++> ,store_returns ++> ,store ++> ,item ++> ,customer ++> ,customer_address ++>where ss_ticket_number = sr_ticket_number ++> and ss_item_sk = sr_item_sk ++> and ss_customer_sk = c_customer_sk ++> and ss_item_sk = i_item_sk ++> and ss_store_sk = s_store_sk ++> and c_birth_country = upper(ca_country) ++> and s_zip = ca_zip ++> and s_market_id = 10 ++>group by c_last_name ++> ,c_first_name ++> ,s_store_name ++> ,ca_state ++> ,s_state ++> ,i_color ++> ,i_current_price ++> ,i_manager_id ++> ,i_units ++> ,i_size) ++>select c_last_name ++> ,c_first_name ++> ,s_store_name ++> ,sum(netpaid) paid ++>from ssales ++>where i_color = 'smoke' ++>group by c_last_name ++> ,c_first_name ++> ,s_store_name ++>having sum(netpaid) > (select 0.05*avg(netpaid) ++> from ssales) ++>; + +*** WARNING[2997] (Attempting to unnest Subquery) + +*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation found) + +--- 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: 24b +>> +>>prepare s from ++>with ssales as ++>(select c_last_name ++> ,c_first_name ++> ,s_store_name ++> ,ca_state ++> ,s_state ++> ,i_color ++> ,i_current_price ++> ,i_manager_id ++> ,i_units ++> ,i_size ++> ,sum(ss_net_paid) netpaid ++>from store_sales ++> ,store_returns ++> ,store ++> ,item ++> ,customer ++> ,customer_address ++>where ss_ticket_number = sr_ticket_number ++> and ss_item_sk = sr_item_sk ++> and ss_customer_sk = c_customer_sk ++> and ss_item_sk = i_item_sk ++> and ss_store_sk = s_store_sk ++> and c_birth_country = upper(ca_country) ++> and s_zip = ca_zip ++> and s_market_id=10 ++>group by c_last_name ++> ,c_first_name ++> ,s_store_name ++> ,ca_state ++> ,s_state ++> ,i_color ++> ,i_current_price ++> ,i_manager_id ++> ,i_units ++> ,i_size) ++>select c_last_name ++> ,c_first_name ++> ,s_store_name ++> ,sum(netpaid) paid ++>from ssales ++>where i_color = 'pink' ++>group by c_last_name ++> ,c_first_name ++> ,s_store_name ++>having sum(netpaid) > (select 0.05*avg(netpaid) ++> from ssales) ++>; + +*** WARNING[2997] (Attempting to unnest Subquery) + +*** WARNING[2997] (Subquery was not unnested. Reason: No Correlation found) + +--- 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: 30 +>> +>>prepare s from ++> with customer_total_return as ++> (select wr_returning_customer_sk as ctr_customer_sk ++> ,ca_state as ctr_state, ++> sum(wr_return_amt) as ctr_total_return ++> from web_returns ++> ,date_dim ++> ,customer_address ++> where wr_returned_date_sk = d_date_sk ++> and d_year =1999 ++> and wr_returning_addr_sk = ca_address_sk ++> group by wr_returning_customer_sk ++> ,ca_state) ++>select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ++> ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ++> ,c_last_review_date_sk,ctr_total_return ++> from customer_total_return ctr1 ++> ,customer_address ++> ,customer ++> where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 ++> from customer_total_return ctr2 ++> where ctr1.ctr_state = ctr2.ctr_state) ++> and ca_address_sk = c_current_addr_sk ++> and ca_state = 'OK' ++> and ctr1.ctr_customer_sk = c_customer_sk ++> order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ++> ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ++> ,c_last_review_date_sk,ctr_total_return ++>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: 31 +>> +>>prepare s from ++> with ss as ++> (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales ++> from store_sales,date_dim,customer_address ++> where ss_sold_date_sk = d_date_sk ++> and ss_addr_sk=ca_address_sk ++> group by ca_county,d_qoy, d_year), ++> ws as ++> (select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales ++> from web_sales,date_dim,customer_address ++> where ws_sold_date_sk = d_date_sk ++> and ws_bill_addr_sk=ca_address_sk ++> group by ca_county,d_qoy, d_year) ++> select ++> ss1.ca_county ++> ,ss1.d_year ++> ,ws2.web_sales/ws1.web_sales web_q1_q2_increase ++> ,ss2.store_sales/ss1.store_sales store_q1_q2_increase ++> ,ws3.web_sales/ws2.web_sales web_q2_q3_increase ++> ,ss3.store_sales/ss2.store_sales store_q2_q3_increase ++> from ++> ss ss1 ++> ,ss ss2 ++> ,ss ss3 ++> ,ws ws1 ++> ,ws ws2 ++> ,ws ws3 ++> where ++> ss1.d_qoy = 1 ++> and ss1.d_year = 2002 ++> and ss1.ca_county = ss2.ca_county ++> and ss2.d_qoy = 2 ++> and ss2.d_year = 2002 ++> and ss2.ca_county = ss3.ca_county ++> and ss3.d_qoy = 3 ++> and ss3.d_year = 2002 ++> and ss1.ca_county = ws1.ca_county ++> and ws1.d_qoy = 1 ++> and ws1.d_year = 2002 ++> and ws1.ca_county = ws2.ca_county ++> and ws2.d_qoy = 2 ++> and ws2.d_year = 2002 ++> and ws1.ca_county = ws3.ca_county ++> and ws3.d_qoy = 3 ++> and ws3.d_year =2002 ++> and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end ++> > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end ++> and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end ++> > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end ++> order by store_q1_q2_increase; + +*** WARNING[5001] Common subexpression SS cannot be shared among multiple consumers. Reason: Encountered VEGs with different constants in different consumers. + +*** WARNING[5001] Common subexpression WS cannot be shared among multiple consumers. Reason: Encountered VEGs with different constants in different consumers. + +--- SQL command prepared. +>>-- Different constants used in different references of WITH clause - not yet supported +>>execute show_cses; + +--- 0 row(s) selected. +>>execute s; + +--- SQL operation complete. +>>obey TEST045(clnup); +>> +>>drop schema T045_CSES cascade; + +--- SQL operation complete. +>>cqd cse_cleanup_hive_tables 'on'; + +--- SQL operation complete. +>>cleanup obsolete volatile tables; + +--- SQL operation complete. +>> +>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/b90dc334/core/sql/regress/compGeneral/TEST005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/TEST005 b/core/sql/regress/compGeneral/TEST005 index 41b62cd..dbdd710 100755 --- a/core/sql/regress/compGeneral/TEST005 +++ b/core/sql/regress/compGeneral/TEST005 @@ -489,7 +489,6 @@ create materialized view T_MV1 select A.dno,B.eno from t005t02 A full outer join t005t01 B on A.dno = B.dno; -cqd mode_special_4 'on'; with w1 as (select * from witht1), w2 as (select * from w1) select * from w2; @@ -505,7 +504,6 @@ select * from w1; with recursive w1 as (select c1, c2 from witht1 union all select origin.c1 , origin.c2 from w1 join t1 origin on origin.c1 = w1.c1 ); with w1 as (select * from witht1), w1 as (select * from witht2) select * from w1; -cqd mode_special_4 reset; ?section cleanup
