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

[Vic-tator]

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 · Accepted solution]

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.

[Vic-tator]

Hey, yes your solution is working good (alias or use different name).
The main thing that i am not sure i understand is why it happing, is it i bug in GBQ or this is by design?
When you are running: select <column1>, <column1> from <table>
As far as i know after the select you put what columns do you want to select from the table.
Why GBQ thinks that it's ok to select table from table?

[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 you do encounter such a situation, being explicit in your SQL (using aliases or fully qualifying column names) can help clarify your intent to the SQL parser.

In summary, while the behavior might seem unintuitive, it's a result of the flexibility and power of SQL combined with naming ambiguities. It's always a good idea to be as clear as possible in your SQL queries to ensure they're interpreted as intended.

[HannaZaboura]

Hi ms4446,
The question here is actually why BigQuery might interpret a column in the select statement as a table? and how is it returning its data? is it doing a join between the table according to a specific column?

[ms4446]

Why might BigQuery interpret a column in the SELECT statement as a table?

BigQuery may interpret a column in the SELECT statement as representing the entire table when the column name matches the table name. This behavior arises because BigQuery can treat ambiguous references as a request for the entire row, represented as a STRUCT, especially when the column name isn't explicitly qualified.

How is the data returned?

When BigQuery treats a column reference as a representation of the entire table, it returns the data as a STRUCT. A STRUCT in BigQuery is a complex data type that encapsulates multiple fields within a single field, with each inner field corresponding to a column of the table.

For instance, consider the query:

SELECT newtable FROM `database1`.newtable

The output of this query will be a STRUCT with fields corresponding to each column in the newtable table:

• newtable.a: Represents the value from the a column.
• newtable.newtable: Represents the value from the newtable column.

Is BigQuery performing a self-join on the table?

No, BigQuery isn't executing a self-join. Instead, it's returning the entire row's data as a STRUCT due to the naming ambiguity.

How can this ambiguity be avoided?

To prevent such ambiguity:

1. Use an Alias: Rename the column in the query to avoid confusion. For example:

SELECT newtable AS my_column FROM `database1`.newtable

2. Fully Qualify the Column Name: Specify both the table name and the column name. For instance:

SELECT `database1`.newtable.newtable FROM `database1`.newtable

Best Practices: It's advisable not to use identical names for tables and their columns to sidestep such issues.