[
https://issues.apache.org/jira/browse/DRILL-8555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18125107#comment-18125107
]
ASF GitHub Bot commented on DRILL-8555:
---------------------------------------
letian-jiang commented on PR #3086:
URL: https://github.com/apache/drill/pull/3086#issuecomment-6063267891
I've updated the parameterizer to preserve HAVING and QUALIFY expressions
whenever the SELECT expressions are preserved for grouping, ordering, or window
definitions. This keeps matching expressions consistent across clauses.
Eligible WHERE literals can still be rebound.
I don't think replanning the original SQL after a planning failure is a
reliable general fallback. Exception types alone cannot distinguish failures
introduced by dynamic parameters from errors in the original query. Also,
parameterization can produce incorrect results without throwing an exception.
For example, before this fix:
```sql
SELECT x + 1 AS k,
ROW_NUMBER() OVER (ORDER BY x + 1) AS rn
FROM (VALUES (1, 1), (1, 2), (2, 3)) AS t(x, v)
WHERE v > 0
QUALIFY x + 1 = 2
AND ROW_NUMBER() OVER (ORDER BY x + 1) = 1
ORDER BY x + 1;
```
With plan caching disabled, this returned one row: (k = 2, rn = 1). With
caching enabled, it returned zero rows without any exception, both on initial
planning and on a confirmed cache hit. An exception-triggered fallback would
not catch this regression.
> Physical plan cache for parameterized SQL queries
> -------------------------------------------------
>
> Key: DRILL-8555
> URL: https://issues.apache.org/jira/browse/DRILL-8555
> Project: Apache Drill
> Issue Type: New Feature
> Components: Query Planning & Optimization
> Reporter: Letian Jiang
> Priority: Major
>
> h2. Motivation
> Repeated queries that differ only in literals are a common workload, yet each
> query currently requires full planning. This adds unnecessary latency and
> wastes CPU time.
> h2. Brief Design
> Add a per-Drillbit, in-memory plan cache. Drill automatically parameterizes
> eligible SQL queries and reuses a cached physical plan with the current
> literal values, without changes to client queries.
> h2. Plan reuse safety
> * *Engine:* matches the SQL template and checks relevant option changes,
> plugin configuration changes, and whether base table versions still match.
> * *Storage plugin:* explicitly opts in, provides a plan-compatible table
> version, and is responsible for rebuilding its predicate-dependent scan work
> from the rebound expressions.
> h2. Expected benefit
> Short, frequently executed queries such as HBase reads can benefit
> substantially. Analytical queries with significant planning cost can benefit
> as well.
> h2. Implementation status
> A working implementation is available, including HBase, Paimon and Iceberg
> integrations. A pull request will follow.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
