[
https://issues.apache.org/jira/browse/HIVE-29580?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Bereznyakov updated HIVE-29580:
------------------------------------------
Description:
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.
was:
The query
{code:java}
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}
"happily" works with CBO {*}on{*}, fails with "FAILED: SemanticException [Error
10007]: Ambiguous column reference delivery_date in bse" when CBO is *off*
> CBO: Ambiguous column reference not detected for duplicate alias inside a CTE
> -----------------------------------------------------------------------------
>
> 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)
