[
https://issues.apache.org/jira/browse/HIVE-29580?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Bereznyakov updated HIVE-29580:
------------------------------------------
Summary: CBO: Ambiguous column reference not detected when duplicate column
aliases escape a subquery/CTE boundary (was: CBO: Ambiguous column reference
not detected for duplicate alias inside a CTE)
> 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)
