Working BQ query shows ambiguous column name error while creating a View

Question

I'm running the below (simplified) query on Big Query.

with

t0 as (select a, b, c from T), t1 as (select a, b from t0 where c = 'x'), t2 as (select a, b from t0 where c = 'y')

select * from t1 full outer join t2 on t1.a = t2.a

As a query it runs perfectly OK but when I use it to generate a view, I get the error: CREATE VIEW has columns with duplicate name 'a' at .

When running the query alone, in the final table, I'm getting column names automatically renamed as a, a_1, b, b_1 etc. But somehow creating a view results in above error.

I'm creating view as: 'create or replace view <view_id> as (query);' The query is exactly the same.

Puzzled as to why a perfectly running query would give ambiguity in column names while creating a view for it. Will appreciate any help!

Answer 1

BigQuery does not automatically rename columns in views to avoid ambiguity.

To resolve this issue, you should provide unique column aliases for the columns in your SELECT statement to avoid naming conflicts

Try the below query

WITH
  t0 AS (SELECT a, b, c FROM T),
  t1 AS (SELECT a AS a_t1, b FROM t0 WHERE c = 'x'),
  t2 AS (SELECT a AS a_t2, b FROM t0 WHERE c = 'y')

SELECT * FROM t1
FULL OUTER JOIN t2 ON t1.a_t1 = t2.a_t2;