Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-8263

CBO : TPC-DS Q64 is item is joined last with store_sales while it should be first as it is the most selective

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 0.14.0
    • 0.14.0
    • CBO
    • None

    Description

      Plan for TPC-DS Q64 shows that item is joined last with store_sales while store_sales x item is the most selective join in the plan.
      Interestingly predicate push down is applied on item but item comes so late in the join which most likely means that calculation of the join selectivity gave too high of a number of it was never considered.

      This is a subset of the logical plan showing that item was joined very last

      HiveProjectRel(_o__col0=[$0], _o__col1=[$2], _o__col2=[$3], _o__col3=[$4], _o__col4=[$5], _o__col5=[$6], _o__col6=[$7], _o__col7=[$8], _o__col8=[$9], _o__col9=[$10], _o__col10=[$11], _o__col11=[$12], _o__col12=[$13], _o__col13=[$14], _o__col14=[$15], _o__col15=[$16], _o__col16=[$22], _o__col17=[$23], _o__col18=[$24], _o__col19=[$20], _o__col20=[$21]): rowcount = 1.0, cumulative cost = {1.1593403796322412E9 rows, 0.0 cpu, 0.0 io}, id = 990
          HiveFilterRel(condition=[<=($21, $13)]): rowcount = 1.0, cumulative cost = {1.1593403796322412E9 rows, 0.0 cpu, 0.0 io}, id = 988
            HiveProjectRel(_o__col0=[$0], _o__col1=[$1], _o__col2=[$2], _o__col3=[$3], _o__col4=[$4], _o__col5=[$5], _o__col6=[$6], _o__col7=[$7], _o__col8=[$8], _o__col9=[$9], _o__col10=[$10], _o__col11=[$11], _o__col12=[$12], _o__col15=[$13], _o__col16=[$14], _o__col17=[$15], _o__col18=[$16], _o__col13=[$17], _o__col20=[$18], _o__col30=[$19], _o__col120=[$20], _o__col150=[$21], _o__col160=[$22], _o__col170=[$23], _o__col180=[$24]): rowcount = 1.0, cumulative cost = {1.1593403796322412E9 rows, 0.0 cpu, 0.0 io}, id = 3571
              HiveJoinRel(condition=[AND(AND(=($1, $17), =($2, $18)), =($3, $19))], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1593403796322412E9 rows, 0.0 cpu, 0.0 io}, id = 3566
                HiveProjectRel(_o__col0=[$0], _o__col1=[$1], _o__col2=[$2], _o__col3=[$3], _o__col4=[$4], _o__col5=[$5], _o__col6=[$6], _o__col7=[$7], _o__col8=[$8], _o__col9=[$9], _o__col10=[$10], _o__col11=[$11], _o__col12=[$12], _o__col15=[$15], _o__col16=[$16], _o__col17=[$17], _o__col18=[$18]): rowcount = 1.0, cumulative cost = {1.1593403776322412E9 rows, 0.0 cpu, 0.0 io}, id = 890
                  HiveFilterRel(condition=[=($12, 2000)]): rowcount = 1.0, cumulative cost = {1.1593403776322412E9 rows, 0.0 cpu, 0.0 io}, id = 888
                    HiveAggregateRel(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14}], agg#0=[count()], agg#1=[sum($15)], agg#2=[sum($16)], agg#3=[sum($17)]): rowcount = 1.0, cumulative cost = {1.1593403776322412E9 rows, 0.0 cpu, 0.0 io}, id = 886
                      HiveProjectRel($f0=[$53], $f1=[$50], $f2=[$27], $f3=[$28], $f4=[$39], $f5=[$40], $f6=[$41], $f7=[$42], $f8=[$44], $f9=[$45], $f10=[$46], $f11=[$47], $f12=[$21], $f13=[$23], $f14=[$25], $f15=[$9], $f16=[$10], $f17=[$11]): rowcount = 1.0, cumulative cost = {1.1593403776322412E9 rows, 0.0 cpu, 0.0 io}, id = 884
                        HiveProjectRel(ss_sold_date_sk=[$17], ss_item_sk=[$18], ss_customer_sk=[$19], ss_cdemo_sk=[$20], ss_hdemo_sk=[$21], ss_addr_sk=[$22], ss_store_sk=[$23], ss_promo_sk=[$24], ss_ticket_number=[$25], ss_wholesale_cost=[$26], ss_list_price=[$27], ss_coupon_amt=[$28], sr_item_sk=[$29], sr_ticket_number=[$30], c_customer_sk=[$31], c_current_cdemo_sk=[$32], c_current_hdemo_sk=[$33], c_current_addr_sk=[$34], c_first_shipto_date_sk=[$35], c_first_sales_date_sk=[$36], d_date_sk=[$37], d_year=[$38], d_date_sk0=[$39], d_year0=[$40], d_date_sk1=[$41], d_year1=[$42], s_store_sk=[$43], s_store_name=[$44], s_zip=[$45], cd_demo_sk=[$46], cd_marital_status=[$47], cd_demo_sk0=[$48], cd_marital_status0=[$49], p_promo_sk=[$0], hd_demo_sk=[$15], hd_income_band_sk=[$16], hd_demo_sk0=[$13], hd_income_band_sk0=[$14], ca_address_sk=[$6], ca_street_number=[$7], ca_street_name=[$8], ca_city=[$9], ca_zip=[$10], ca_address_sk0=[$1], ca_street_number0=[$2], ca_street_name0=[$3], ca_city0=[$4], ca_zip0=[$5], ib_income_band_sk=[$12], ib_income_band_sk0=[$11], i_item_sk=[$51], i_current_price=[$52], i_color=[$53], i_product_name=[$54], _o__col0=[$50]): rowcount = 1.0, cumulative cost = {1.1593403776322412E9 rows, 0.0 cpu, 0.0 io}, id = 3564
                          HiveJoinRel(condition=[=($24, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1593403776322412E9 rows, 0.0 cpu, 0.0 io}, id = 3562
                            HiveProjectRel(p_promo_sk=[$0]): rowcount = 450.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 928
                              HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.promotion]]): rowcount = 450.0, cumulative cost = {0}, id = 58
                            HiveJoinRel(condition=[=($33, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1593399266322412E9 rows, 0.0 cpu, 0.0 io}, id = 3560
                              HiveProjectRel(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9]): rowcount = 800000.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 940
                                HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.customer_address]]): rowcount = 800000.0, cumulative cost = {0}, id = 61
                              HiveJoinRel(condition=[=($16, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1585399256322412E9 rows, 0.0 cpu, 0.0 io}, id = 3558
                                HiveProjectRel(ca_address_sk=[$0], ca_street_number=[$2], ca_street_name=[$3], ca_city=[$6], ca_zip=[$9]): rowcount = 800000.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 940
                                  HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.customer_address]]): rowcount = 800000.0, cumulative cost = {0}, id = 61
                                HiveJoinRel(condition=[=($3, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1577399246322412E9 rows, 0.0 cpu, 0.0 io}, id = 3556
                                  HiveProjectRel(ib_income_band_sk=[$0]): rowcount = 20.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 948
                                    HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.income_band]]): rowcount = 20.0, cumulative cost = {0}, id = 63
                                  HiveJoinRel(condition=[=($4, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1577399036322412E9 rows, 0.0 cpu, 0.0 io}, id = 3554
                                    HiveProjectRel(ib_income_band_sk=[$0]): rowcount = 20.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 948
                                      HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.income_band]]): rowcount = 20.0, cumulative cost = {0}, id = 63
                                    HiveJoinRel(condition=[=($5, $38)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1577398826322412E9 rows, 0.0 cpu, 0.0 io}, id = 3552
                                      HiveJoinRel(condition=[=($20, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1577398806322412E9 rows, 0.0 cpu, 0.0 io}, id = 3550
                                        HiveProjectRel(hd_demo_sk=[$0], hd_income_band_sk=[$1]): rowcount = 7200.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 932
                                          HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.household_demographics]]): rowcount = 7200.0, cumulative cost = {0}, id = 53
                                        HiveJoinRel(condition=[=($6, $0)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1577326796322412E9 rows, 0.0 cpu, 0.0 io}, id = 3548
                                          HiveProjectRel(hd_demo_sk=[$0], hd_income_band_sk=[$1]): rowcount = 7200.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 932
                                            HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.household_demographics]]): rowcount = 7200.0, cumulative cost = {0}, id = 53
                                          HiveJoinRel(condition=[=($1, $33)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1577254786322412E9 rows, 0.0 cpu, 0.0 io}, id = 2795
                                            HiveFilterRel(condition=[<>($30, $32)]): rowcount = 1.0, cumulative cost = {1.1577254766322412E9 rows, 0.0 cpu, 0.0 io}, id = 832
                                              HiveProjectRel(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_customer_sk=[$2], ss_cdemo_sk=[$3], ss_hdemo_sk=[$4], ss_addr_sk=[$5], ss_store_sk=[$6], ss_promo_sk=[$7], ss_ticket_number=[$8], ss_wholesale_cost=[$9], ss_list_price=[$10], ss_coupon_amt=[$11], sr_item_sk=[$31], sr_ticket_number=[$32], c_customer_sk=[$21], c_current_cdemo_sk=[$22], c_current_hdemo_sk=[$23], c_current_addr_sk=[$24], c_first_shipto_date_sk=[$25], c_first_sales_date_sk=[$26], d_date_sk=[$14], d_year=[$15], d_date_sk0=[$27], d_year0=[$28], d_date_sk1=[$29], d_year1=[$30], s_store_sk=[$16], s_store_name=[$17], s_zip=[$18], cd_demo_sk=[$12], cd_marital_status=[$13], cd_demo_sk0=[$19], cd_marital_status0=[$20]): rowcount = 3.9579965360452816, cumulative cost = {1.1577254766322412E9 rows, 0.0 cpu, 0.0 io}, id = 1998
                                                HiveJoinRel(condition=[AND(=($1, $31), =($8, $32))], joinType=[inner]): rowcount = 3.9579965360452816, cumulative cost = {1.1577254766322412E9 rows, 0.0 cpu, 0.0 io}, id = 1996
                                                  HiveJoinRel(condition=[=($2, $21)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1021474706322412E9 rows, 0.0 cpu, 0.0 io}, id = 1994
                                                    HiveJoinRel(condition=[=($6, $16)], joinType=[inner]): rowcount = 1.0, cumulative cost = {1.1021474686322412E9 rows, 0.0 cpu, 0.0 io}, id = 1987
                                                      HiveJoinRel(condition=[=($0, $14)], joinType=[inner]): rowcount = 299.6322411714753, cumulative cost = {1.102146957E9 rows, 0.0 cpu, 0.0 io}, id = 1985
                                                        HiveJoinRel(condition=[=($3, $12)], joinType=[inner]): rowcount = 5.50076554E8, cumulative cost = {5.51997354E8 rows, 0.0 cpu, 0.0 io}, id = 1569
                                                          HiveProjectRel(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_cdemo_sk=[$4], ss_hdemo_sk=[$5], ss_addr_sk=[$6], ss_store_sk=[$7], ss_promo_sk=[$8], ss_ticket_number=[$9], ss_wholesale_cost=[$11], ss_list_price=[$12], ss_coupon_amt=[$19]): rowcount = 5.50076554E8, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 892
                                                            HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.store_sales]]): rowcount = 5.50076554E8, cumulative cost = {0}, id = 55
                                                          HiveProjectRel(cd_demo_sk=[$0], cd_marital_status=[$2]): rowcount = 1920800.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 918
                                                            HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.customer_demographics]]): rowcount = 1920800.0, cumulative cost = {0}, id = 56
                                                        HiveProjectRel(d_date_sk=[$0], d_year=[$6]): rowcount = 73049.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 902
                                                          HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.date_dim]]): rowcount = 73049.0, cumulative cost = {0}, id = 65
                                                      HiveProjectRel(s_store_sk=[$0], s_store_name=[$5], s_zip=[$25]): rowcount = 212.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 914
                                                        HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.store]]): rowcount = 212.0, cumulative cost = {0}, id = 54
                                                    HiveJoinRel(condition=[=($6, $10)], joinType=[inner]): rowcount = 1.0, cumulative cost = {5266899.0 rows, 0.0 cpu, 0.0 io}, id = 1992
                                                      HiveJoinRel(condition=[=($7, $8)], joinType=[inner]): rowcount = 1.0, cumulative cost = {5193849.0 rows, 0.0 cpu, 0.0 io}, id = 1990
                                                        HiveJoinRel(condition=[=($3, $0)], joinType=[inner]): rowcount = 1600000.0, cumulative cost = {3520800.0 rows, 0.0 cpu, 0.0 io}, id = 1578
                                                          HiveProjectRel(cd_demo_sk=[$0], cd_marital_status=[$2]): rowcount = 1920800.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 918
                                                            HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.customer_demographics]]): rowcount = 1920800.0, cumulative cost = {0}, id = 56
                                                          HiveProjectRel(c_customer_sk=[$0], c_current_cdemo_sk=[$2], c_current_hdemo_sk=[$3], c_current_addr_sk=[$4], c_first_shipto_date_sk=[$5], c_first_sales_date_sk=[$6]): rowcount = 1600000.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 898
                                                            HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.customer]]): rowcount = 1600000.0, cumulative cost = {0}, id = 59
                                                        HiveProjectRel(d_date_sk=[$0], d_year=[$6]): rowcount = 73049.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 902
                                                          HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.date_dim]]): rowcount = 73049.0, cumulative cost = {0}, id = 65
                                                      HiveProjectRel(d_date_sk=[$0], d_year=[$6]): rowcount = 73049.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 902
                                                        HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.date_dim]]): rowcount = 73049.0, cumulative cost = {0}, id = 65
                                                  HiveProjectRel(sr_item_sk=[$2], sr_ticket_number=[$9]): rowcount = 5.5578005E7, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 894
                                                    HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.store_returns]]): rowcount = 5.5578005E7, cumulative cost = {0}, id = 62
                                            HiveProjectRel(_o__col0=[$0]): rowcount = 1.0, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 880
                                              HiveFilterRel(condition=[>($1, *(CAST(2):DOUBLE NOT NULL, $2))]): rowcount = 1.0, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 878
                                                HiveAggregateRel(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)]): rowcount = 38846.0, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 876
                                                  HiveProjectRel($f0=[$0], $f1=[$2], $f2=[+(+($5, $6), $7)]): rowcount = 6.692553251460564E8, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 874
                                                    HiveProjectRel(cs_item_sk=[$0], cs_order_number=[$1], cs_ext_list_price=[$2], cr_item_sk=[$3], cr_order_number=[$4], cr_refunded_cash=[$5], cr_reversed_charge=[$6], cr_store_credit=[$7]): rowcount = 6.692553251460564E8, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1132
                                                      HiveJoinRel(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner]): rowcount = 6.692553251460564E8, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1127
                                                        HiveProjectRel(cs_item_sk=[$15], cs_order_number=[$17], cs_ext_list_price=[$25]): rowcount = 2.86549727E8, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 962
                                                          HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.catalog_sales]]): rowcount = 2.86549727E8, cumulative cost = {0}, id = 45
                                                        HiveProjectRel(cr_item_sk=[$2], cr_order_number=[$16], cr_refunded_cash=[$23], cr_reversed_charge=[$24], cr_store_credit=[$25]): rowcount = 2.8798881E7, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 964
                                                          HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.catalog_returns]]): rowcount = 2.8798881E7, cumulative cost = {0}, id = 46
                                      HiveFilterRel(condition=[AND(in($2, 'maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate'), between(false, $1, 35, +(35, 10)), between(false, $1, +(35, 1), +(35, 15)))]): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 864
                                        HiveProjectRel(i_item_sk=[$0], i_current_price=[$5], i_color=[$17], i_product_name=[$21]): rowcount = 48000.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 862
                                          HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200.item]]): rowcount = 48000.0, cumulative cost = {0}, id = 68
      
      

      Physical plan

      STAGE PLANS:
        Stage: Stage-1
          Tez
            Edges:
              Map 10 <- Map 40 (BROADCAST_EDGE)
              Map 16 <- Map 2 (BROADCAST_EDGE), Map 23 (BROADCAST_EDGE), Map 6 (BROADCAST_EDGE)
              Map 19 <- Map 42 (BROADCAST_EDGE)
              Map 33 <- Map 35 (BROADCAST_EDGE)
              Map 35 <- Map 1 (BROADCAST_EDGE), Map 16 (BROADCAST_EDGE), Map 3 (BROADCAST_EDGE), Map 8 (BROADCAST_EDGE)
              Map 36 <- Map 18 (BROADCAST_EDGE), Map 25 (BROADCAST_EDGE), Map 41 (BROADCAST_EDGE)
              Map 38 <- Map 15 (BROADCAST_EDGE), Map 31 (BROADCAST_EDGE), Map 36 (BROADCAST_EDGE), Map 5 (BROADCAST_EDGE)
              Map 4 <- Map 38 (BROADCAST_EDGE)
              Reducer 11 <- Map 10 (SIMPLE_EDGE), Map 22 (BROADCAST_EDGE), Map 24 (BROADCAST_EDGE), Map 26 (BROADCAST_EDGE), Map 27 (BROADCAST_EDGE), Map 30 (BROADCAST_EDGE), Map 32 (BROADCAST_EDGE), Map 39 (BROADCAST_EDGE), Map 4 (BROADCAST_EDGE), Map 7 (BROADCAST_EDGE)
              Reducer 12 <- Reducer 11 (SIMPLE_EDGE)
              Reducer 13 <- Reducer 12 (SIMPLE_EDGE), Reducer 21 (SIMPLE_EDGE)
              Reducer 14 <- Reducer 13 (SIMPLE_EDGE)
              Reducer 20 <- Map 17 (BROADCAST_EDGE), Map 19 (SIMPLE_EDGE), Map 28 (BROADCAST_EDGE), Map 29 (BROADCAST_EDGE), Map 33 (BROADCAST_EDGE), Map 34 (BROADCAST_EDGE), Map 37 (BROADCAST_EDGE), Map 43 (BROADCAST_EDGE), Map 44 (BROADCAST_EDGE), Map 9 (BROADCAST_EDGE)
              Reducer 21 <- Reducer 20 (SIMPLE_EDGE)
            DagName: mmokhtar_20140925174747_6fa8c67a-6d24-43cc-8fbb-3be14937b8b1:1
            Vertices:
              Map 1 
                  Map Operator Tree:
                      TableScan
                        alias: d1
                        filterExpr: d_date_sk is not null (type: boolean)
                        Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: d_date_sk is not null (type: boolean)
                          Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: d_date_sk (type: int), d_year (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 10 
                  Map Operator Tree:
                      TableScan
                        alias: catalog_sales
                        filterExpr: (cs_item_sk is not null and cs_order_number is not null) (type: boolean)
                        Statistics: Num rows: 286549727 Data size: 38890158232 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (cs_item_sk is not null and cs_order_number is not null) (type: boolean)
                          Statistics: Num rows: 71637432 Data size: 9722539591 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cs_item_sk (type: int), cs_order_number (type: int), cs_ext_list_price (type: float)
                            outputColumnNames: _col0, _col1, _col2
                            Statistics: Num rows: 71637432 Data size: 9722539591 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col0} {_col2}
                                1 {_col2} {_col3} {_col4}
                              keys:
                                0 _col0 (type: int), _col1 (type: int)
                                1 _col0 (type: int), _col1 (type: int)
                              outputColumnNames: _col0, _col2, _col5, _col6, _col7
                              input vertices:
                                1 Map 40
                              Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                              Select Operator
                                expressions: _col0 (type: int), _col2 (type: float), ((_col5 + _col6) + _col7) (type: float)
                                outputColumnNames: _col0, _col1, _col2
                                Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                                Group By Operator
                                  aggregations: sum(_col1), sum(_col2)
                                  keys: _col0 (type: int)
                                  mode: hash
                                  outputColumnNames: _col0, _col1, _col2
                                  Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                                  Reduce Output Operator
                                    key expressions: _col0 (type: int)
                                    sort order: +
                                    Map-reduce partition columns: _col0 (type: int)
                                    Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                                    value expressions: _col1 (type: double), _col2 (type: double)
                  Execution mode: vectorized
              Map 15 
                  Map Operator Tree:
                      TableScan
                        alias: customer
                        filterExpr: (((((c_current_cdemo_sk is not null and c_first_sales_date_sk is not null) and c_first_shipto_date_sk is not null) and c_customer_sk is not null) and c_current_hdemo_sk is not null) and c_current_addr_sk is not null) (type: boolean)
                        Statistics: Num rows: 1600000 Data size: 1376033128 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (((((c_current_cdemo_sk is not null and c_first_sales_date_sk is not null) and c_first_shipto_date_sk is not null) and c_customer_sk is not null) and c_current_hdemo_sk is not null) and c_current_addr_sk is not null) (type: boolean)
                          Statistics: Num rows: 25000 Data size: 21500517 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: c_customer_sk (type: int), c_current_cdemo_sk (type: int), c_current_hdemo_sk (type: int), c_current_addr_sk (type: int), c_first_shipto_date_sk (type: int), c_first_sales_date_sk (type: int)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
                            Statistics: Num rows: 25000 Data size: 21500517 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col1 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col1 (type: int)
                              Statistics: Num rows: 25000 Data size: 21500517 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col0 (type: int), _col2 (type: int), _col3 (type: int), _col4 (type: int), _col5 (type: int)
                  Execution mode: vectorized
              Map 16 
                  Map Operator Tree:
                      TableScan
                        alias: cd1
                        filterExpr: cd_demo_sk is not null (type: boolean)
                        Statistics: Num rows: 1920800 Data size: 718379200 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: cd_demo_sk is not null (type: boolean)
                          Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cd_demo_sk (type: int), cd_marital_status (type: string)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col0} {_col1} {_col2} {_col4} {_col5} {_col6} {_col7} {_col8} {_col9} {_col10} {_col11}
                                1 {_col1}
                              keys:
                                0 _col3 (type: int)
                                1 _col0 (type: int)
                              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col13
                              input vertices:
                                0 Map 6
                              Statistics: Num rows: 1181805 Data size: 51579732 Basic stats: COMPLETE Column stats: NONE
                              Map Join Operator
                                condition map:
                                     Inner Join 0 to 1
                                condition expressions:
                                  0 {_col1} {_col2} {_col4} {_col5} {_col6} {_col7} {_col8} {_col9} {_col10} {_col11} {_col13}
                                  1 
                                keys:
                                  0 _col0 (type: int)
                                  1 _col0 (type: int)
                                outputColumnNames: _col1, _col2, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col13
                                input vertices:
                                  1 Map 23
                                Statistics: Num rows: 1299985 Data size: 56737708 Basic stats: COMPLETE Column stats: NONE
                                Map Join Operator
                                  condition map:
                                       Inner Join 0 to 1
                                  condition expressions:
                                    0 {_col1} {_col2} {_col4} {_col5} {_col7} {_col8} {_col9} {_col10} {_col11} {_col13}
                                    1 {_col1} {_col2}
                                  keys:
                                    0 _col6 (type: int)
                                    1 _col0 (type: int)
                                  outputColumnNames: _col1, _col2, _col4, _col5, _col7, _col8, _col9, _col10, _col11, _col13, _col17, _col18
                                  input vertices:
                                    1 Map 2
                                  Statistics: Num rows: 1429983 Data size: 62411480 Basic stats: COMPLETE Column stats: NONE
                                  Reduce Output Operator
                                    key expressions: _col2 (type: int)
                                    sort order: +
                                    Map-reduce partition columns: _col2 (type: int)
                                    Statistics: Num rows: 1429983 Data size: 62411480 Basic stats: COMPLETE Column stats: NONE
                                    value expressions: _col1 (type: int), _col4 (type: int), _col5 (type: int), _col7 (type: int), _col8 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float), _col13 (type: string), _col17 (type: string), _col18 (type: string)
                  Execution mode: vectorized
              Map 17 
                  Map Operator Tree:
                      TableScan
                        alias: ib1
                        filterExpr: ib_income_band_sk is not null (type: boolean)
                        Statistics: Num rows: 20 Data size: 240 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ib_income_band_sk is not null (type: boolean)
                          Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ib_income_band_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 18 
                  Map Operator Tree:
                      TableScan
                        alias: store_sales
                        filterExpr: ((((((((ss_cdemo_sk is not null and ss_sold_date_sk is not null) and ss_store_sk is not null) and ss_customer_sk is not null) and ss_item_sk is not null) and ss_ticket_number is not null) and ss_hdemo_sk is not null) and ss_addr_sk is not null) and ss_promo_sk is not null) (type: boolean)
                        Statistics: Num rows: 550076554 Data size: 24008004411 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ((((((((ss_cdemo_sk is not null and ss_sold_date_sk is not null) and ss_store_sk is not null) and ss_customer_sk is not null) and ss_item_sk is not null) and ss_ticket_number is not null) and ss_hdemo_sk is not null) and ss_addr_sk is not null) and ss_promo_sk is not null) (type: boolean)
                          Statistics: Num rows: 1074369 Data size: 46890665 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ss_sold_date_sk (type: int), ss_item_sk (type: int), ss_customer_sk (type: int), ss_cdemo_sk (type: int), ss_hdemo_sk (type: int), ss_addr_sk (type: int), ss_store_sk (type: int), ss_promo_sk (type: int), ss_ticket_number (type: int), ss_wholesale_cost (type: float), ss_list_price (type: float), ss_coupon_amt (type: float)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11
                            Statistics: Num rows: 1074369 Data size: 46890665 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col3 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col3 (type: int)
                              Statistics: Num rows: 1074369 Data size: 46890665 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col0 (type: int), _col1 (type: int), _col2 (type: int), _col4 (type: int), _col5 (type: int), _col6 (type: int), _col7 (type: int), _col8 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float)
                  Execution mode: vectorized
              Map 19 
                  Map Operator Tree:
                      TableScan
                        alias: catalog_sales
                        filterExpr: (cs_item_sk is not null and cs_order_number is not null) (type: boolean)
                        Statistics: Num rows: 286549727 Data size: 38890158232 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (cs_item_sk is not null and cs_order_number is not null) (type: boolean)
                          Statistics: Num rows: 71637432 Data size: 9722539591 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cs_item_sk (type: int), cs_order_number (type: int), cs_ext_list_price (type: float)
                            outputColumnNames: _col0, _col1, _col2
                            Statistics: Num rows: 71637432 Data size: 9722539591 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col0} {_col2}
                                1 {_col2} {_col3} {_col4}
                              keys:
                                0 _col0 (type: int), _col1 (type: int)
                                1 _col0 (type: int), _col1 (type: int)
                              outputColumnNames: _col0, _col2, _col5, _col6, _col7
                              input vertices:
                                1 Map 42
                              Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                              Select Operator
                                expressions: _col0 (type: int), _col2 (type: float), ((_col5 + _col6) + _col7) (type: float)
                                outputColumnNames: _col0, _col1, _col2
                                Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                                Group By Operator
                                  aggregations: sum(_col1), sum(_col2)
                                  keys: _col0 (type: int)
                                  mode: hash
                                  outputColumnNames: _col0, _col1, _col2
                                  Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                                  Reduce Output Operator
                                    key expressions: _col0 (type: int)
                                    sort order: +
                                    Map-reduce partition columns: _col0 (type: int)
                                    Statistics: Num rows: 78801176 Data size: 10694794240 Basic stats: COMPLETE Column stats: NONE
                                    value expressions: _col1 (type: double), _col2 (type: double)
                  Execution mode: vectorized
              Map 2 
                  Map Operator Tree:
                      TableScan
                        alias: store
                        filterExpr: ((s_store_sk is not null and s_store_name is not null) and s_zip is not null) (type: boolean)
                        Statistics: Num rows: 212 Data size: 405680 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ((s_store_sk is not null and s_store_name is not null) and s_zip is not null) (type: boolean)
                          Statistics: Num rows: 27 Data size: 51666 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: s_store_sk (type: int), s_store_name (type: string), s_zip (type: string)
                            outputColumnNames: _col0, _col1, _col2
                            Statistics: Num rows: 27 Data size: 51666 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 27 Data size: 51666 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: string), _col2 (type: string)
                  Execution mode: vectorized
              Map 22 
                  Map Operator Tree:
                      TableScan
                        alias: ib1
                        filterExpr: ib_income_band_sk is not null (type: boolean)
                        Statistics: Num rows: 20 Data size: 240 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ib_income_band_sk is not null (type: boolean)
                          Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ib_income_band_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 23 
                  Map Operator Tree:
                      TableScan
                        alias: d1
                        filterExpr: (d_date_sk is not null and (d_year = 2000)) (type: boolean)
                        Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (d_date_sk is not null and (d_year = 2000)) (type: boolean)
                          Statistics: Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: d_date_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 24 
                  Map Operator Tree:
                      TableScan
                        alias: hd1
                        filterExpr: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                        Statistics: Num rows: 7200 Data size: 799 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                          Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: hd_demo_sk (type: int), hd_income_band_sk (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 25 
                  Map Operator Tree:
                      TableScan
                        alias: d1
                        filterExpr: (d_date_sk is not null and (d_year = 2001)) (type: boolean)
                        Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (d_date_sk is not null and (d_year = 2001)) (type: boolean)
                          Statistics: Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: d_date_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 26 
                  Map Operator Tree:
                      TableScan
                        alias: promotion
                        filterExpr: p_promo_sk is not null (type: boolean)
                        Statistics: Num rows: 450 Data size: 530848 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: p_promo_sk is not null (type: boolean)
                          Statistics: Num rows: 225 Data size: 265424 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: p_promo_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 225 Data size: 265424 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 225 Data size: 265424 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 27 
                  Map Operator Tree:
                      TableScan
                        alias: hd1
                        filterExpr: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                        Statistics: Num rows: 7200 Data size: 799 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                          Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: hd_demo_sk (type: int), hd_income_band_sk (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 28 
                  Map Operator Tree:
                      TableScan
                        alias: ad1
                        filterExpr: ca_address_sk is not null (type: boolean)
                        Statistics: Num rows: 800000 Data size: 811903688 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ca_address_sk is not null (type: boolean)
                          Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ca_address_sk (type: int), ca_street_number (type: string), ca_street_name (type: string), ca_city (type: string), ca_zip (type: string)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4
                            Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string)
                  Execution mode: vectorized
              Map 29 
                  Map Operator Tree:
                      TableScan
                        alias: item
                        filterExpr: ((((i_color) IN ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate') and i_current_price BETWEEN 35 AND 45) and i_current_price BETWEEN 36 AND 50) and i_item_sk is not null) (type: boolean)
                        Statistics: Num rows: 48000 Data size: 68732712 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ((((i_color) IN ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate') and i_current_price BETWEEN 35 AND 45) and i_current_price BETWEEN 36 AND 50) and i_item_sk is not null) (type: boolean)
                          Statistics: Num rows: 3000 Data size: 4295794 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: i_item_sk (type: int), i_product_name (type: string)
                            outputColumnNames: _col0, _col3
                            Statistics: Num rows: 3000 Data size: 4295794 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 3000 Data size: 4295794 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col3 (type: string)
                  Execution mode: vectorized
              Map 3 
                  Map Operator Tree:
                      TableScan
                        alias: customer
                        filterExpr: (((((c_current_cdemo_sk is not null and c_first_sales_date_sk is not null) and c_first_shipto_date_sk is not null) and c_customer_sk is not null) and c_current_hdemo_sk is not null) and c_current_addr_sk is not null) (type: boolean)
                        Statistics: Num rows: 1600000 Data size: 1376033128 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (((((c_current_cdemo_sk is not null and c_first_sales_date_sk is not null) and c_first_shipto_date_sk is not null) and c_customer_sk is not null) and c_current_hdemo_sk is not null) and c_current_addr_sk is not null) (type: boolean)
                          Statistics: Num rows: 25000 Data size: 21500517 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: c_customer_sk (type: int), c_current_cdemo_sk (type: int), c_current_hdemo_sk (type: int), c_current_addr_sk (type: int), c_first_shipto_date_sk (type: int), c_first_sales_date_sk (type: int)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
                            Statistics: Num rows: 25000 Data size: 21500517 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col1 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col1 (type: int)
                              Statistics: Num rows: 25000 Data size: 21500517 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col0 (type: int), _col2 (type: int), _col3 (type: int), _col4 (type: int), _col5 (type: int)
                  Execution mode: vectorized
              Map 30 
                  Map Operator Tree:
                      TableScan
                        alias: ib1
                        filterExpr: ib_income_band_sk is not null (type: boolean)
                        Statistics: Num rows: 20 Data size: 240 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ib_income_band_sk is not null (type: boolean)
                          Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ib_income_band_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 31 
                  Map Operator Tree:
                      TableScan
                        alias: d1
                        filterExpr: d_date_sk is not null (type: boolean)
                        Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: d_date_sk is not null (type: boolean)
                          Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: d_date_sk (type: int), d_year (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 32 
                  Map Operator Tree:
                      TableScan
                        alias: ad1
                        filterExpr: ca_address_sk is not null (type: boolean)
                        Statistics: Num rows: 800000 Data size: 811903688 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ca_address_sk is not null (type: boolean)
                          Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ca_address_sk (type: int), ca_street_number (type: string), ca_street_name (type: string), ca_city (type: string), ca_zip (type: string)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4
                            Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string)
                  Execution mode: vectorized
              Map 33 
                  Map Operator Tree:
                      TableScan
                        alias: store_returns
                        filterExpr: (sr_item_sk is not null and sr_ticket_number is not null) (type: boolean)
                        Statistics: Num rows: 55578005 Data size: 4377627636 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (sr_item_sk is not null and sr_ticket_number is not null) (type: boolean)
                          Statistics: Num rows: 13894502 Data size: 1094406968 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: sr_item_sk (type: int), sr_ticket_number (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 13894502 Data size: 1094406968 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col1} {_col4} {_col5} {_col7} {_col9} {_col10} {_col11} {_col13} {_col17} {_col18} {_col20} {_col23} {_col24} {_col28} {_col30}
                                1 
                              keys:
                                0 _col1 (type: int), _col8 (type: int)
                                1 _col0 (type: int), _col1 (type: int)
                              outputColumnNames: _col1, _col4, _col5, _col7, _col9, _col10, _col11, _col13, _col17, _col18, _col20, _col23, _col24, _col28, _col30
                              input vertices:
                                0 Map 35
                              Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                              Filter Operator
                                predicate: (_col13 <> _col20) (type: boolean)
                                Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                                Select Operator
                                  expressions: _col1 (type: int), _col10 (type: float), _col11 (type: float), _col23 (type: int), _col24 (type: int), 2000 (type: int), _col28 (type: int), _col30 (type: int), _col17 (type: string), _col18 (type: string), _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float)
                                  outputColumnNames: _col1, _col10, _col11, _col16, _col17, _col21, _col23, _col25, _col27, _col28, _col4, _col5, _col7, _col9
                                  Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                                  Reduce Output Operator
                                    key expressions: _col1 (type: int)
                                    sort order: +
                                    Map-reduce partition columns: _col1 (type: int)
                                    Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                                    value expressions: _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float), _col16 (type: int), _col17 (type: int), _col21 (type: int), _col23 (type: int), _col25 (type: int), _col27 (type: string), _col28 (type: string)
                  Execution mode: vectorized
              Map 34 
                  Map Operator Tree:
                      TableScan
                        alias: promotion
                        filterExpr: p_promo_sk is not null (type: boolean)
                        Statistics: Num rows: 450 Data size: 530848 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: p_promo_sk is not null (type: boolean)
                          Statistics: Num rows: 225 Data size: 265424 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: p_promo_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 225 Data size: 265424 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 225 Data size: 265424 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 35 
                  Map Operator Tree:
                      TableScan
                        alias: cd1
                        filterExpr: cd_demo_sk is not null (type: boolean)
                        Statistics: Num rows: 1920800 Data size: 718379200 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: cd_demo_sk is not null (type: boolean)
                          Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cd_demo_sk (type: int), cd_marital_status (type: string)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col1}
                                1 {_col0} {_col2} {_col3} {_col4} {_col5}
                              keys:
                                0 _col0 (type: int)
                                1 _col1 (type: int)
                              outputColumnNames: _col1, _col2, _col4, _col5, _col6, _col7
                              input vertices:
                                1 Map 3
                              Statistics: Num rows: 1056440 Data size: 395108576 Basic stats: COMPLETE Column stats: NONE
                              Map Join Operator
                                condition map:
                                     Inner Join 0 to 1
                                condition expressions:
                                  0 {_col1} {_col2} {_col4} {_col5} {_col6}
                                  1 {_col1}
                                keys:
                                  0 _col7 (type: int)
                                  1 _col0 (type: int)
                                outputColumnNames: _col1, _col2, _col4, _col5, _col6, _col9
                                input vertices:
                                  1 Map 8
                                Statistics: Num rows: 1162084 Data size: 434619456 Basic stats: COMPLETE Column stats: NONE
                                Map Join Operator
                                  condition map:
                                       Inner Join 0 to 1
                                  condition expressions:
                                    0 {_col1} {_col2} {_col4} {_col5} {_col9}
                                    1 {_col1}
                                  keys:
                                    0 _col6 (type: int)
                                    1 _col0 (type: int)
                                  outputColumnNames: _col1, _col2, _col4, _col5, _col9, _col11
                                  input vertices:
                                    1 Map 1
                                  Statistics: Num rows: 1278292 Data size: 478081408 Basic stats: COMPLETE Column stats: NONE
                                  Select Operator
                                    expressions: _col1 (type: string), _col11 (type: int), _col2 (type: int), _col4 (type: int), _col5 (type: int), _col9 (type: int)
                                    outputColumnNames: _col1, _col11, _col2, _col4, _col5, _col9
                                    Statistics: Num rows: 1278292 Data size: 478081408 Basic stats: COMPLETE Column stats: NONE
                                    Map Join Operator
                                      condition map:
                                           Inner Join 0 to 1
                                      condition expressions:
                                        0 {_col1} {_col4} {_col5} {_col7} {_col8} {_col9} {_col10} {_col11} {_col13} {_col17} {_col18}
                                        1 {_col1} {_col4} {_col5} {_col9} {_col11}
                                      keys:
                                        0 _col2 (type: int)
                                        1 _col2 (type: int)
                                      outputColumnNames: _col1, _col4, _col5, _col7, _col8, _col9, _col10, _col11, _col13, _col17, _col18, _col20, _col23, _col24, _col28, _col30
                                      input vertices:
                                        0 Map 16
                                      Statistics: Num rows: 1572981 Data size: 68652632 Basic stats: COMPLETE Column stats: NONE
                                      Reduce Output Operator
                                        key expressions: _col1 (type: int), _col8 (type: int)
                                        sort order: ++
                                        Map-reduce partition columns: _col1 (type: int), _col8 (type: int)
                                        Statistics: Num rows: 1572981 Data size: 68652632 Basic stats: COMPLETE Column stats: NONE
                                        value expressions: _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float), _col13 (type: string), _col17 (type: string), _col18 (type: string), _col20 (type: string), _col23 (type: int), _col24 (type: int), _col28 (type: int), _col30 (type: int)
                  Execution mode: vectorized
              Map 36 
                  Map Operator Tree:
                      TableScan
                        alias: cd1
                        filterExpr: cd_demo_sk is not null (type: boolean)
                        Statistics: Num rows: 1920800 Data size: 718379200 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: cd_demo_sk is not null (type: boolean)
                          Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cd_demo_sk (type: int), cd_marital_status (type: string)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col0} {_col1} {_col2} {_col4} {_col5} {_col6} {_col7} {_col8} {_col9} {_col10} {_col11}
                                1 {_col1}
                              keys:
                                0 _col3 (type: int)
                                1 _col0 (type: int)
                              outputColumnNames: _col0, _col1, _col2, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col13
                              input vertices:
                                0 Map 18
                              Statistics: Num rows: 1181805 Data size: 51579732 Basic stats: COMPLETE Column stats: NONE
                              Map Join Operator
                                condition map:
                                     Inner Join 0 to 1
                                condition expressions:
                                  0 {_col1} {_col2} {_col4} {_col5} {_col6} {_col7} {_col8} {_col9} {_col10} {_col11} {_col13}
                                  1 
                                keys:
                                  0 _col0 (type: int)
                                  1 _col0 (type: int)
                                outputColumnNames: _col1, _col2, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col13
                                input vertices:
                                  1 Map 25
                                Statistics: Num rows: 1299985 Data size: 56737708 Basic stats: COMPLETE Column stats: NONE
                                Map Join Operator
                                  condition map:
                                       Inner Join 0 to 1
                                  condition expressions:
                                    0 {_col1} {_col2} {_col4} {_col5} {_col7} {_col8} {_col9} {_col10} {_col11} {_col13}
                                    1 {_col1} {_col2}
                                  keys:
                                    0 _col6 (type: int)
                                    1 _col0 (type: int)
                                  outputColumnNames: _col1, _col2, _col4, _col5, _col7, _col8, _col9, _col10, _col11, _col13, _col17, _col18
                                  input vertices:
                                    1 Map 41
                                  Statistics: Num rows: 1429983 Data size: 62411480 Basic stats: COMPLETE Column stats: NONE
                                  Reduce Output Operator
                                    key expressions: _col2 (type: int)
                                    sort order: +
                                    Map-reduce partition columns: _col2 (type: int)
                                    Statistics: Num rows: 1429983 Data size: 62411480 Basic stats: COMPLETE Column stats: NONE
                                    value expressions: _col1 (type: int), _col4 (type: int), _col5 (type: int), _col7 (type: int), _col8 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float), _col13 (type: string), _col17 (type: string), _col18 (type: string)
                  Execution mode: vectorized
              Map 37 
                  Map Operator Tree:
                      TableScan
                        alias: ib1
                        filterExpr: ib_income_band_sk is not null (type: boolean)
                        Statistics: Num rows: 20 Data size: 240 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ib_income_band_sk is not null (type: boolean)
                          Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ib_income_band_sk (type: int)
                            outputColumnNames: _col0
                            Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 10 Data size: 120 Basic stats: COMPLETE Column stats: NONE
                  Execution mode: vectorized
              Map 38 
                  Map Operator Tree:
                      TableScan
                        alias: cd1
                        filterExpr: cd_demo_sk is not null (type: boolean)
                        Statistics: Num rows: 1920800 Data size: 718379200 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: cd_demo_sk is not null (type: boolean)
                          Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cd_demo_sk (type: int), cd_marital_status (type: string)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 960400 Data size: 359189600 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col1}
                                1 {_col0} {_col2} {_col3} {_col4} {_col5}
                              keys:
                                0 _col0 (type: int)
                                1 _col1 (type: int)
                              outputColumnNames: _col1, _col2, _col4, _col5, _col6, _col7
                              input vertices:
                                1 Map 15
                              Statistics: Num rows: 1056440 Data size: 395108576 Basic stats: COMPLETE Column stats: NONE
                              Map Join Operator
                                condition map:
                                     Inner Join 0 to 1
                                condition expressions:
                                  0 {_col1} {_col2} {_col4} {_col5} {_col6}
                                  1 {_col1}
                                keys:
                                  0 _col7 (type: int)
                                  1 _col0 (type: int)
                                outputColumnNames: _col1, _col2, _col4, _col5, _col6, _col9
                                input vertices:
                                  1 Map 31
                                Statistics: Num rows: 1162084 Data size: 434619456 Basic stats: COMPLETE Column stats: NONE
                                Map Join Operator
                                  condition map:
                                       Inner Join 0 to 1
                                  condition expressions:
                                    0 {_col1} {_col2} {_col4} {_col5} {_col9}
                                    1 {_col1}
                                  keys:
                                    0 _col6 (type: int)
                                    1 _col0 (type: int)
                                  outputColumnNames: _col1, _col2, _col4, _col5, _col9, _col11
                                  input vertices:
                                    1 Map 5
                                  Statistics: Num rows: 1278292 Data size: 478081408 Basic stats: COMPLETE Column stats: NONE
                                  Select Operator
                                    expressions: _col1 (type: string), _col11 (type: int), _col2 (type: int), _col4 (type: int), _col5 (type: int), _col9 (type: int)
                                    outputColumnNames: _col1, _col11, _col2, _col4, _col5, _col9
                                    Statistics: Num rows: 1278292 Data size: 478081408 Basic stats: COMPLETE Column stats: NONE
                                    Map Join Operator
                                      condition map:
                                           Inner Join 0 to 1
                                      condition expressions:
                                        0 {_col1} {_col4} {_col5} {_col7} {_col8} {_col9} {_col10} {_col11} {_col13} {_col17} {_col18}
                                        1 {_col1} {_col4} {_col5} {_col9} {_col11}
                                      keys:
                                        0 _col2 (type: int)
                                        1 _col2 (type: int)
                                      outputColumnNames: _col1, _col4, _col5, _col7, _col8, _col9, _col10, _col11, _col13, _col17, _col18, _col20, _col23, _col24, _col28, _col30
                                      input vertices:
                                        0 Map 36
                                      Statistics: Num rows: 1572981 Data size: 68652632 Basic stats: COMPLETE Column stats: NONE
                                      Reduce Output Operator
                                        key expressions: _col1 (type: int), _col8 (type: int)
                                        sort order: ++
                                        Map-reduce partition columns: _col1 (type: int), _col8 (type: int)
                                        Statistics: Num rows: 1572981 Data size: 68652632 Basic stats: COMPLETE Column stats: NONE
                                        value expressions: _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float), _col13 (type: string), _col17 (type: string), _col18 (type: string), _col20 (type: string), _col23 (type: int), _col24 (type: int), _col28 (type: int), _col30 (type: int)
                  Execution mode: vectorized
              Map 39 
                  Map Operator Tree:
                      TableScan
                        alias: item
                        filterExpr: ((((i_color) IN ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate') and i_current_price BETWEEN 35 AND 45) and i_current_price BETWEEN 36 AND 50) and i_item_sk is not null) (type: boolean)
                        Statistics: Num rows: 48000 Data size: 68732712 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ((((i_color) IN ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate') and i_current_price BETWEEN 35 AND 45) and i_current_price BETWEEN 36 AND 50) and i_item_sk is not null) (type: boolean)
                          Statistics: Num rows: 3000 Data size: 4295794 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: i_item_sk (type: int), i_product_name (type: string)
                            outputColumnNames: _col0, _col3
                            Statistics: Num rows: 3000 Data size: 4295794 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 3000 Data size: 4295794 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col3 (type: string)
                  Execution mode: vectorized
              Map 4 
                  Map Operator Tree:
                      TableScan
                        alias: store_returns
                        filterExpr: (sr_item_sk is not null and sr_ticket_number is not null) (type: boolean)
                        Statistics: Num rows: 55578005 Data size: 4377627636 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (sr_item_sk is not null and sr_ticket_number is not null) (type: boolean)
                          Statistics: Num rows: 13894502 Data size: 1094406968 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: sr_item_sk (type: int), sr_ticket_number (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 13894502 Data size: 1094406968 Basic stats: COMPLETE Column stats: NONE
                            Map Join Operator
                              condition map:
                                   Inner Join 0 to 1
                              condition expressions:
                                0 {_col1} {_col4} {_col5} {_col7} {_col9} {_col10} {_col11} {_col13} {_col17} {_col18} {_col20} {_col23} {_col24} {_col28} {_col30}
                                1 
                              keys:
                                0 _col1 (type: int), _col8 (type: int)
                                1 _col0 (type: int), _col1 (type: int)
                              outputColumnNames: _col1, _col4, _col5, _col7, _col9, _col10, _col11, _col13, _col17, _col18, _col20, _col23, _col24, _col28, _col30
                              input vertices:
                                0 Map 38
                              Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                              Filter Operator
                                predicate: (_col13 <> _col20) (type: boolean)
                                Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                                Select Operator
                                  expressions: _col1 (type: int), _col10 (type: float), _col11 (type: float), _col23 (type: int), _col24 (type: int), 2001 (type: int), _col28 (type: int), _col30 (type: int), _col17 (type: string), _col18 (type: string), _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float)
                                  outputColumnNames: _col1, _col10, _col11, _col16, _col17, _col21, _col23, _col25, _col27, _col28, _col4, _col5, _col7, _col9
                                  Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                                  Reduce Output Operator
                                    key expressions: _col1 (type: int)
                                    sort order: +
                                    Map-reduce partition columns: _col1 (type: int)
                                    Statistics: Num rows: 15283953 Data size: 1203847680 Basic stats: COMPLETE Column stats: NONE
                                    value expressions: _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float), _col16 (type: int), _col17 (type: int), _col21 (type: int), _col23 (type: int), _col25 (type: int), _col27 (type: string), _col28 (type: string)
                  Execution mode: vectorized
              Map 40 
                  Map Operator Tree:
                      TableScan
                        alias: catalog_returns
                        filterExpr: (cr_item_sk is not null and cr_order_number is not null) (type: boolean)
                        Statistics: Num rows: 28798881 Data size: 3057234680 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (cr_item_sk is not null and cr_order_number is not null) (type: boolean)
                          Statistics: Num rows: 7199721 Data size: 764308749 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cr_item_sk (type: int), cr_order_number (type: int), cr_refunded_cash (type: float), cr_reversed_charge (type: float), cr_store_credit (type: float)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4
                            Statistics: Num rows: 7199721 Data size: 764308749 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int), _col1 (type: int)
                              sort order: ++
                              Map-reduce partition columns: _col0 (type: int), _col1 (type: int)
                              Statistics: Num rows: 7199721 Data size: 764308749 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col2 (type: float), _col3 (type: float), _col4 (type: float)
                  Execution mode: vectorized
              Map 41 
                  Map Operator Tree:
                      TableScan
                        alias: store
                        filterExpr: ((s_store_sk is not null and s_store_name is not null) and s_zip is not null) (type: boolean)
                        Statistics: Num rows: 212 Data size: 405680 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ((s_store_sk is not null and s_store_name is not null) and s_zip is not null) (type: boolean)
                          Statistics: Num rows: 27 Data size: 51666 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: s_store_sk (type: int), s_store_name (type: string), s_zip (type: string)
                            outputColumnNames: _col0, _col1, _col2
                            Statistics: Num rows: 27 Data size: 51666 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 27 Data size: 51666 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: string), _col2 (type: string)
                  Execution mode: vectorized
              Map 42 
                  Map Operator Tree:
                      TableScan
                        alias: catalog_returns
                        filterExpr: (cr_item_sk is not null and cr_order_number is not null) (type: boolean)
                        Statistics: Num rows: 28798881 Data size: 3057234680 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (cr_item_sk is not null and cr_order_number is not null) (type: boolean)
                          Statistics: Num rows: 7199721 Data size: 764308749 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: cr_item_sk (type: int), cr_order_number (type: int), cr_refunded_cash (type: float), cr_reversed_charge (type: float), cr_store_credit (type: float)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4
                            Statistics: Num rows: 7199721 Data size: 764308749 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int), _col1 (type: int)
                              sort order: ++
                              Map-reduce partition columns: _col0 (type: int), _col1 (type: int)
                              Statistics: Num rows: 7199721 Data size: 764308749 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col2 (type: float), _col3 (type: float), _col4 (type: float)
                  Execution mode: vectorized
              Map 43 
                  Map Operator Tree:
                      TableScan
                        alias: ad1
                        filterExpr: ca_address_sk is not null (type: boolean)
                        Statistics: Num rows: 800000 Data size: 811903688 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ca_address_sk is not null (type: boolean)
                          Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ca_address_sk (type: int), ca_street_number (type: string), ca_street_name (type: string), ca_city (type: string), ca_zip (type: string)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4
                            Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string)
                  Execution mode: vectorized
              Map 44 
                  Map Operator Tree:
                      TableScan
                        alias: hd1
                        filterExpr: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                        Statistics: Num rows: 7200 Data size: 799 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                          Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: hd_demo_sk (type: int), hd_income_band_sk (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 5 
                  Map Operator Tree:
                      TableScan
                        alias: d1
                        filterExpr: d_date_sk is not null (type: boolean)
                        Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: d_date_sk is not null (type: boolean)
                          Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: d_date_sk (type: int), d_year (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 6 
                  Map Operator Tree:
                      TableScan
                        alias: store_sales
                        filterExpr: ((((((((ss_cdemo_sk is not null and ss_sold_date_sk is not null) and ss_store_sk is not null) and ss_customer_sk is not null) and ss_item_sk is not null) and ss_ticket_number is not null) and ss_hdemo_sk is not null) and ss_addr_sk is not null) and ss_promo_sk is not null) (type: boolean)
                        Statistics: Num rows: 550076554 Data size: 24008004411 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ((((((((ss_cdemo_sk is not null and ss_sold_date_sk is not null) and ss_store_sk is not null) and ss_customer_sk is not null) and ss_item_sk is not null) and ss_ticket_number is not null) and ss_hdemo_sk is not null) and ss_addr_sk is not null) and ss_promo_sk is not null) (type: boolean)
                          Statistics: Num rows: 1074369 Data size: 46890665 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ss_sold_date_sk (type: int), ss_item_sk (type: int), ss_customer_sk (type: int), ss_cdemo_sk (type: int), ss_hdemo_sk (type: int), ss_addr_sk (type: int), ss_store_sk (type: int), ss_promo_sk (type: int), ss_ticket_number (type: int), ss_wholesale_cost (type: float), ss_list_price (type: float), ss_coupon_amt (type: float)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11
                            Statistics: Num rows: 1074369 Data size: 46890665 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col3 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col3 (type: int)
                              Statistics: Num rows: 1074369 Data size: 46890665 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col0 (type: int), _col1 (type: int), _col2 (type: int), _col4 (type: int), _col5 (type: int), _col6 (type: int), _col7 (type: int), _col8 (type: int), _col9 (type: float), _col10 (type: float), _col11 (type: float)
                  Execution mode: vectorized
              Map 7 
                  Map Operator Tree:
                      TableScan
                        alias: ad1
                        filterExpr: ca_address_sk is not null (type: boolean)
                        Statistics: Num rows: 800000 Data size: 811903688 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: ca_address_sk is not null (type: boolean)
                          Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: ca_address_sk (type: int), ca_street_number (type: string), ca_street_name (type: string), ca_city (type: string), ca_zip (type: string)
                            outputColumnNames: _col0, _col1, _col2, _col3, _col4
                            Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 400000 Data size: 405951844 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string)
                  Execution mode: vectorized
              Map 8 
                  Map Operator Tree:
                      TableScan
                        alias: d1
                        filterExpr: d_date_sk is not null (type: boolean)
                        Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: d_date_sk is not null (type: boolean)
                          Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: d_date_sk (type: int), d_year (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 36525 Data size: 40871475 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Map 9 
                  Map Operator Tree:
                      TableScan
                        alias: hd1
                        filterExpr: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                        Statistics: Num rows: 7200 Data size: 799 Basic stats: COMPLETE Column stats: NONE
                        Filter Operator
                          predicate: (hd_demo_sk is not null and hd_income_band_sk is not null) (type: boolean)
                          Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                          Select Operator
                            expressions: hd_demo_sk (type: int), hd_income_band_sk (type: int)
                            outputColumnNames: _col0, _col1
                            Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                            Reduce Output Operator
                              key expressions: _col0 (type: int)
                              sort order: +
                              Map-reduce partition columns: _col0 (type: int)
                              Statistics: Num rows: 1800 Data size: 199 Basic stats: COMPLETE Column stats: NONE
                              value expressions: _col1 (type: int)
                  Execution mode: vectorized
              Reducer 11 
                  Reduce Operator Tree:
                    Group By Operator
                      aggregations: sum(VALUE._col0), sum(VALUE._col1)
                      keys: KEY._col0 (type: int)
                      mode: mergepartial
                      outputColumnNames: _col0, _col1, _col2
                      Statistics: Num rows: 39400588 Data size: 5347397120 Basic stats: COMPLETE Column stats: NONE
                      Filter Operator
                        predicate: (_col1 > (UDFToDouble(2) * _col2)) (type: boolean)
                        Statistics: Num rows: 13133529 Data size: 1782465661 Basic stats: COMPLETE Column stats: NONE
                        Select Operator
                          expressions: _col0 (type: int)
                          outputColumnNames: _col0
                          Statistics: Num rows: 13133529 Data size: 1782465661 Basic stats: COMPLETE Column stats: NONE
                          Map Join Operator
                            condition map:
                                 Inner Join 0 to 1
                            condition expressions:
                              0 {_col1} {_col4} {_col5} {_col7} {_col9} {_col10} {_col11} {_col16} {_col17} {_col21} {_col23} {_col25} {_col27} {_col28}
                              1 
                            keys:
                              0 _col1 (type: int)
                              1 _col0 (type: int)
                            outputColumnNames: _col1, _col4, _col5, _col7, _col9, _col10, _col11, _col16, _col17, _col21, _col23, _col25, _col27, _col28
                            input vertices:
                              0 Map 4
                            Statistics: Num rows: 16812348 Data size: 1324232448 Basic stats: COMPLETE Column stats: NONE
                            Select Operator
                              expressions: _col1 (type: int), _col10 (type: float), _col11 (type: float), _col16 (type: int), _col17 (type: int), _col21 (type: int), _col23 (type: int), _col25 (type: int), _col27 (type: string), _col28 (type: string), _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float)
                              outputColumnNames: _col1, _col10, _col11, _col16, _col17, _col21, _col23, _col25, _col27, _col28, _col4, _col5, _col7, _col9
                              Statistics: Num rows: 16812348 Data size: 1324232448 Basic stats: COMPLETE Column stats: NONE
                              Map Join Operator
                                condition map:
                                     Inner Join 0 to 1
                                condition expressions:
                                  0 {_col1}
                                  1 {_col1} {_col5} {_col7} {_col9} {_col10} {_col11} {_col16} {_col17} {_col21} {_col23} {_col25} {_col27} {_col28}
                                keys:
                                  0 _col0 (type: int)
                                  1 _col4 (type: int)
                                outputColumnNames: _col1, _col3, _col7, _col9, _col11, _col12, _col13, _col18, _col19, _col23, _col25, _col27, _col29, _col30
                                input vertices:
                                  0 Map 24
                                Statistics: Num rows: 18493584 Data size: 1456655744 Basic stats: COMPLETE Column stats: NONE
                                Select Operator
                                  expressions: _col1 (type: int), _col11 (type: float), _col12 (type: float), _col13 (type: float), _col18 (type: int), _col19 (type: int), _col23 (type: int), _col25 (type: int), _col27 (type: int), _col29 (type: string), _col3 (type: int), _col30 (type: string), _col7 (type: int), _col9 (type: int)
                                  outputColumnNames: _col1, _col11, _col12, _col13, _col18, _col19, _col23, _col25, _col27, _col29, _col3, _col30, _col7, _col9
                                  Statistics: Num rows: 18493584 Data size: 1456655744 Basic stats: COMPLETE Column stats: NONE
                                  Map Join Operator
                                    condition map:
                                         Inner Join 0 to 1
                                    condition expressions:
                                      0 {_col1}
                                      1 {_col1} {_col3} {_col7} {_col9} {_col11} {_col12} {_col13} {_col19} {_col23} {_col25} {_col27} {_col29} {_col30}
                                    keys:
                                      0 _col0 (type: int)
                                      1 _col18 (type: int)
                                    outputColumnNames: _col1, _col3, _col5, _col9, _col11, _col13, _col14, _col15, _col21, _col25, _col27, _col29, _col31, _col32
                                    input vertices:
                                      0 Map 27
                                    Statistics: Num rows: 20342942 Data size: 1602321408 Basic stats: COMPLETE Column stats: NONE
                                    Map Join Operator
                                      condition map:
                                           Inner Join 0 to 1
                                      condition expressions:
                                        0 {_col1} {_col3} {_col9} {_col11} {_col13} {_col14} {_col15} {_col21} {_col25} {_col27} {_col29} {_col31} {_col32}
                                        1 {_col0} {_col3}
                                      keys:
                                        0 _col5 (type: int)
                                        1 _col0 (type: int)
                                      outputColumnNames: _col1, _col3, _col9, _col11, _col13, _col14, _col15, _col21, _col25, _col27, _col29, _col31, _col32, _col38, _col41
                                      input vertices:
                                        1 Map 39
                                      Statistics: Num rows: 22377236 Data size: 1762553600 Basic stats: COMPLETE Column stats: NONE
                                      Select Operator
                                        expressions: _col1 (type: int), _col11 (type: int), _col13 (type: float), _col14 (type: float), _col15 (type: float), _col21 (type: int), _col25 (type: int), _col27 (type: int), _col29 (type: int), _col3 (type: int), _col31 (type: string), _col32 (type: string), _col38 (type: int), _col41 (type: string), _col9 (type: int)
                                        outputColumnNames: _col1, _col11, _col13, _col14, _col15, _col21, _col25, _col27, _col29, _col3, _col31, _col32, _col38, _col41, _col9
                                        Statistics: Num rows: 22377236 Data size: 1762553600 Basic stats: COMPLETE Column stats: NONE
                                        Map Join Operator
                                          condition map:
                                               Inner Join 0 to 1
                                          condition expressions:
                                            0 
                                            1 {_col1} {_col9} {_col11} {_col13} {_col14} {_col15} {_col21} {_col25} {_col27} {_col29} {_col31} {_col32} {_col38} {_col41}
                                          keys:
                                            0 _col0 (type: int)
                                            1 _col3 (type: int)
                                          outputColumnNames: _col2, _col10, _col12, _col14, _col15, _col16, _col22, _col26, _col28, _col30, _col32, _col33, _col39, _col42
                                          input vertices:
                                            0 Map 22
                                          Statistics: Num rows: 24614960 Data size: 1938808960 Basic stats: COMPLETE Column stats: NONE
                                          Select Operator
                                            expressions: _col10 (type: int), _col12 (type: int), _col14 (type: float), _col15 (type: float), _col16 (type: float), _col2 (type: int), _col22 (type: int), _col26 (type: int), _col28 (type: int), _col30 (type: int), _col32 (type: string), _col33 (type: string), _col39 (type: int), _col42 (type: string)
                                            outputColumnNames: _col10, _col12, _col14, _col15, _col16, _col2, _col22, _col26, _col28, _col30, _col32, _col33, _col39, _col42
                                            Statistics: Num rows: 24614960 Data size: 1938808960 Basic stats: COMPLETE Column stats: NONE
                                            Map Join Operator
                                              condition map:
                                                   Inner Join 0 to 1
                                              condition expressions:
                                                0 
                                                1 {_col10} {_col12} {_col14} {_col15} {_col16} {_col22} {_col26} {_col28} {_col30} {_col32} {_col33} {_col39} {_col42}
                                              keys:
                                                0 _col0 (type: int)
                                                1 _col2 (type: int)
                                              outputColumnNames: _col11, _col13, _col15, _col16, _col17, _col23, _col27, _col29, _col31, _col33, _col34, _col40, _col43
                                              input vertices:
                                                0 Map 30
                                              Statistics: Num rows: 27076456 Data size: 2132689920 Basic stats: COMPLETE Column stats: NONE
                                              Select Operator
                                                expressions: _col11 (type: int), _col13 (type: int), _col15 (type: float), _col16 (type: float), _col17 (type: float), _col23 (type: int), _col27 (type: int), _col29 (type: int), _col31 (type: int), _col33 (type: string), _col34 (type: string), _col40 (type: int), _col43 (type: string)
                                                outputColumnNames: _col11, _col13, _col15, _col16, _col17, _col23, _col27, _col29, _col31, _col33, _col34, _col40, _col43
                                                Statistics: Num rows: 27076456 Data size: 2132689920 Basic stats: COMPLETE Column stats: NONE
                                                Map Join Operator
                                                  condition map:
                                                       Inner Join 0 to 1
                                                  condition expressions:
                                                    0 {_col1} {_col2} {_col3} {_col4}
                                                    1 {_col13} {_col15} {_col16} {_col17} {_col23} {_col27} {_col29} {_col31} {_col33} {_col34} {_col40} {_col43}
                                                  keys:
                                                    0 _col0 (type: int)
                                                    1 _col11 (type: int)
                                                  outputColumnNames: _col1, _col2, _col3, _col4, _col18, _col20, _col21, _col22, _col28, _col32, _col34, _col36, _col38, _col39, _col45, _col48
                                                  input vertices:
                                                    0 Map 32
                                                  Statistics: Num rows: 29784102 Data size: 2345958912 Basic stats: COMPLETE Column stats: NONE
                                                  Select Operator
                                                    expressions: _col1 (type: string), _col18 (type: int), _col2 (type: string), _col20 (type: float), _col21 (type: float), _col22 (type: float), _col28 (type: int), _col3 (type: string), _col32 (type: int), _col34 (type: int), _col36 (type: int), _col38 (type: string), _col39 (type: string), _col4 (type: string), _col45 (type: int), _col48 (type: string)
                                                    outputColumnNames: _col1, _col18, _col2, _col20, _col21, _col22, _col28, _col3, _col32, _col34, _col36, _col38, _col39, _col4, _col45, _col48
                                                    Statistics: Num rows: 29784102 Data size: 2345958912 Basic stats: COMPLETE Column stats: NONE
                                                    Map Join Operator
                                                      condition map:
                                                           Inner Join 0 to 1
                                                      condition expressions:
                                                        0 {_col1} {_col2} {_col3} {_col4}
                                                        1 {_col1} {_col2} {_col3} {_col4} {_col18} {_col20} {_col21} {_col22} {_col32} {_col34} {_col36} {_col38} {_col39} {_col45} {_col48}
                                                      keys:
                                                        0 _col0 (type: int)
                                                        1 _col28 (type: int)
                                                      outputColumnNames: _col1, _col2, _col3, _col4, _col6, _col7, _col8, _col9, _col23, _col25, _col26, _col27, _col37, _col39, _col41, _col43, _col44, _col50, _col53
                                                      input vertices:
                                                        0 Map 7
                                                      Statistics: Num rows: 32762512 Data size: 2580554752 Basic stats: COMPLETE Column stats: NONE
                                                      Select Operator
                                                        expressions: _col1 (type: string), _col2 (type: string), _col23 (type: int), _col25 (type: float), _col26 (type: float), _col27 (type: float), _col3 (type: string), _col37 (type: int), _col39 (type: int), _col4 (type: string), _col41 (type: int), _col43 (type: string), _col44 (type: string), _col50 (type: int), _col53 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string)
                                                        outputColumnNames: _col1, _col2, _col23, _col25, _col26, _col27, _col3, _col37, _col39, _col4, _col41, _col43, _col44, _col50, _col53, _col6, _col7, _col8, _col9
                                                        Statistics: Num rows: 32762512 Data size: 2580554752 Basic stats: COMPLETE Column stats: NONE
                                                        Map Join Operator
                                                          condition map:
                                                               Inner Join 0 to 1
                                                          condition expressions:
                                                            0 
                                                            1 {_col1} {_col2} {_col3} {_col4} {_col6} {_col7} {_col8} {_col9} {_col25} {_col26} {_col27} {_col37} {_col39} {_col41} {_col43} {_col44} {_col50} {_col53}
                                                          keys:
                                                            0 _col0 (type: int)
                                                            1 _col23 (type: int)
                                                          outputColumnNames: _col2, _col3, _col4, _col5, _col7, _col8, _col9, _col10, _col26, _col27, _col28, _col38, _col40, _col42, _col44, _col45, _col51, _col54
                                                          input vertices:
                                                            0 Map 26
                                                          Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                          Select Operator
                                                            expressions: _col54 (type: string), _col51 (type: int), _col44 (type: string), _col45 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col38 (type: int), _col40 (type: int), _col42 (type: int), _col26 (type: float), _col27 (type: float), _col28 (type: float)
                                                            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17
                                                            Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                            Group By Operator
                                                              aggregations: count(), sum(_col15), sum(_col16), sum(_col17)
                                                              keys: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: int), _col14 (type: int)
                                                              mode: hash
                                                              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18
                                                              Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                              Reduce Output Operator
                                                                key expressions: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: int), _col14 (type: int)
                                                                sort order: +++++++++++++++
                                                                Map-reduce partition columns: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: int), _col14 (type: int)
                                                                Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                                value expressions: _col15 (type: bigint), _col16 (type: double), _col17 (type: double), _col18 (type: double)
              Reducer 12 
                  Reduce Operator Tree:
                    Group By Operator
                      aggregations: count(VALUE._col0), sum(VALUE._col1), sum(VALUE._col2), sum(VALUE._col3)
                      keys: KEY._col0 (type: string), KEY._col1 (type: int), KEY._col2 (type: string), KEY._col3 (type: string), KEY._col4 (type: string), KEY._col5 (type: string), KEY._col6 (type: string), KEY._col7 (type: string), KEY._col8 (type: string), KEY._col9 (type: string), KEY._col10 (type: string), KEY._col11 (type: string), KEY._col12 (type: int), KEY._col13 (type: int), KEY._col14 (type: int)
                      mode: mergepartial
                      outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18
                      Statistics: Num rows: 18019382 Data size: 1419305088 Basic stats: COMPLETE Column stats: NONE
                      Select Operator
                        expressions: _col1 (type: int), _col2 (type: string), _col3 (type: string), _col12 (type: int), _col15 (type: bigint), _col16 (type: double), _col17 (type: double), _col18 (type: double)
                        outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7
                        Statistics: Num rows: 18019382 Data size: 1419305088 Basic stats: COMPLETE Column stats: NONE
                        Reduce Output Operator
                          key expressions: _col0 (type: int), _col1 (type: string), _col2 (type: string)
                          sort order: +++
                          Map-reduce partition columns: _col0 (type: int), _col1 (type: string), _col2 (type: string)
                          Statistics: Num rows: 18019382 Data size: 1419305088 Basic stats: COMPLETE Column stats: NONE
                          value expressions: _col3 (type: int), _col4 (type: bigint), _col5 (type: double), _col6 (type: double), _col7 (type: double)
                  Execution mode: vectorized
              Reducer 13 
                  Reduce Operator Tree:
                    Merge Join Operator
                      condition map:
                           Inner Join 0 to 1
                      condition expressions:
                        0 {VALUE._col0} {KEY.reducesinkkey1} {KEY.reducesinkkey2} {VALUE._col1} {VALUE._col2} {VALUE._col3} {VALUE._col4} {VALUE._col5} {VALUE._col6} {VALUE._col7} {VALUE._col8} {VALUE._col9} {VALUE._col10} {VALUE._col11} {VALUE._col12} {VALUE._col13}
                        1 {VALUE._col0} {VALUE._col1} {VALUE._col2} {VALUE._col3} {VALUE._col4}
                      outputColumnNames: _col0, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col20, _col21, _col22, _col23, _col24
                      Statistics: Num rows: 19821320 Data size: 1561235584 Basic stats: COMPLETE Column stats: NONE
                      Filter Operator
                        predicate: (_col21 <= _col13) (type: boolean)
                        Statistics: Num rows: 6607106 Data size: 520411808 Basic stats: COMPLETE Column stats: NONE
                        Select Operator
                          expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: bigint), _col14 (type: double), _col15 (type: double), _col16 (type: double), _col22 (type: double), _col23 (type: double), _col24 (type: double), _col20 (type: int), _col21 (type: bigint)
                          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20
                          Statistics: Num rows: 6607106 Data size: 520411808 Basic stats: COMPLETE Column stats: NONE
                          Reduce Output Operator
                            key expressions: _col0 (type: string), _col1 (type: string), _col20 (type: bigint)
                            sort order: +++
                            Statistics: Num rows: 6607106 Data size: 520411808 Basic stats: COMPLETE Column stats: NONE
                            value expressions: _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: int), _col12 (type: bigint), _col13 (type: double), _col14 (type: double), _col15 (type: double), _col16 (type: double), _col17 (type: double), _col18 (type: double), _col19 (type: int)
              Reducer 14 
                  Reduce Operator Tree:
                    Select Operator
                      expressions: KEY.reducesinkkey0 (type: string), KEY.reducesinkkey1 (type: string), VALUE._col0 (type: string), VALUE._col1 (type: string), VALUE._col2 (type: string), VALUE._col3 (type: string), VALUE._col4 (type: string), VALUE._col5 (type: string), VALUE._col6 (type: string), VALUE._col7 (type: string), VALUE._col8 (type: string), VALUE._col9 (type: int), VALUE._col10 (type: bigint), VALUE._col11 (type: double), VALUE._col12 (type: double), VALUE._col13 (type: double), VALUE._col14 (type: double), VALUE._col15 (type: double), VALUE._col16 (type: double), VALUE._col17 (type: int), KEY.reducesinkkey2 (type: bigint)
                      outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20
                      Statistics: Num rows: 6607106 Data size: 520411808 Basic stats: COMPLETE Column stats: NONE
                      File Output Operator
                        compressed: false
                        Statistics: Num rows: 6607106 Data size: 520411808 Basic stats: COMPLETE Column stats: NONE
                        table:
                            input format: org.apache.hadoop.mapred.TextInputFormat
                            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
                  Execution mode: vectorized
              Reducer 20 
                  Reduce Operator Tree:
                    Group By Operator
                      aggregations: sum(VALUE._col0), sum(VALUE._col1)
                      keys: KEY._col0 (type: int)
                      mode: mergepartial
                      outputColumnNames: _col0, _col1, _col2
                      Statistics: Num rows: 39400588 Data size: 5347397120 Basic stats: COMPLETE Column stats: NONE
                      Filter Operator
                        predicate: (_col1 > (UDFToDouble(2) * _col2)) (type: boolean)
                        Statistics: Num rows: 13133529 Data size: 1782465661 Basic stats: COMPLETE Column stats: NONE
                        Select Operator
                          expressions: _col0 (type: int)
                          outputColumnNames: _col0
                          Statistics: Num rows: 13133529 Data size: 1782465661 Basic stats: COMPLETE Column stats: NONE
                          Map Join Operator
                            condition map:
                                 Inner Join 0 to 1
                            condition expressions:
                              0 {_col1} {_col4} {_col5} {_col7} {_col9} {_col10} {_col11} {_col16} {_col17} {_col21} {_col23} {_col25} {_col27} {_col28}
                              1 
                            keys:
                              0 _col1 (type: int)
                              1 _col0 (type: int)
                            outputColumnNames: _col1, _col4, _col5, _col7, _col9, _col10, _col11, _col16, _col17, _col21, _col23, _col25, _col27, _col28
                            input vertices:
                              0 Map 33
                            Statistics: Num rows: 16812348 Data size: 1324232448 Basic stats: COMPLETE Column stats: NONE
                            Select Operator
                              expressions: _col1 (type: int), _col10 (type: float), _col11 (type: float), _col16 (type: int), _col17 (type: int), _col21 (type: int), _col23 (type: int), _col25 (type: int), _col27 (type: string), _col28 (type: string), _col4 (type: int), _col5 (type: int), _col7 (type: int), _col9 (type: float)
                              outputColumnNames: _col1, _col10, _col11, _col16, _col17, _col21, _col23, _col25, _col27, _col28, _col4, _col5, _col7, _col9
                              Statistics: Num rows: 16812348 Data size: 1324232448 Basic stats: COMPLETE Column stats: NONE
                              Map Join Operator
                                condition map:
                                     Inner Join 0 to 1
                                condition expressions:
                                  0 {_col1}
                                  1 {_col1} {_col5} {_col7} {_col9} {_col10} {_col11} {_col16} {_col17} {_col21} {_col23} {_col25} {_col27} {_col28}
                                keys:
                                  0 _col0 (type: int)
                                  1 _col4 (type: int)
                                outputColumnNames: _col1, _col3, _col7, _col9, _col11, _col12, _col13, _col18, _col19, _col23, _col25, _col27, _col29, _col30
                                input vertices:
                                  0 Map 9
                                Statistics: Num rows: 18493584 Data size: 1456655744 Basic stats: COMPLETE Column stats: NONE
                                Select Operator
                                  expressions: _col1 (type: int), _col11 (type: float), _col12 (type: float), _col13 (type: float), _col18 (type: int), _col19 (type: int), _col23 (type: int), _col25 (type: int), _col27 (type: int), _col29 (type: string), _col3 (type: int), _col30 (type: string), _col7 (type: int), _col9 (type: int)
                                  outputColumnNames: _col1, _col11, _col12, _col13, _col18, _col19, _col23, _col25, _col27, _col29, _col3, _col30, _col7, _col9
                                  Statistics: Num rows: 18493584 Data size: 1456655744 Basic stats: COMPLETE Column stats: NONE
                                  Map Join Operator
                                    condition map:
                                         Inner Join 0 to 1
                                    condition expressions:
                                      0 {_col1}
                                      1 {_col1} {_col3} {_col7} {_col9} {_col11} {_col12} {_col13} {_col19} {_col23} {_col25} {_col27} {_col29} {_col30}
                                    keys:
                                      0 _col0 (type: int)
                                      1 _col18 (type: int)
                                    outputColumnNames: _col1, _col3, _col5, _col9, _col11, _col13, _col14, _col15, _col21, _col25, _col27, _col29, _col31, _col32
                                    input vertices:
                                      0 Map 44
                                    Statistics: Num rows: 20342942 Data size: 1602321408 Basic stats: COMPLETE Column stats: NONE
                                    Map Join Operator
                                      condition map:
                                           Inner Join 0 to 1
                                      condition expressions:
                                        0 {_col1} {_col3} {_col9} {_col11} {_col13} {_col14} {_col15} {_col21} {_col25} {_col27} {_col29} {_col31} {_col32}
                                        1 {_col0} {_col3}
                                      keys:
                                        0 _col5 (type: int)
                                        1 _col0 (type: int)
                                      outputColumnNames: _col1, _col3, _col9, _col11, _col13, _col14, _col15, _col21, _col25, _col27, _col29, _col31, _col32, _col38, _col41
                                      input vertices:
                                        1 Map 29
                                      Statistics: Num rows: 22377236 Data size: 1762553600 Basic stats: COMPLETE Column stats: NONE
                                      Select Operator
                                        expressions: _col1 (type: int), _col11 (type: int), _col13 (type: float), _col14 (type: float), _col15 (type: float), _col21 (type: int), _col25 (type: int), _col27 (type: int), _col29 (type: int), _col3 (type: int), _col31 (type: string), _col32 (type: string), _col38 (type: int), _col41 (type: string), _col9 (type: int)
                                        outputColumnNames: _col1, _col11, _col13, _col14, _col15, _col21, _col25, _col27, _col29, _col3, _col31, _col32, _col38, _col41, _col9
                                        Statistics: Num rows: 22377236 Data size: 1762553600 Basic stats: COMPLETE Column stats: NONE
                                        Map Join Operator
                                          condition map:
                                               Inner Join 0 to 1
                                          condition expressions:
                                            0 
                                            1 {_col1} {_col9} {_col11} {_col13} {_col14} {_col15} {_col21} {_col25} {_col27} {_col29} {_col31} {_col32} {_col38} {_col41}
                                          keys:
                                            0 _col0 (type: int)
                                            1 _col3 (type: int)
                                          outputColumnNames: _col2, _col10, _col12, _col14, _col15, _col16, _col22, _col26, _col28, _col30, _col32, _col33, _col39, _col42
                                          input vertices:
                                            0 Map 37
                                          Statistics: Num rows: 24614960 Data size: 1938808960 Basic stats: COMPLETE Column stats: NONE
                                          Select Operator
                                            expressions: _col10 (type: int), _col12 (type: int), _col14 (type: float), _col15 (type: float), _col16 (type: float), _col2 (type: int), _col22 (type: int), _col26 (type: int), _col28 (type: int), _col30 (type: int), _col32 (type: string), _col33 (type: string), _col39 (type: int), _col42 (type: string)
                                            outputColumnNames: _col10, _col12, _col14, _col15, _col16, _col2, _col22, _col26, _col28, _col30, _col32, _col33, _col39, _col42
                                            Statistics: Num rows: 24614960 Data size: 1938808960 Basic stats: COMPLETE Column stats: NONE
                                            Map Join Operator
                                              condition map:
                                                   Inner Join 0 to 1
                                              condition expressions:
                                                0 
                                                1 {_col10} {_col12} {_col14} {_col15} {_col16} {_col22} {_col26} {_col28} {_col30} {_col32} {_col33} {_col39} {_col42}
                                              keys:
                                                0 _col0 (type: int)
                                                1 _col2 (type: int)
                                              outputColumnNames: _col11, _col13, _col15, _col16, _col17, _col23, _col27, _col29, _col31, _col33, _col34, _col40, _col43
                                              input vertices:
                                                0 Map 17
                                              Statistics: Num rows: 27076456 Data size: 2132689920 Basic stats: COMPLETE Column stats: NONE
                                              Select Operator
                                                expressions: _col11 (type: int), _col13 (type: int), _col15 (type: float), _col16 (type: float), _col17 (type: float), _col23 (type: int), _col27 (type: int), _col29 (type: int), _col31 (type: int), _col33 (type: string), _col34 (type: string), _col40 (type: int), _col43 (type: string)
                                                outputColumnNames: _col11, _col13, _col15, _col16, _col17, _col23, _col27, _col29, _col31, _col33, _col34, _col40, _col43
                                                Statistics: Num rows: 27076456 Data size: 2132689920 Basic stats: COMPLETE Column stats: NONE
                                                Map Join Operator
                                                  condition map:
                                                       Inner Join 0 to 1
                                                  condition expressions:
                                                    0 {_col1} {_col2} {_col3} {_col4}
                                                    1 {_col13} {_col15} {_col16} {_col17} {_col23} {_col27} {_col29} {_col31} {_col33} {_col34} {_col40} {_col43}
                                                  keys:
                                                    0 _col0 (type: int)
                                                    1 _col11 (type: int)
                                                  outputColumnNames: _col1, _col2, _col3, _col4, _col18, _col20, _col21, _col22, _col28, _col32, _col34, _col36, _col38, _col39, _col45, _col48
                                                  input vertices:
                                                    0 Map 43
                                                  Statistics: Num rows: 29784102 Data size: 2345958912 Basic stats: COMPLETE Column stats: NONE
                                                  Select Operator
                                                    expressions: _col1 (type: string), _col18 (type: int), _col2 (type: string), _col20 (type: float), _col21 (type: float), _col22 (type: float), _col28 (type: int), _col3 (type: string), _col32 (type: int), _col34 (type: int), _col36 (type: int), _col38 (type: string), _col39 (type: string), _col4 (type: string), _col45 (type: int), _col48 (type: string)
                                                    outputColumnNames: _col1, _col18, _col2, _col20, _col21, _col22, _col28, _col3, _col32, _col34, _col36, _col38, _col39, _col4, _col45, _col48
                                                    Statistics: Num rows: 29784102 Data size: 2345958912 Basic stats: COMPLETE Column stats: NONE
                                                    Map Join Operator
                                                      condition map:
                                                           Inner Join 0 to 1
                                                      condition expressions:
                                                        0 {_col1} {_col2} {_col3} {_col4}
                                                        1 {_col1} {_col2} {_col3} {_col4} {_col18} {_col20} {_col21} {_col22} {_col32} {_col34} {_col36} {_col38} {_col39} {_col45} {_col48}
                                                      keys:
                                                        0 _col0 (type: int)
                                                        1 _col28 (type: int)
                                                      outputColumnNames: _col1, _col2, _col3, _col4, _col6, _col7, _col8, _col9, _col23, _col25, _col26, _col27, _col37, _col39, _col41, _col43, _col44, _col50, _col53
                                                      input vertices:
                                                        0 Map 28
                                                      Statistics: Num rows: 32762512 Data size: 2580554752 Basic stats: COMPLETE Column stats: NONE
                                                      Select Operator
                                                        expressions: _col1 (type: string), _col2 (type: string), _col23 (type: int), _col25 (type: float), _col26 (type: float), _col27 (type: float), _col3 (type: string), _col37 (type: int), _col39 (type: int), _col4 (type: string), _col41 (type: int), _col43 (type: string), _col44 (type: string), _col50 (type: int), _col53 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string)
                                                        outputColumnNames: _col1, _col2, _col23, _col25, _col26, _col27, _col3, _col37, _col39, _col4, _col41, _col43, _col44, _col50, _col53, _col6, _col7, _col8, _col9
                                                        Statistics: Num rows: 32762512 Data size: 2580554752 Basic stats: COMPLETE Column stats: NONE
                                                        Map Join Operator
                                                          condition map:
                                                               Inner Join 0 to 1
                                                          condition expressions:
                                                            0 
                                                            1 {_col1} {_col2} {_col3} {_col4} {_col6} {_col7} {_col8} {_col9} {_col25} {_col26} {_col27} {_col37} {_col39} {_col41} {_col43} {_col44} {_col50} {_col53}
                                                          keys:
                                                            0 _col0 (type: int)
                                                            1 _col23 (type: int)
                                                          outputColumnNames: _col2, _col3, _col4, _col5, _col7, _col8, _col9, _col10, _col26, _col27, _col28, _col38, _col40, _col42, _col44, _col45, _col51, _col54
                                                          input vertices:
                                                            0 Map 34
                                                          Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                          Select Operator
                                                            expressions: _col54 (type: string), _col51 (type: int), _col44 (type: string), _col45 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col38 (type: int), _col40 (type: int), _col42 (type: int), _col26 (type: float), _col27 (type: float), _col28 (type: float)
                                                            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17
                                                            Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                            Group By Operator
                                                              aggregations: count(), sum(_col15), sum(_col16), sum(_col17)
                                                              keys: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: int), _col14 (type: int)
                                                              mode: hash
                                                              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18
                                                              Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                              Reduce Output Operator
                                                                key expressions: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: int), _col14 (type: int)
                                                                sort order: +++++++++++++++
                                                                Map-reduce partition columns: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: int), _col14 (type: int)
                                                                Statistics: Num rows: 36038764 Data size: 2838610176 Basic stats: COMPLETE Column stats: NONE
                                                                value expressions: _col15 (type: bigint), _col16 (type: double), _col17 (type: double), _col18 (type: double)
              Reducer 21 
                  Reduce Operator Tree:
                    Group By Operator
                      aggregations: count(VALUE._col0), sum(VALUE._col1), sum(VALUE._col2), sum(VALUE._col3)
                      keys: KEY._col0 (type: string), KEY._col1 (type: int), KEY._col2 (type: string), KEY._col3 (type: string), KEY._col4 (type: string), KEY._col5 (type: string), KEY._col6 (type: string), KEY._col7 (type: string), KEY._col8 (type: string), KEY._col9 (type: string), KEY._col10 (type: string), KEY._col11 (type: string), KEY._col12 (type: int), KEY._col13 (type: int), KEY._col14 (type: int)
                      mode: mergepartial
                      outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18
                      Statistics: Num rows: 18019382 Data size: 1419305088 Basic stats: COMPLETE Column stats: NONE
                      Select Operator
                        expressions: _col0 (type: string), _col1 (type: int), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col15 (type: bigint), _col16 (type: double), _col17 (type: double), _col18 (type: double)
                        outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16
                        Statistics: Num rows: 18019382 Data size: 1419305088 Basic stats: COMPLETE Column stats: NONE
                        Reduce Output Operator
                          key expressions: _col1 (type: int), _col2 (type: string), _col3 (type: string)
                          sort order: +++
                          Map-reduce partition columns: _col1 (type: int), _col2 (type: string), _col3 (type: string)
                          Statistics: Num rows: 18019382 Data size: 1419305088 Basic stats: COMPLETE Column stats: NONE
                          value expressions: _col0 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: int), _col13 (type: bigint), _col14 (type: double), _col15 (type: double), _col16 (type: double)
                  Execution mode: vectorized
      
        Stage: Stage-0
          Fetch Operator
            limit: -1
            Processor Tree:
              ListSink
      
      

      Query

      select cs1.product_name ,cs1.store_name ,cs1.store_zip ,cs1.b_street_number ,cs1.b_streen_name ,cs1.b_city
           ,cs1.b_zip ,cs1.c_street_number ,cs1.c_street_name ,cs1.c_city ,cs1.c_zip ,cs1.syear ,cs1.cnt
           ,cs1.s1 ,cs1.s2 ,cs1.s3
           ,cs2.s1 ,cs2.s2 ,cs2.s3 ,cs2.syear ,cs2.cnt
      from
      (select i_product_name as product_name ,i_item_sk as item_sk ,s_store_name as store_name
           ,s_zip as store_zip ,ad1.ca_street_number as b_street_number ,ad1.ca_street_name as b_streen_name
           ,ad1.ca_city as b_city ,ad1.ca_zip as b_zip ,ad2.ca_street_number as c_street_number
           ,ad2.ca_street_name as c_street_name ,ad2.ca_city as c_city ,ad2.ca_zip as c_zip
           ,d1.d_year as syear ,d2.d_year as fsyear ,d3.d_year as s2year ,count(*) as cnt
           ,sum(ss_wholesale_cost) as s1 ,sum(ss_list_price) as s2 ,sum(ss_coupon_amt) as s3
        FROM   store_sales
              JOIN store_returns ON store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number
              JOIN customer ON store_sales.ss_customer_sk = customer.c_customer_sk
              JOIN date_dim d1 ON store_sales.ss_sold_date_sk = d1.d_date_sk
              JOIN date_dim d2 ON customer.c_first_sales_date_sk = d2.d_date_sk 
              JOIN date_dim d3 ON customer.c_first_shipto_date_sk = d3.d_date_sk
              JOIN store ON store_sales.ss_store_sk = store.s_store_sk
              JOIN customer_demographics cd1 ON store_sales.ss_cdemo_sk= cd1.cd_demo_sk
              JOIN customer_demographics cd2 ON customer.c_current_cdemo_sk = cd2.cd_demo_sk
              JOIN promotion ON store_sales.ss_promo_sk = promotion.p_promo_sk
              JOIN household_demographics hd1 ON store_sales.ss_hdemo_sk = hd1.hd_demo_sk
              JOIN household_demographics hd2 ON customer.c_current_hdemo_sk = hd2.hd_demo_sk
              JOIN customer_address ad1 ON store_sales.ss_addr_sk = ad1.ca_address_sk
              JOIN customer_address ad2 ON customer.c_current_addr_sk = ad2.ca_address_sk
              JOIN income_band ib1 ON hd1.hd_income_band_sk = ib1.ib_income_band_sk
              JOIN income_band ib2 ON hd2.hd_income_band_sk = ib2.ib_income_band_sk
              JOIN item ON store_sales.ss_item_sk = item.i_item_sk
              JOIN
       (select cs_item_sk
              ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
        from catalog_sales JOIN catalog_returns
        ON catalog_sales.cs_item_sk = catalog_returns.cr_item_sk
          and catalog_sales.cs_order_number = catalog_returns.cr_order_number
        group by cs_item_sk
        having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)) cs_ui
      ON store_sales.ss_item_sk = cs_ui.cs_item_sk
        WHERE  
               cd1.cd_marital_status <> cd2.cd_marital_status and
               i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
               i_current_price between 35 and 35 + 10 and
               i_current_price between 35 + 1 and 35 + 15
      group by i_product_name ,i_item_sk ,s_store_name ,s_zip ,ad1.ca_street_number
             ,ad1.ca_street_name ,ad1.ca_city ,ad1.ca_zip ,ad2.ca_street_number
             ,ad2.ca_street_name ,ad2.ca_city ,ad2.ca_zip ,d1.d_year ,d2.d_year ,d3.d_year
      ) cs1
      JOIN
      (select i_product_name as product_name ,i_item_sk as item_sk ,s_store_name as store_name
           ,s_zip as store_zip ,ad1.ca_street_number as b_street_number ,ad1.ca_street_name as b_streen_name
           ,ad1.ca_city as b_city ,ad1.ca_zip as b_zip ,ad2.ca_street_number as c_street_number
           ,ad2.ca_street_name as c_street_name ,ad2.ca_city as c_city ,ad2.ca_zip as c_zip
           ,d1.d_year as syear ,d2.d_year as fsyear ,d3.d_year as s2year ,count(*) as cnt
           ,sum(ss_wholesale_cost) as s1 ,sum(ss_list_price) as s2 ,sum(ss_coupon_amt) as s3
        FROM   store_sales
              JOIN store_returns ON store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number
              JOIN customer ON store_sales.ss_customer_sk = customer.c_customer_sk
              JOIN date_dim d1 ON store_sales.ss_sold_date_sk = d1.d_date_sk
              JOIN date_dim d2 ON customer.c_first_sales_date_sk = d2.d_date_sk 
              JOIN date_dim d3 ON customer.c_first_shipto_date_sk = d3.d_date_sk
              JOIN store ON store_sales.ss_store_sk = store.s_store_sk
              JOIN customer_demographics cd1 ON store_sales.ss_cdemo_sk= cd1.cd_demo_sk
              JOIN customer_demographics cd2 ON customer.c_current_cdemo_sk = cd2.cd_demo_sk
              JOIN promotion ON store_sales.ss_promo_sk = promotion.p_promo_sk
              JOIN household_demographics hd1 ON store_sales.ss_hdemo_sk = hd1.hd_demo_sk
              JOIN household_demographics hd2 ON customer.c_current_hdemo_sk = hd2.hd_demo_sk
              JOIN customer_address ad1 ON store_sales.ss_addr_sk = ad1.ca_address_sk
              JOIN customer_address ad2 ON customer.c_current_addr_sk = ad2.ca_address_sk
              JOIN income_band ib1 ON hd1.hd_income_band_sk = ib1.ib_income_band_sk
              JOIN income_band ib2 ON hd2.hd_income_band_sk = ib2.ib_income_band_sk
              JOIN item ON store_sales.ss_item_sk = item.i_item_sk
              JOIN
       (select cs_item_sk
              ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
        from catalog_sales JOIN catalog_returns
        ON catalog_sales.cs_item_sk = catalog_returns.cr_item_sk
          and catalog_sales.cs_order_number = catalog_returns.cr_order_number
        group by cs_item_sk
        having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)) cs_ui
      ON store_sales.ss_item_sk = cs_ui.cs_item_sk
        WHERE  
               cd1.cd_marital_status <> cd2.cd_marital_status and
               i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
               i_current_price between 35 and 35 + 10 and
               i_current_price between 35 + 1 and 35 + 15
      group by i_product_name ,i_item_sk ,s_store_name ,s_zip ,ad1.ca_street_number
             ,ad1.ca_street_name ,ad1.ca_city ,ad1.ca_zip ,ad2.ca_street_number
             ,ad2.ca_street_name ,ad2.ca_city ,ad2.ca_zip ,d1.d_year ,d2.d_year ,d3.d_year
      ) cs2
      ON cs1.item_sk=cs2.item_sk
      where 
           cs1.syear = 2000 and
           cs2.syear = 2000 + 1 and
           cs2.cnt <= cs1.cnt and
           cs1.store_name = cs2.store_name and
           cs1.store_zip = cs2.store_zip
      order by cs1.product_name ,cs1.store_name ,cs2.cnt
      

      Attachments

        1. HIVE-8263.1.patch
          2 kB
          Harish Butani
        2. Q64_cbo_on_explain_log.txt.zip
          1.35 MB
          Mostafa Mokhtar

        Issue Links

          Activity

            People

              rhbutani Harish Butani
              mmokhtar Mostafa Mokhtar
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: