[
https://issues.apache.org/jira/browse/HIVE-29374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18045563#comment-18045563
]
Stamatis Zampetakis commented on HIVE-29374:
--------------------------------------------
After debugging the plan for TPC-DS query9 that is given as an input to
{{CommonTableExpressionIdentitySuggester}} (via {{TestTPCDSCteCliDriver}}) and
in particular the following snippet(s):
{noformat}
HiveFilter(condition=[BETWEEN(false, $9, 1, 20)])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
{noformat}
it turns out that the scan operators of store_sales table that are below the
filter operator are not identical. Although they appear identical in the output
of {{EXPLAIN CBO}} the debugger shows that there is some projection built-in
into the {{HiveTableScan}} operator (see neededColIndxsFrmReloptHT and
hiveTableScanRowType attributes) that makes each occurrence unique.
{noformat}
input = {HiveTableScan@24614}
hiveTableScanRowType = {RelRecordType@24620} "RecordType(INTEGER ss_quantity)"
neededColIndxsFrmReloptHT = {RegularImmutableList@24621} size = 1
tblAlias = "store_sales"
input = {HiveTableScan@24648}
hiveTableScanRowType = {RelRecordType@24653} "RecordType(INTEGER ss_quantity,
DECIMAL(7, 2) ss_ext_list_price)"
neededColIndxsFrmReloptHT = {RegularImmutableList@24654} size = 2
tblAlias = "store_sales"
input = {HiveTableScan@24675}
hiveTableScanRowType = {RelRecordType@24680} "RecordType(INTEGER ss_quantity,
DECIMAL(7, 2) ss_net_paid_inc_tax)"
neededColIndxsFrmReloptHT = {RegularImmutableList@24681} size = 2
tblAlias = "store_sales"
{noformat}
The {{neededColIndxsFrmReloptHT}} attribute contributes to the digest of the
{{HiveTableScan}} so essentially they are considered as different operators by
the {{HepPlanner}} and essentially the logic of the {{CommonRelSubExprRule}}
does not kick in.
> No CTE suggestion for TPC-DS query9
> -----------------------------------
>
> Key: HIVE-29374
> URL: https://issues.apache.org/jira/browse/HIVE-29374
> Project: Hive
> Issue Type: Bug
> Components: CBO
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> The {{hive.optimize.cte.suggester.class}} property set to
> {{CommonTableExpressionIdentitySuggester}} triggers the common table
> expression (CTE) detection and rewrite logic.
> The CTE tests indicate that for TPC-DS
> [query9|https://github.com/apache/hive/blob/1fd3fb9f1e5fa894c3888699caaec67d4c42d661/ql/src/test/results/clientpositive/perf/tpcds30tb/cte/cbo_query9.q.out]
> the {{CommonTableExpressionIdentitySuggester}} does not generate any
> suggestion (no "CTE Suggestion" entries in the output). This is unexpected
> since the CBO plan does contain some common table expressions:
> {noformat}
> HiveFilter(condition=[BETWEEN(false, $9, 1, 20)])
> HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
> {noformat}
> The sub-plan above appears at least 3 times in the query plan so it is a
> valid CTE candidate that should appear as a suggestion.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
