[jira] [Resolved] (IMPALA-5239) Unable to create view when referencing field with same name at parent level and in array of structs

["Alexander Behm (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IMPALA-5239?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Alexander Behm resolved IMPALA-5239.
------------------------------------
    Resolution: Not A Bug

This is not a bug because in this query:
{code}
select
columnname,
structured_column.item.columnname as structured_columnname
from
test_ambiguous,
test_ambiguous.structured_column
{code}

The column reference "columnname" in the second line is correctly reported as ambiguous.
Explanation: There are two table aliases which both expose the same column, so unqualified column references are ambiguous.

A similar example without nested types would be:
select c from mytable a inner join mytable b

Both the "a" and "b" aliases have the column "c", so the unqualified column reference is ambiguous.

To fix your issue, qualify the column reference:
{code}
select
test_ambiguous.columnname,
structured_column.item.columnname as structured_columnname
from
test_ambiguous,
test_ambiguous.structured_column
{code}

or even more clearly with explicit table aliases (recommended with nested types)
{code}
select
a.columnname,
b.columnname as structured_columnname
from
test_ambiguous a,
a.structured_column b
{code}

> Unable to create view when referencing field with same name at parent level and in array of structs
> ---------------------------------------------------------------------------------------------------
>
>                 Key: IMPALA-5239
>                 URL: https://issues.apache.org/jira/browse/IMPALA-5239
>             Project: IMPALA
>          Issue Type: Bug
>          Components: Frontend
>    Affects Versions: Impala 2.6.0
>            Reporter: Nathan Salmon
>              Labels: ramp-up
>         Attachments: test_ambiguous.sql
>
>
> If a column name at the top level is the same as the name of a member of a struct in an array, a "Column/field reference is ambiguous" error is encountered when trying to create a view with both in scope.
> DDL attached.  See output below:
> Server version: impalad version 2.6.0-cdh5.8.2 RELEASE (build f25aa5b2bcdabf1eb4233747a7b04a067059ee3b)
> Query: use default
> Query: drop table if exists test_ambiguous
> Query: create external table test_ambiguous(
>     columnname string,
>     structured_column array<struct<
>         columnname: string>>,
>     anothercolumn string
> )
> Fetched 0 row(s) in 0.12s
> Query: drop view if exists test_ambiguous_view
> Query: create view test_ambiguous_view as select
>     columnname,
>     structured_column.item.columnname as structured_columnname
> from
>     test_ambiguous,
>     test_ambiguous.structured_column
> ERROR: AnalysisException: Column/field reference is ambiguous: 'columnname'
> Could not execute command: create view test_ambiguous_view as select
>     columnname,
>     structured_column.item.columnname as structured_columnname
> from
>     test_ambiguous,
>     test_ambiguous.structured_column



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)