Konstantin Bereznyakov created HIVE-30037:
---------------------------------------------
Summary: 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
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)
