soumyakanti3578 commented on code in PR #6673:
URL: https://github.com/apache/hive/pull/6673#discussion_r3917720093
##########
ql/src/test/results/clientpositive/jdbc/postgres/cbo_query14.q.out:
##########
@@ -0,0 +1,332 @@
+Warning: Shuffle Join MERGEJOIN[334][tables = [$hdt$_1, $hdt$_2]] in Stage
'Reducer 28' is a cross product
+Warning: Shuffle Join MERGEJOIN[340][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in
Stage 'Reducer 9' is a cross product
+Warning: Shuffle Join MERGEJOIN[346][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in
Stage 'Reducer 17' is a cross product
+Warning: Shuffle Join MERGEJOIN[352][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in
Stage 'Reducer 20' is a cross product
+CBO PLAN:
+HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC],
dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
+ HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3], _c4=[$4], _c5=[$5])
+ HiveAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0,
1}, {0}, {}]], agg#0=[sum($4)], agg#1=[sum($5)])
+ HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3], sales=[$4], number_sales=[$5])
+ HiveUnion(all=[true])
+ HiveProject(channel=[_UTF-16LE'store':VARCHAR(2147483647) CHARACTER
SET "UTF-16LE"], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2],
sales=[$3], number_sales=[$4])
+ HiveJoin(condition=[>($3, $6)], joinType=[inner],
algorithm=[none], cost=[not available])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4])
+ HiveFilter(condition=[IS NOT NULL($3)])
+ HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)],
agg#1=[count()])
+ HiveProject($f0=[$5], $f1=[$6], $f2=[$7],
$f3=[*(CAST($2):DECIMAL(10, 0), $3)])
+ HiveSemiJoin(condition=[=($1, $11)], joinType=[semi])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1],
ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$4], i_brand_id=[$5],
i_class_id=[$6], i_category_id=[$7], d_date_sk=[$8], d_year=[$9], d_moy=[$10])
+ HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1],
ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$7], i_brand_id=[$8],
i_class_id=[$9], i_category_id=[$10], d_date_sk=[$4], d_year=[$5], d_moy=[$6])
+ JdbcJoin(condition=[=($1, $7)], joinType=[inner])
+ JdbcJoin(condition=[=($0, $4)],
joinType=[inner])
+ JdbcProject(ss_sold_date_sk=[$0],
ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3])
+ JdbcFilter(condition=[AND(IS NOT NULL($1),
IS NOT NULL($0))])
+ JdbcProject(ss_sold_date_sk=[$0],
ss_item_sk=[$2], ss_quantity=[$10], ss_list_price=[$12])
+ JdbcHiveTableScan(table=[[default,
store_sales]], table:alias=[store_sales])
+ JdbcProject(d_date_sk=[$0], d_year=[$1],
d_moy=[$2])
+ JdbcFilter(condition=[AND(=($1, 2000),
=($2, 11), IS NOT NULL($0))])
+ JdbcProject(d_date_sk=[$0], d_year=[$6],
d_moy=[$8])
+ JdbcHiveTableScan(table=[[default,
date_dim]], table:alias=[date_dim])
+ JdbcProject(i_item_sk=[$0], i_brand_id=[$1],
i_class_id=[$2], i_category_id=[$3])
+ JdbcFilter(condition=[IS NOT NULL($0)])
+ JdbcProject(i_item_sk=[$0],
i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ JdbcHiveTableScan(table=[[default,
item]], table:alias=[item])
+ HiveProject(i_item_sk=[$0])
+ HiveJoin(condition=[AND(=($1, $4), =($2, $5), =($3,
$6))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$1],
i_class_id=[$2], i_category_id=[$3])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$1],
i_class_id=[$2], i_category_id=[$3])
+ HiveProject(i_item_sk=[$0], i_brand_id=[$1],
i_class_id=[$2], i_category_id=[$3])
+ HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcFilter(condition=[AND(IS NOT NULL($1),
IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($0))])
+ JdbcProject(i_item_sk=[$0],
i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11])
+ JdbcHiveTableScan(table=[[default,
item]], table:alias=[item])
+ HiveProject($f0=[$0], $f1=[$1], $f2=[$2])
+ HiveFilter(condition=[=($3, 3)])
+ HiveAggregate(group=[{0, 1, 2}],
agg#0=[count($3)])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveUnion(all=[true])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+
HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcAggregate(group=[{4, 5,
6}], agg#0=[count()])
+ JdbcJoin(condition=[=($1,
$3)], joinType=[inner])
+ JdbcJoin(condition=[=($0,
$2)], joinType=[inner])
+
JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1])
+
JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
+
JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2])
+
JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+
JdbcProject(d_date_sk=[$0])
+
JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))])
+
JdbcProject(d_date_sk=[$0], d_year=[$6])
+
JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+
JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3])
+
JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS
NOT NULL($3))])
+
JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9],
i_category_id=[$11])
+
JdbcHiveTableScan(table=[[default, item]], table:alias=[iss])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+
HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcAggregate(group=[{4, 5,
6}], agg#0=[count()])
+ JdbcJoin(condition=[=($1,
$3)], joinType=[inner])
+ JdbcJoin(condition=[=($0,
$2)], joinType=[inner])
+
JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$1])
+
JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
+
JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$15])
+
JdbcHiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+
JdbcProject(d_date_sk=[$0])
+
JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))])
+
JdbcProject(d_date_sk=[$0], d_year=[$6])
+
JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+
JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3])
+
JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS
NOT NULL($3))])
+
JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9],
i_category_id=[$11])
+
JdbcHiveTableScan(table=[[default, item]], table:alias=[ics])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+ HiveProject(i_brand_id=[$0],
i_class_id=[$1], i_category_id=[$2], $f3=[$3])
+
HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcAggregate(group=[{4, 5,
6}], agg#0=[count()])
+ JdbcJoin(condition=[=($1,
$3)], joinType=[inner])
+ JdbcJoin(condition=[=($0,
$2)], joinType=[inner])
+
JdbcProject(ws_sold_date_sk=[$0], ws_item_sk=[$1])
+
JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
+
JdbcProject(ws_sold_date_sk=[$0], ws_item_sk=[$3])
+
JdbcHiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+
JdbcProject(d_date_sk=[$0])
+
JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))])
+
JdbcProject(d_date_sk=[$0], d_year=[$6])
+
JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+
JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3])
+
JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS
NOT NULL($3))])
+
JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9],
i_category_id=[$11])
+
JdbcHiveTableScan(table=[[default, item]], table:alias=[iws])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none],
cost=[not available])
+ HiveProject(cnt=[$0])
+ HiveFilter(condition=[sq_count_check($0)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveTableScan(table=[[default, avg_sales]],
table:alias=[avg_sales])
+ HiveProject(average_sales=[$0])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, avg_sales]],
table:alias=[avg_sales])
Review Comment:
Added `conf.put(HiveConf.ConfVars.HIVE_CTE_MATERIALIZE_THRESHOLD, "-1");` in
`MiniLlapLocalPostgresJdbcCliConfig`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
