>>obey TEST045(setup);
>>
>>-- general setup
>>cqd hist_missing_stats_warning_level '0';

--- SQL operation complete.
>>cqd hive_max_string_length_in_bytes '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.
>>
>>insert into date_dim values (
+>  1, '1', date '2000-01-01', 1, 1, 1, 2000, 1, 1, 1, 1, 2000, 1, 1, 'aday', 'aq', ' ', ' ', ' ', 1, 31, 0, 0, ' ', ' ', ' ', ' ', ' '
+>);

--- 1 row(s) inserted.
>>
>>insert into store_sales values (
+>  1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1.0, 1.0, 1.0, 0.0, 1.0, 1.0, 1.0, 0.0, 0.0, 1.0, 1.0, 0.0
+>);

--- 1 row(s) inserted.
>>
>>update statistics for table date_dim on every column;

--- SQL operation complete.
>>update statistics for table date_dim on (d_qoy, d_year);

*** WARNING[9202] UPDATE STATISTICS has located previously generated histograms that are not being regenerated. This may affect the plans that will be generated. Missing columns lists are (D_CURRENT_YEAR),(D_CURRENT_QUARTER),(D_CURRENT_MONTH),(D_CURRENT_WEEK),(D_CURRENT_DAY),(D_SAME_DAY_LQ),(D_SAME_DAY_LY),(D_LAST_DOM),(D_FIRST_DOM),(D_FOLLOWING_HOLIDAY),(D_WEEKEND),(D_HOLIDAY),(D_QUARTER_NAME),(D_DAY_NAME),(D_FY_WEEK_SEQ),(D_FY_QUARTER_SEQ),(D_FY_YEAR),(D_DOM),(D_MOY),(D_DOW),(D_QUARTER_SEQ),(D_WEEK_SEQ),(D_MONTH_SEQ),(D_DATE),(D_DATE_ID),(D_DATE_SK).

--- SQL operation completed with warnings.
>>update statistics for table store_sales on every column;

--- 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

--- 2 row(s) selected.
>>-- execute a second time
>>execute s using 2;

(EXPR)              
--------------------

               10436
               10436

--- 2 row(s) selected.
>>
>>-- 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

--- 2 row(s) selected.
>>-- execute a second time
>>execute s using 4;

(EXPR)              
--------------------

               10437
               10437

--- 2 row(s) selected.
>>
>>-- 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;

--- 0 row(s) selected.
>>
>>
>>--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 WSCS will not be shared among multiple consumers. Reason: expression is only evaluated once because parent is materialized.

--- 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;

--- 0 row(s) selected.
>>
>>
>>--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;

--- 0 row(s) selected.
>>
>>
>>--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;

--- 0 row(s) selected.
>>
>>--QID: 14a
>>
>>prepare s from
+>-- **************
+>-- VARIANT/ALTERNATIVE Version from TPC-DS (Rollout) is used
+>-- **************
+>
+>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[2997]  (Subquery was not unnested. Reason: No Correlation found)

--- SQL command prepared.
>>execute show_cses;

OPERATOR                        HOW_MANY            
------------------------------  --------------------

BLOCKED_UNION                                      5
HIVE_INSERT                                        3
SCAN TEMP                                         11

--- 3 row(s) selected.
>>execute s;

CHANNEL  I_BRAND_ID   I_CLASS_ID   I_CATEGORY_ID  SUM_SALES             NUMBER_SALES
-------  -----------  -----------  -------------  --------------------  --------------------

?                  ?            ?              ?                     ?                     ?

--- 1 row(s) selected.
>>
>>--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 MAX_STORE_SALES will not be shared among multiple consumers. Reason: expression is only evaluated once because parent is materialized.

--- SQL command prepared.
>>execute show_cses;

OPERATOR                        HOW_MANY            
------------------------------  --------------------

BLOCKED_UNION                                      3
HIVE_INSERT                                        2
SCAN TEMP                                          4

--- 3 row(s) selected.
>>execute s;

(EXPR)              
--------------------

                   ?

--- 1 row(s) selected.
>>
>>--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 MAX_STORE_SALES will not be shared among multiple consumers. Reason: expression is only evaluated once because parent is materialized.

--- SQL command prepared.
>>execute show_cses;

OPERATOR                        HOW_MANY            
------------------------------  --------------------

BLOCKED_UNION                                      3
HIVE_INSERT                                        2
SCAN TEMP                                          4

--- 3 row(s) selected.
>>execute s;

--- 0 row(s) selected.
>>
>>--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;

--- 0 row(s) selected.
>>
>>--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;

--- 0 row(s) selected.
>>
>>--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;

--- 0 row(s) selected.
>>
>>--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 will not be shared among multiple consumers. Reason: Encountered VEGs with different constants in different consumers.

*** WARNING[5001] Common subexpression SS will not be shared among multiple consumers. Reason: Differing inputs in CTE references, try CQD QUERY_CACHE '0'.

*** WARNING[5001] Common subexpression WS will not be shared among multiple consumers. Reason: Encountered VEGs with different constants in different consumers.

*** WARNING[5001] Common subexpression WS will not be shared among multiple consumers. Reason: Differing inputs in CTE references, try CQD QUERY_CACHE '0'.

--- SQL command prepared.
>>-- Different constants used in different references of WITH clause - not yet supported
>>execute show_cses;

--- 0 row(s) selected.
>>execute s;

--- 0 row(s) selected.
>>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;
