[
https://issues.apache.org/jira/browse/HIVE-29374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18046272#comment-18046272
]
Stamatis Zampetakis commented on HIVE-29374:
--------------------------------------------
The issue with HiveTableScan operators with pruned schema can be circumvented
when producing suggestions by "normalizing" the scans before hitting the
suggester. A rough POC outlining the general idea can be found in:
[https://github.com/zabetak/hive/tree/HIVE-29374-quick-poc]
Essentially, column pruning is removed from scans just for the sake of creating
suggestions. Still, the generated suggestions especially for Q9 are pretty
basic so probably the patch is not worth merging as it is.
In order to improve suggestions for Q9, I explored also another direction of
creating a specialized suggester for the patterns that appear in Q9. A POC
implementation illustrating the idea and the impact on current TPC-DS queries
can be found in: [https://github.com/zabetak/hive/tree/cte-suggester-for-q9]
> 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)
