[
https://issues.apache.org/jira/browse/HIVE-29580?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Bereznyakov updated HIVE-29580:
------------------------------------------
Hadoop Flags: Incompatible change
Release Note: Under CBO, a by-name reference to a column whose alias is
duplicated at a subquery/CTE boundary now fails at compile time with
SemanticException Error 10007 (Ambiguous column reference) instead of silently
binding to one of the candidates. Affected queries should give the duplicate
columns explicit, unique aliases.
> CBO: Ambiguous column reference not detected when duplicate column aliases
> escape a subquery/CTE boundary
> ---------------------------------------------------------------------------------------------------------
>
> Key: HIVE-29580
> URL: https://issues.apache.org/jira/browse/HIVE-29580
> Project: Hive
> Issue Type: Bug
> Reporter: Konstantin Bereznyakov
> Assignee: Konstantin Bereznyakov
> Priority: Major
> Labels: pull-request-available
>
> The query
> {code:sql}
> with bse as (
> select 'a' as delivery_date, concat('a') as delivery_date
> ),
> tpm as (
> select * from bse
> )
> select tpm.delivery_date
> from tpm;
> {code}
> compiles and runs with CBO *on* and silently returns one of the two candidate
> columns. With CBO *off* the same query fails with
> {noformat}
> FAILED: SemanticException [Error 10007]: Ambiguous column reference
> delivery_date in bse
> {noformat}
> This is one instance of a general hole: when duplicate column aliases escape
> a subquery/CTE boundary, a later by-name reference through the boundary alias
> silently binds to the first candidate instead of being rejected as ambiguous.
> The reference can also sit in WHERE, a join condition, HAVING, ORDER BY, a
> windowing clause or a JOIN ... USING shape, and the two candidates need not
> be equal in value. In the worst case the arbitrary pick is persisted:
> {code:sql}
> create table ctas_x1 as
> with bse as (select 'FIRST' as c, 'SECOND' as c),
> tpm as (select * from bse)
> select tpm.c from tpm;
> {code}
> succeeds on master and stores a one-column table containing FIRST; SECOND is
> discarded without any warning, so every downstream reader treats the
> arbitrary choice as fact.
> *Resolution:* under CBO, the surviving column is marked when its duplicate
> alias collides at the boundary, and any later by-name reference fails with
> the existing Error 10007 (AMBIGUOUS_COLUMN). Unreferenced duplicates stay
> tolerated, so star expansion and positional use keep working, preserving the
> behavior introduced by HIVE-19770 (see also HIVE-20215). Non-CBO planning is
> unchanged. Design notes and the full test matrix are in the PR.
> *Incompatible change:* a query that references a duplicate-named column by
> name through a subquery/CTE boundary no longer compiles under CBO. Two
> existing tests contained such references and were updated with explicit
> column aliases ({{cross_prod_3.q}}, {{limit_join_transpose.q}}). PostgreSQL
> rejects all of these shapes the same way.
> Note the error message reports different aliases per engine for the same
> query: CBO names the reference site (delivery_date in tpm), non-CBO the
> definition site (delivery_date in bse). Both are Error 10007.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
