[
https://issues.apache.org/jira/browse/HIVE-8280?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14163080#comment-14163080
]
Mostafa Mokhtar commented on HIVE-8280:
---------------------------------------
+1
With latest patch issue is fixed.
{code}
2014-10-07 19:56:31,721 DEBUG [main]: parse.SemanticAnalyzer
(SemanticAnalyzer.java:apply(12316)) - HiveSortRel(sort0=[$0], sort1=[$1],
sort2=[$20], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.566394015453287,
cumulative cost = {6.108050195995289E8 rows, 5.566394015453287 cpu, 0.0 io}, id
= 1321
HiveProjectRel(product_name=[$0], store_name=[$2], store_zip=[$3],
b_street_number=[$4], b_streen_name=[$5], b_city=[$6], b_zip=[$7],
c_street_number=[$8], c_street_name=[$9], c_city=[$10], c_zip=[$11],
syear=[$12], cnt=[$13], s1=[$14], s2=[$15], s3=[$16], s11=[$22], s21=[$23],
s31=[$24], syear1=[$20], cnt1=[$21]): rowcount = 5.566394015453287, cumulative
cost = {6.108042168877393E8 rows, 0.0 cpu, 0.0 io}, id = 1319
HiveFilterRel(condition=[<=($21, $13)]): rowcount = 5.566394015453287,
cumulative cost = {6.108042168877393E8 rows, 0.0 cpu, 0.0 io}, id = 1317
HiveProjectRel(product_name=[$0], item_sk=[$1], store_name=[$2],
store_zip=[$3], b_street_number=[$4], b_streen_name=[$5], b_city=[$6],
b_zip=[$7], c_street_number=[$8], c_street_name=[$9], c_city=[$10],
c_zip=[$11], syear=[$12], cnt=[$13], s1=[$14], s2=[$15], s3=[$16],
item_sk0=[$17], store_name0=[$18], store_zip0=[$19], syear0=[$20], cnt0=[$21],
s10=[$22], s20=[$23], s30=[$24]): rowcount = 16.699182046359862, cumulative
cost = {6.108042168877393E8 rows, 0.0 cpu, 0.0 io}, id = 3638
HiveJoinRel(condition=[AND(AND(=($1, $17), =($2, $18)), =($3, $19))],
joinType=[inner]): rowcount = 16.699182046359862, cumulative cost =
{6.108042168877393E8 rows, 0.0 cpu, 0.0 io}, id = 3633
HiveProjectRel(product_name=[$0], item_sk=[$1], store_name=[$2],
store_zip=[$3], b_street_number=[$4], b_streen_name=[$5], b_city=[$6],
b_zip=[$7], c_street_number=[$8], c_street_name=[$9], c_city=[$10],
c_zip=[$11], syear=[$12], cnt=[$15], s1=[$16], s2=[$17], s3=[$18]): rowcount =
79.18487224803891, cumulative cost = {6.108040585179948E8 rows, 0.0 cpu, 0.0
io}, id = 1219
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 = 79.18487224803891, cumulative cost =
{6.108040585179948E8 rows, 0.0 cpu, 0.0 io}, id = 1217
HiveProjectRel($f0=[$17], $f1=[$14], $f2=[$31], $f3=[$32],
$f4=[$43], $f5=[$44], $f6=[$45], $f7=[$46], $f8=[$48], $f9=[$49], $f10=[$50],
$f11=[$51], $f12=[$25], $f13=[$27], $f14=[$29], $f15=[$8], $f16=[$9],
$f17=[$10]): rowcount = 215.84856608045916, cumulative cost =
{6.108040585179948E8 rows, 0.0 cpu, 0.0 io}, id = 1215
HiveProjectRel(ss_item_sk=[$16], ss_customer_sk=[$17],
ss_cdemo_sk=[$18], ss_hdemo_sk=[$19], ss_addr_sk=[$20], ss_store_sk=[$21],
ss_promo_sk=[$22], ss_ticket_number=[$23], ss_wholesale_cost=[$24],
ss_list_price=[$25], ss_coupon_amt=[$26], ss_sold_date_sk=[$27],
sr_item_sk=[$28], sr_ticket_number=[$29], i_item_sk=[$30],
i_current_price=[$31], i_color=[$32], i_product_name=[$33],
c_customer_sk=[$34], c_current_cdemo_sk=[$35], c_current_hdemo_sk=[$36],
c_current_addr_sk=[$37], c_first_shipto_date_sk=[$38],
c_first_sales_date_sk=[$39], d_date_sk=[$40], d_year=[$41], d_date_sk0=[$42],
d_year0=[$43], d_date_sk1=[$44], d_year1=[$45], s_store_sk=[$46],
s_store_name=[$47], s_zip=[$48], cd_demo_sk=[$49], cd_marital_status=[$50],
cd_demo_sk0=[$51], cd_marital_status0=[$52], p_promo_sk=[$15], hd_demo_sk=[$0],
hd_income_band_sk=[$1], hd_demo_sk0=[$13], hd_income_band_sk0=[$14],
ca_address_sk=[$8], ca_street_number=[$9], ca_street_name=[$10], ca_city=[$11],
ca_zip=[$12], ca_address_sk0=[$3], ca_street_number0=[$4],
ca_street_name0=[$5], ca_city0=[$6], ca_zip0=[$7], ib_income_band_sk=[$2],
ib_income_band_sk0=[$54], cs_item_sk=[$53]): rowcount = 215.84856608045916,
cumulative cost = {6.108040585179948E8 rows, 0.0 cpu, 0.0 io}, id = 3631
HiveJoinRel(condition=[=($19, $0)], joinType=[inner]):
rowcount = 215.84856608045916, cumulative cost = {6.108040585179948E8 rows, 0.0
cpu, 0.0 io}, id = 3629
HiveJoinRel(condition=[=($1, $2)], joinType=[inner]):
rowcount = 7200.0, cumulative cost = {7220.0 rows, 0.0 cpu, 0.0 io}, id = 3464
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 = 1269
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.household_demographics]]):
rowcount = 7200.0, cumulative cost = {0}, id = 53
HiveProjectRel(ib_income_band_sk=[$0]): rowcount = 20.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1285
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.income_band]]):
rowcount = 20.0, cumulative cost = {0}, id = 63
HiveJoinRel(condition=[=($11, $51)], joinType=[inner]):
rowcount = 136.44231621312272, cumulative cost = {6.107967220756786E8 rows, 0.0
cpu, 0.0 io}, id = 3627
HiveJoinRel(condition=[=($34, $0)], joinType=[inner]):
rowcount = 136.44231621312272, cumulative cost = {6.107965656333624E8 rows, 0.0
cpu, 0.0 io}, id = 3625
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 = 1277
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.customer_address]]):
rowcount = 800000.0, cumulative cost = {0}, id = 61
HiveJoinRel(condition=[=($12, $0)], joinType=[inner]):
rowcount = 136.44231621312272, cumulative cost = {6.099964291910462E8 rows, 0.0
cpu, 0.0 io}, id = 3623
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 = 1277
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.customer_address]]):
rowcount = 800000.0, cumulative cost = {0}, id = 61
HiveJoinRel(condition=[=($23, $0)],
joinType=[inner]): rowcount = 136.44231621312272, cumulative cost =
{6.091962927487301E8 rows, 0.0 cpu, 0.0 io}, id = 3621
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 = 1269
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.household_demographics]]):
rowcount = 7200.0, cumulative cost = {0}, id = 53
HiveJoinRel(condition=[=($7, $0)],
joinType=[inner]): rowcount = 136.44231621312272, cumulative cost =
{6.091889563064139E8 rows, 0.0 cpu, 0.0 io}, id = 3619
HiveProjectRel(p_promo_sk=[$0]): rowcount =
450.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1265
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.promotion]]):
rowcount = 450.0, cumulative cost = {0}, id = 58
HiveJoinRel(condition=[=($0, $37)],
joinType=[inner]): rowcount = 136.44231621312272, cumulative cost =
{6.091883698640977E8 rows, 0.0 cpu, 0.0 io}, id = 3479
HiveFilterRel(condition=[<>($34, $36)]):
rowcount = 136.44231621312272, cumulative cost = {6.091882324217815E8 rows, 0.0
cpu, 0.0 io}, id = 1169
HiveProjectRel(ss_item_sk=[$19],
ss_customer_sk=[$20], ss_cdemo_sk=[$21], ss_hdemo_sk=[$22], ss_addr_sk=[$23],
ss_store_sk=[$24], ss_promo_sk=[$25], ss_ticket_number=[$26],
ss_wholesale_cost=[$27], ss_list_price=[$28], ss_coupon_amt=[$29],
ss_sold_date_sk=[$30], sr_item_sk=[$0], sr_ticket_number=[$1], i_item_sk=[$31],
i_current_price=[$32], i_color=[$33], i_product_name=[$34], c_customer_sk=[$4],
c_current_cdemo_sk=[$5], c_current_hdemo_sk=[$6], c_current_addr_sk=[$7],
c_first_shipto_date_sk=[$8], c_first_sales_date_sk=[$9], d_date_sk=[$35],
d_year=[$36], d_date_sk0=[$10], d_year0=[$11], d_date_sk1=[$12], d_year1=[$13],
s_store_sk=[$14], s_store_name=[$15], s_zip=[$16], cd_demo_sk=[$17],
cd_marital_status=[$18], cd_demo_sk0=[$2], cd_marital_status0=[$3]): rowcount =
955.0962134918581, cumulative cost = {6.091882324217815E8 rows, 0.0 cpu, 0.0
io}, id = 2633
HiveJoinRel(condition=[AND(=($19, $0),
=($26, $1))], joinType=[inner]): rowcount = 955.0962134918581, cumulative cost
= {6.091882324217815E8 rows, 0.0 cpu, 0.0 io}, id = 2631
HiveProjectRel(sr_item_sk=[$1],
sr_ticket_number=[$8]): rowcount = 5.5578005E7, cumulative cost = {0.0 rows,
0.0 cpu, 0.0 io}, id = 1223
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.store_returns]]):
rowcount = 5.5578005E7, cumulative cost = {0}, id = 62
HiveJoinRel(condition=[=($18, $2)],
joinType=[inner]): rowcount = 241.3079963041512, cumulative cost =
{5.536099861137853E8 rows, 0.0 cpu, 0.0 io}, id = 2629
HiveJoinRel(condition=[=($6, $10)],
joinType=[inner]): rowcount = 1600000.0, cumulative cost = {6866898.0 rows, 0.0
cpu, 0.0 io}, id = 2620
HiveJoinRel(condition=[=($7, $8)],
joinType=[inner]): rowcount = 1600000.0, cumulative cost = {5193849.0 rows, 0.0
cpu, 0.0 io}, id = 2618
HiveJoinRel(condition=[=($3, $0)],
joinType=[inner]): rowcount = 1600000.0, cumulative cost = {3520800.0 rows, 0.0
cpu, 0.0 io}, id = 2060
HiveProjectRel(cd_demo_sk=[$0],
cd_marital_status=[$2]): rowcount = 1920800.0, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1255
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.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 = 1233
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.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 = 1243
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.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 = 1243
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.date_dim]]):
rowcount = 73049.0, cumulative cost = {0}, id = 65
HiveJoinRel(condition=[=($10, $0)],
joinType=[inner]): rowcount = 102.32078757440475, cumulative cost =
{5.520098837929977E8 rows, 0.0 cpu, 0.0 io}, id = 2627
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 = 1251
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.store]]): rowcount
= 212.0, cumulative cost = {0}, id = 54
HiveJoinRel(condition=[=($4, $0)],
joinType=[inner]): rowcount = 102.32078757440475, cumulative cost =
{5.520095694722102E8 rows, 0.0 cpu, 0.0 io}, id = 2625
HiveProjectRel(cd_demo_sk=[$0],
cd_marital_status=[$2]): rowcount = 1920800.0, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1255
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.customer_demographics]]):
rowcount = 1920800.0, cumulative cost = {0}, id = 56
HiveJoinRel(condition=[=($11,
$16)], joinType=[inner]): rowcount = 102.32078757440475, cumulative cost =
{5.500886671514226E8 rows, 0.0 cpu, 0.0 io}, id = 2623
HiveJoinRel(condition=[=($0,
$12)], joinType=[inner]): rowcount = 11459.928208333333, cumulative cost =
{5.50076555E8 rows, 0.0 cpu, 0.0 io}, id = 2090
HiveProjectRel(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=[$10], ss_list_price=[$11], ss_coupon_amt=[$18],
ss_sold_date_sk=[$22]): rowcount = 5.50076554E8, cumulative cost = {0.0 rows,
0.0 cpu, 0.0 io}, id = 1221
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.store_sales]]):
rowcount = 5.50076554E8, cumulative cost = {0}, id = 55
HiveProjectRel(i_item_sk=[$0],
i_current_price=[$5], i_color=[$17], i_product_name=[$21]): rowcount = 1.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1135
HiveFilterRel(condition=[AND(in($17, 'maroon', 'burnished', 'dim', 'steel',
'navajo', 'chocolate'), between(false, $5, 35, +(35, 10)), between(false, $5,
+(35, 1), +(35, 15)))]): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu,
0.0 io}, id = 1133
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.item]]): rowcount =
48000.0, cumulative cost = {0}, id = 68
HiveProjectRel(d_date_sk=[$0],
d_year=[$6]): rowcount = 652.2232142857142, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1145
HiveFilterRel(condition=[=($6,
2000)]): rowcount = 652.2232142857142, cumulative cost = {0.0 rows, 0.0 cpu,
0.0 io}, id = 1143
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.date_dim]]):
rowcount = 73049.0, cumulative cost = {0}, id = 65
HiveProjectRel(cs_item_sk=[$0]): rowcount =
1.0, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1211
HiveFilterRel(condition=[>($1,
*(CAST(2):DOUBLE NOT NULL, $2))]): rowcount = 1.0, cumulative cost =
{3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1209
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 = 1207
HiveProjectRel($f0=[$0], $f1=[$2],
$f2=[+(+($5, $6), $7)]): rowcount = 6.692553251460564E8, cumulative cost =
{3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1205
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 = 1461
HiveJoinRel(condition=[AND(=($0, $3),
=($1, $4))], joinType=[inner]): rowcount = 6.692553251460564E8, cumulative cost
= {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1456
HiveProjectRel(cs_item_sk=[$14],
cs_order_number=[$16], cs_ext_list_price=[$24]): rowcount = 2.86549727E8,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1293
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.catalog_sales]]):
rowcount = 2.86549727E8, cumulative cost = {0}, id = 45
HiveProjectRel(cr_item_sk=[$1],
cr_order_number=[$15], cr_refunded_cash=[$22], cr_reversed_charge=[$23],
cr_store_credit=[$24]): rowcount = 2.8798881E7, cumulative cost = {0.0 rows,
0.0 cpu, 0.0 io}, id = 1295
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.catalog_returns]]):
rowcount = 2.8798881E7, cumulative cost = {0}, id = 46
HiveProjectRel(ib_income_band_sk=[$0]): rowcount = 20.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1285
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.income_band]]):
rowcount = 20.0, cumulative cost = {0}, id = 63
HiveProjectRel(item_sk=[$1], store_name=[$2], store_zip=[$3],
syear=[$12], cnt=[$15], s1=[$16], s2=[$17], s3=[$18]): rowcount =
79.18487224803891, cumulative cost = {6.108040585179948E8 rows, 0.0 cpu, 0.0
io}, id = 1313
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 = 79.18487224803891, cumulative cost =
{6.108040585179948E8 rows, 0.0 cpu, 0.0 io}, id = 1311
HiveProjectRel($f0=[$17], $f1=[$14], $f2=[$31], $f3=[$32],
$f4=[$43], $f5=[$44], $f6=[$45], $f7=[$46], $f8=[$48], $f9=[$49], $f10=[$50],
$f11=[$51], $f12=[$25], $f13=[$27], $f14=[$29], $f15=[$8], $f16=[$9],
$f17=[$10]): rowcount = 215.84856608045916, cumulative cost =
{6.108040585179948E8 rows, 0.0 cpu, 0.0 io}, id = 1309
HiveProjectRel(ss_item_sk=[$16], ss_customer_sk=[$17],
ss_cdemo_sk=[$18], ss_hdemo_sk=[$19], ss_addr_sk=[$20], ss_store_sk=[$21],
ss_promo_sk=[$22], ss_ticket_number=[$23], ss_wholesale_cost=[$24],
ss_list_price=[$25], ss_coupon_amt=[$26], ss_sold_date_sk=[$27],
sr_item_sk=[$28], sr_ticket_number=[$29], i_item_sk=[$30],
i_current_price=[$31], i_color=[$32], i_product_name=[$33],
c_customer_sk=[$34], c_current_cdemo_sk=[$35], c_current_hdemo_sk=[$36],
c_current_addr_sk=[$37], c_first_shipto_date_sk=[$38],
c_first_sales_date_sk=[$39], d_date_sk=[$40], d_year=[$41], d_date_sk0=[$42],
d_year0=[$43], d_date_sk1=[$44], d_year1=[$45], s_store_sk=[$46],
s_store_name=[$47], s_zip=[$48], cd_demo_sk=[$49], cd_marital_status=[$50],
cd_demo_sk0=[$51], cd_marital_status0=[$52], p_promo_sk=[$15], hd_demo_sk=[$0],
hd_income_band_sk=[$1], hd_demo_sk0=[$13], hd_income_band_sk0=[$14],
ca_address_sk=[$8], ca_street_number=[$9], ca_street_name=[$10], ca_city=[$11],
ca_zip=[$12], ca_address_sk0=[$3], ca_street_number0=[$4],
ca_street_name0=[$5], ca_city0=[$6], ca_zip0=[$7], ib_income_band_sk=[$2],
ib_income_band_sk0=[$54], cs_item_sk=[$53]): rowcount = 215.84856608045916,
cumulative cost = {6.108040585179948E8 rows, 0.0 cpu, 0.0 io}, id = 3132
HiveJoinRel(condition=[=($19, $0)], joinType=[inner]):
rowcount = 215.84856608045916, cumulative cost = {6.108040585179948E8 rows, 0.0
cpu, 0.0 io}, id = 3130
HiveJoinRel(condition=[=($1, $2)], joinType=[inner]):
rowcount = 7200.0, cumulative cost = {7220.0 rows, 0.0 cpu, 0.0 io}, id = 2965
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 = 1269
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.household_demographics]]):
rowcount = 7200.0, cumulative cost = {0}, id = 53
HiveProjectRel(ib_income_band_sk=[$0]): rowcount = 20.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1285
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.income_band]]):
rowcount = 20.0, cumulative cost = {0}, id = 63
HiveJoinRel(condition=[=($11, $51)], joinType=[inner]):
rowcount = 136.44231621312272, cumulative cost = {6.107967220756786E8 rows, 0.0
cpu, 0.0 io}, id = 3128
HiveJoinRel(condition=[=($34, $0)], joinType=[inner]):
rowcount = 136.44231621312272, cumulative cost = {6.107965656333624E8 rows, 0.0
cpu, 0.0 io}, id = 3126
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 = 1277
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.customer_address]]):
rowcount = 800000.0, cumulative cost = {0}, id = 61
HiveJoinRel(condition=[=($12, $0)], joinType=[inner]):
rowcount = 136.44231621312272, cumulative cost = {6.099964291910462E8 rows, 0.0
cpu, 0.0 io}, id = 3124
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 = 1277
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.customer_address]]):
rowcount = 800000.0, cumulative cost = {0}, id = 61
HiveJoinRel(condition=[=($23, $0)],
joinType=[inner]): rowcount = 136.44231621312272, cumulative cost =
{6.091962927487301E8 rows, 0.0 cpu, 0.0 io}, id = 3122
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 = 1269
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.household_demographics]]):
rowcount = 7200.0, cumulative cost = {0}, id = 53
HiveJoinRel(condition=[=($7, $0)],
joinType=[inner]): rowcount = 136.44231621312272, cumulative cost =
{6.091889563064139E8 rows, 0.0 cpu, 0.0 io}, id = 3120
HiveProjectRel(p_promo_sk=[$0]): rowcount =
450.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1265
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.promotion]]):
rowcount = 450.0, cumulative cost = {0}, id = 58
HiveJoinRel(condition=[=($0, $37)],
joinType=[inner]): rowcount = 136.44231621312272, cumulative cost =
{6.091883698640977E8 rows, 0.0 cpu, 0.0 io}, id = 2980
HiveFilterRel(condition=[<>($34, $36)]):
rowcount = 136.44231621312272, cumulative cost = {6.091882324217815E8 rows, 0.0
cpu, 0.0 io}, id = 1263
HiveProjectRel(ss_item_sk=[$19],
ss_customer_sk=[$20], ss_cdemo_sk=[$21], ss_hdemo_sk=[$22], ss_addr_sk=[$23],
ss_store_sk=[$24], ss_promo_sk=[$25], ss_ticket_number=[$26],
ss_wholesale_cost=[$27], ss_list_price=[$28], ss_coupon_amt=[$29],
ss_sold_date_sk=[$30], sr_item_sk=[$0], sr_ticket_number=[$1], i_item_sk=[$31],
i_current_price=[$32], i_color=[$33], i_product_name=[$34], c_customer_sk=[$4],
c_current_cdemo_sk=[$5], c_current_hdemo_sk=[$6], c_current_addr_sk=[$7],
c_first_shipto_date_sk=[$8], c_first_sales_date_sk=[$9], d_date_sk=[$35],
d_year=[$36], d_date_sk0=[$10], d_year0=[$11], d_date_sk1=[$12], d_year1=[$13],
s_store_sk=[$14], s_store_name=[$15], s_zip=[$16], cd_demo_sk=[$17],
cd_marital_status=[$18], cd_demo_sk0=[$2], cd_marital_status0=[$3]): rowcount =
955.0962134918581, cumulative cost = {6.091882324217815E8 rows, 0.0 cpu, 0.0
io}, id = 2047
HiveJoinRel(condition=[AND(=($19, $0),
=($26, $1))], joinType=[inner]): rowcount = 955.0962134918581, cumulative cost
= {6.091882324217815E8 rows, 0.0 cpu, 0.0 io}, id = 2045
HiveProjectRel(sr_item_sk=[$1],
sr_ticket_number=[$8]): rowcount = 5.5578005E7, cumulative cost = {0.0 rows,
0.0 cpu, 0.0 io}, id = 1223
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.store_returns]]):
rowcount = 5.5578005E7, cumulative cost = {0}, id = 62
HiveJoinRel(condition=[=($18, $2)],
joinType=[inner]): rowcount = 241.3079963041512, cumulative cost =
{5.536099861137853E8 rows, 0.0 cpu, 0.0 io}, id = 2043
HiveJoinRel(condition=[=($6, $10)],
joinType=[inner]): rowcount = 1600000.0, cumulative cost = {6866898.0 rows, 0.0
cpu, 0.0 io}, id = 2034
HiveJoinRel(condition=[=($7, $8)],
joinType=[inner]): rowcount = 1600000.0, cumulative cost = {5193849.0 rows, 0.0
cpu, 0.0 io}, id = 2032
HiveJoinRel(condition=[=($3, $0)],
joinType=[inner]): rowcount = 1600000.0, cumulative cost = {3520800.0 rows, 0.0
cpu, 0.0 io}, id = 1474
HiveProjectRel(cd_demo_sk=[$0],
cd_marital_status=[$2]): rowcount = 1920800.0, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1255
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.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 = 1233
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.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 = 1243
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.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 = 1243
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.date_dim]]):
rowcount = 73049.0, cumulative cost = {0}, id = 65
HiveJoinRel(condition=[=($10, $0)],
joinType=[inner]): rowcount = 102.32078757440475, cumulative cost =
{5.520098837929977E8 rows, 0.0 cpu, 0.0 io}, id = 2041
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 = 1251
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.store]]): rowcount
= 212.0, cumulative cost = {0}, id = 54
HiveJoinRel(condition=[=($4, $0)],
joinType=[inner]): rowcount = 102.32078757440475, cumulative cost =
{5.520095694722102E8 rows, 0.0 cpu, 0.0 io}, id = 2039
HiveProjectRel(cd_demo_sk=[$0],
cd_marital_status=[$2]): rowcount = 1920800.0, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1255
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.customer_demographics]]):
rowcount = 1920800.0, cumulative cost = {0}, id = 56
HiveJoinRel(condition=[=($11,
$16)], joinType=[inner]): rowcount = 102.32078757440475, cumulative cost =
{5.500886671514226E8 rows, 0.0 cpu, 0.0 io}, id = 2037
HiveJoinRel(condition=[=($0,
$12)], joinType=[inner]): rowcount = 11459.928208333333, cumulative cost =
{5.50076555E8 rows, 0.0 cpu, 0.0 io}, id = 1504
HiveProjectRel(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=[$10], ss_list_price=[$11], ss_coupon_amt=[$18],
ss_sold_date_sk=[$22]): rowcount = 5.50076554E8, cumulative cost = {0.0 rows,
0.0 cpu, 0.0 io}, id = 1221
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.store_sales]]):
rowcount = 5.50076554E8, cumulative cost = {0}, id = 55
HiveProjectRel(i_item_sk=[$0],
i_current_price=[$5], i_color=[$17], i_product_name=[$21]): rowcount = 1.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1229
HiveFilterRel(condition=[AND(in($17, 'maroon', 'burnished', 'dim', 'steel',
'navajo', 'chocolate'), between(false, $5, 35, +(35, 10)), between(false, $5,
+(35, 1), +(35, 15)))]): rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu,
0.0 io}, id = 1227
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.item]]): rowcount =
48000.0, cumulative cost = {0}, id = 68
HiveProjectRel(d_date_sk=[$0],
d_year=[$6]): rowcount = 652.2232142857142, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1239
HiveFilterRel(condition=[=($6,
+(2000, 1))]): rowcount = 652.2232142857142, cumulative cost = {0.0 rows, 0.0
cpu, 0.0 io}, id = 1237
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.date_dim]]):
rowcount = 73049.0, cumulative cost = {0}, id = 65
HiveProjectRel(cs_item_sk=[$0]): rowcount =
1.0, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1305
HiveFilterRel(condition=[>($1,
*(CAST(2):DOUBLE NOT NULL, $2))]): rowcount = 1.0, cumulative cost =
{3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1303
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 = 1301
HiveProjectRel($f0=[$0], $f1=[$2],
$f2=[+(+($5, $6), $7)]): rowcount = 6.692553251460564E8, cumulative cost =
{3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1299
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 = 1454
HiveJoinRel(condition=[AND(=($0, $3),
=($1, $4))], joinType=[inner]): rowcount = 6.692553251460564E8, cumulative cost
= {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1449
HiveProjectRel(cs_item_sk=[$14],
cs_order_number=[$16], cs_ext_list_price=[$24]): rowcount = 2.86549727E8,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1293
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.catalog_sales]]):
rowcount = 2.86549727E8, cumulative cost = {0}, id = 45
HiveProjectRel(cr_item_sk=[$1],
cr_order_number=[$15], cr_refunded_cash=[$22], cr_reversed_charge=[$23],
cr_store_credit=[$24]): rowcount = 2.8798881E7, cumulative cost = {0.0 rows,
0.0 cpu, 0.0 io}, id = 1295
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.catalog_returns]]):
rowcount = 2.8798881E7, cumulative cost = {0}, id = 46
HiveProjectRel(ib_income_band_sk=[$0]): rowcount = 20.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1285
HiveTableScanRel(table=[[tpcds_bin_partitioned_orc_200_orig.income_band]]):
rowcount = 20.0, cumulative cost = {0}, id = 63
{code}
> 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
>
> Attachments: HIVE-8280.1.patch, HIVE-8280.2.patch, HIVE-8280.3.patch
>
>
> When a filter is applied on PK side joins don't qualify as PK/FK join.
> In getUniqueKeys when a filter is applied on the table the child is no
> longer a table scan.
> {code}
> public Set<BitSet> getUniqueKeys(ProjectRelBase rel, boolean ignoreNulls) {
> RelNode child = rel.getChild();
> if (!(child instanceof HiveTableScanRel)) {
> Function<RelNode, Metadata> fn = RelMdUniqueKeys.SOURCE.apply(
> rel.getClass(), BuiltInMetadata.UniqueKeys.class);
> return ((BuiltInMetadata.UniqueKeys) fn.apply(rel))
> .getUniqueKeys(ignoreNulls);
> }
> {code}
> Repro
> {code}
> with ss as
> (select
> ss_customer_sk, ss_item_sk, ss_ticket_number
> from
> store_sales,
> store
> where
> s_store_sk = ss_store_sk
> and s_market_id = 4),
> sr as
> (select sr_customer_sk,sr_item_sk ,sr_ticket_number from store_returns, store
> where s_store_sk = sr_store_sk and s_market_id=4)
> select
> count(*)
> from
> ss,
> sr
> where
> ss_customer_sk = sr_customer_sk
> and ss_item_sk = sr_item_sk
> and ss_ticket_number = sr_ticket_number;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
