trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From zellerh <...@git.apache.org>
Subject [GitHub] incubator-trafodion pull request #794: [TRAFODION-2317] Infrastructure for c...
Date Fri, 28 Oct 2016 00:47:42 GMT
Github user zellerh commented on a diff in the pull request:

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


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---


Mime
View raw message