[
https://issues.apache.org/jira/browse/HIVE-30037?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated HIVE-30037:
----------------------------------
Labels: pull-request-available (was: )
> Ordinal ORDER BY silently dropped when CBO declines the statement
> -----------------------------------------------------------------
>
> Key: HIVE-30037
> URL: https://issues.apache.org/jira/browse/HIVE-30037
> Project: Hive
> Issue Type: Bug
> Components: CBO, Query Planning
> Affects Versions: 4.0.0
> Reporter: Konstantin Bereznyakov
> Priority: Major
> Labels: pull-request-available
> Attachments: orderby_pos_cbo_on_never3.q.out,
> orderby_pos_silent_drop.master.q.out, orderby_pos_silent_drop.q,
> orderby_pos_silent_drop3.master.q.out, orderby_pos_silent_drop3.q
>
>
> h3. Summary
> With default settings ({{hive.cbo.enable=true}},
> {{hive.orderby.position.alias=true}}), a statement that CBO declines to plan
> is compiled by the legacy planner with its ORDER BY ordinal never
> substituted. The ordinal is compiled as a constant sort key, the Reduce Sink
> ends up with no sort keys, and the query returns rows in an order other than
> the one requested, with no error or warning. Reproduced on master 9019223a86
> and on a 4.0.0 build (2026-09-10).
> h3. Reproduction
> {code:sql}
> create table ob_t (d int);
> insert into ob_t values (1), (2), (3);
> set hive.fetch.task.conversion=none;
> /* CBO declines TABLESAMPLE (TOK_TABLESPLITSAMPLE) */
> select d from ob_t tablesample (5 rows) s order by 1 desc;
> /* cbo=true: 1, 2, 3 cbo=false: 3, 2, 1 */
> /* CBO declines SORT BY with LIMIT in a subquery */
> select d from (select d from ob_t sort by d limit 5) s order by 1 desc;
> /* cbo=true: 1, 2, 3 cbo=false: 3, 2, 1 */
> {code}
> {{hive.cbo.fallback.strategy}} plays no part: no exception is thrown, CBO is
> simply not invoked. EXPLAIN with {{cbo=true}} shows the Reduce Output
> Operator with an empty {{sort order:}} and no key expressions (attached
> {{orderby_pos_cbo_on_never3.q.out}}).
> h3. Mechanism (line numbers as of master 9019223a86)
> * {{SemanticAnalyzer.analyzeInternal}} calls {{processPositionAlias(ast)}}
> (line 13251) before {{genOPTree}} (13302). Inside {{processPositionAlias}},
> the ORDER BY substitution is guarded by {{!HiveConf.getBoolVar(conf,
> HIVE_CBO_ENABLED)}} (14297), with the comment "if cbo is enabled, orderby
> position will be processed in genPlan". With CBO enabled the ordinal stays a
> Number literal in the AST.
> * {{CalcitePlanner.genOPTree}} evaluates {{canCBOHandleAst}} (559). When CBO
> runs, the ordinal is resolved in {{CalcitePlannerAction.genSortByKey}}
> through {{getFieldIndexFromColumnNumber}} (5314 onward). When CBO declines,
> the same unsubstituted AST is handed to {{super.genOPTree}} (709) with
> {{cboInfo}} set to "Plan not optimized by CBO because the statement ...", and
> nothing on that path resolves the ordinal.
> * Decliners: {{HiveCalciteUtil.unsupportedFeaturesPresentInASTorQB}}
> (TOK_CHARSETLITERAL, TOK_TABLESPLITSAMPLE, TOK_UNIQUEJOIN,
> TOK_TABLEBUCKETSAMPLE, recursive table sample) and {{canHandleQbForCbo}}
> ("has sort by with limit", "has PTF", "uses scripts", "has lateral views"),
> plus statements without a source table.
> * The legacy planner then compiles {{order by 1}} as a constant key; the
> constant is eliminated from the Reduce Sink, which is left with no sort keys.
> The exception-driven fallback ({{recompile_without_cbo}}) is not affected:
> the recompile runs with {{hive.cbo.enable=false}}, so
> {{processPositionAlias}} substitutes normally (verified with a CBO-crashing
> predicate plus an ordinal ORDER BY under CONSERVATIVE: sorted output). The
> decline path is the gap.
> h3. Suggested fix
> Make the substitution follow the planner that actually runs rather than the
> config flag: in {{CalcitePlanner.genOPTree}}, when {{canCBOHandleAst}}
> declines, substitute ORDER BY ordinals before calling {{super.genOPTree}}.
> Failing the statement instead of silently dropping the sort would also be
> acceptable. HIVE-28725 (4.1.0) fixed the mirror case on the CBO path, where
> sorting was performed although position alias was disabled.
> h3. Attachments
> orderby_pos_silent_drop.q, orderby_pos_silent_drop3.q and their master
> outputs; orderby_pos_cbo_on_never3.q.out (EXPLAIN showing the empty sort
> order).
> h3. Related
> HIVE-28725 (mirror, 4.1.0); HIVE-15938, HIVE-18189, HIVE-15160
> (position-alias lineage); HIVE-27830 (deprecation of hive.cbo.enable; the
> decline path exists independently of that flag).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
