Solved: Issue create view when the source table and column... - Page 2

Vic-tator · 08-22-2023 05:15 AM

Hey,
I have an Issue when trying to create view when the source table and column have the same name.
The view is created with wrong datatypes struct.
Reproduce:
Create table:

CREATE TABLE `database1`.newtable (a int, newtable string)

insert into `database1`.newtable values (1, "val")
Run:

select a, newtable from `1111`.newtable
You will get 2 columns 1 int and the second one struct that contains 2 columns.
Is GBQ cannot distinction between column name and table name?

ms4446

BigQuery can distinguish between a table name and a column name. However, when a SQL query references both a table and a column with the same name without being explicit, it can lead to ambiguity. In your example, the newtable in the query might be interpreted as a reference to the entire table (as a STRUCT) rather than just the column named newtable.

To avoid this ambiguity, you can use aliasing or fully qualify the column name. It's also a good practice to avoid using the same name for tables and columns to prevent such issues. If possible, consider renaming either the table or the column to make your queries clearer and less prone to confusion.

View solution in original post

Issue create view when the source table and column have the same name