hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mostafa Mokhtar (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HIVE-8280) CBO : When filter is applied on dimension table PK/FK code path is not in effect.
Date Fri, 26 Sep 2014 22:51:34 GMT
Mostafa Mokhtar created HIVE-8280:
-------------------------------------

             Summary: CBO : When filter is applied on dimension table PK/FK code path is not in effect.
                 Key: HIVE-8280
                 URL: https://issues.apache.org/jira/browse/HIVE-8280
             Project: Hive
          Issue Type: Bug
          Components: CBO
    Affects Versions: 0.14.0
            Reporter: Mostafa Mokhtar
            Assignee: Harish Butani
             Fix For: 0.14.0


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
{code}
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

{code}

Physical plan 
{code}
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

{code}


Query 
{code}
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
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message