[
https://issues.apache.org/jira/browse/HIVE-30037?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18114485#comment-18114485
]
Konstantin Bereznyakov commented on HIVE-30037:
-----------------------------------------------
A note for whoever adds coverage for this, measured on master 9019223a86 with
stock configuration (one EXPLAIN per shape, verdict taken from the engine log
line "Not invoking CBO because the statement ..."):
Of the decliners listed above, six reach this bug: TABLESAMPLE, bucket sample,
charset literal, UNIQUEJOIN, sort by with limit, and TRANSFORM. Three do not:
* an ordinary window function does not set the PTF feature, which is set at
TOK_PTBLFUNCTION in the FROM clause;
* * lateral views need hive.cbo.returnpath.hiveop on, and CalcitePlanner
overrides isCBOSupportedLateralView to the negation of that flag, which is off
by default;
* * the no-source-table branch did not fire for either shape I tried.
A test built on a lateral view or a window function would therefore pass for
the wrong reason: CBO plans the statement, the ordinal resolves normally, and
nothing is exercised. Note also that sort by with limit has to sit in a
subquery, since ErrorMsg.ORDERBY_SORTBY_CONFLICT forbids ORDER BY and SORT BY
in one query block.
The cheapest trigger is a table sample, and it needs no set statements at all:
{code:sql}
create table m (d int);
insert into m values (1), (2);
select d from m tablesample (2 rows) order by 1 desc; /* returns 1, 2 */
select d from m order by 1 desc; /* returns 2, 1 */
{code}
----
_Drafted with Claude Code; verified and posted by Konstantin Bereznyakov._
> 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
> 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)
