[
https://issues.apache.org/jira/browse/HIVE-24028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17472416#comment-17472416
]
Aman Sinha commented on HIVE-24028:
-----------------------------------
This is expected behavior because both CREATE MATERIALIZED VIEW AS and CREATE
TABLE AS require that the SELECT list contain unique column names because they
determine the schema of the target table/MV. For instance, the following CTAS
also gives (expected) error:
{noformat}
0: jdbc:hive2://localhost:11050/default> create table tt1 as select
n1.n_nationkey, n2.n_nationkey from tpch.nation n1 inner join tpch.nation n2 on
n1.n_regionkey = n2.n_regionkey;
Error: Error while compiling statement: FAILED: SemanticException [Error
10036]: Duplicate column name: n_nationkey (state=42000,code=10036)
{noformat}
In the TPC-DS query in the description, the SELECT list contains duplicate
columns names (from the 2 joined tables) without aliases, hence the error.
{noformat}
select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
{noformat}
Adding aliases will allow the CMV to succeed. Note that running just the
SELECT part works without aliases since the results are just returned to the
client without creating table metadata.
> MV query fails with CalciteViewSemanticException
> ------------------------------------------------
>
> Key: HIVE-24028
> URL: https://issues.apache.org/jira/browse/HIVE-24028
> Project: Hive
> Issue Type: Sub-task
> Components: Materialized views
> Reporter: Rajesh Balamohan
> Priority: Major
>
> {noformat}
> explain create materialized view qmv39 as
> with inv as
> (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
> ,stdev,mean, case mean when 0 then null else stdev/mean end cov
> from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
> ,stddev_samp(inv_quantity_on_hand)
> stdev,avg(inv_quantity_on_hand) mean
> from inventory
> ,item
> ,warehouse
> ,date_dim
> where inv_item_sk = i_item_sk
> and inv_warehouse_sk = w_warehouse_sk
> and inv_date_sk = d_date_sk
> and d_year =2000
> group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
> where case mean when 0 then 0 else stdev/mean end > 1)
> select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
> ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
> from inv inv1,inv inv2
> where inv1.i_item_sk = inv2.i_item_sk
> and inv1.w_warehouse_sk = inv2.w_warehouse_sk
> and inv1.d_moy=2
> and inv2.d_moy=2+1
> {noformat}
> {noformat}
> Error: Error while compiling statement: FAILED: SemanticException
> org.apache.hadoop.hive.ql.optimizer.calcite.CalciteViewSemanticException:
> Duplicate column name: w_warehouse_sk (state=42000,code=40000)
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
