[jira] [Updated] (HIVE-30037) Ordinal ORDER BY silently dropped when CBO declines the statement

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ 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)