Upcoming change to field visibility in SELECT statements

[Seth Hollyman]

Google BigQuery is rolling out an update to our query execution engine during the week of August 3, 2015. As part of this update, we will apply stricter checks on field visibility in SELECT statements. To avoid errors during this rollout, please ensure that your queries do not use any of the undocumented patterns below.

Pattern 1: Subquery implicitly returns fields referenced in non-SELECT clauses

Currently, some fields are implicitly returned from a subquery when they are referenced in a clause other than a SELECT clause (e.g., a WHERE clause). After the update, these fields must be explicitly mentioned in the SELECT clause.

Example query:

SELECT foo, bar FROM (SELECT bar FROM table WHERE foo = 42)

To resolve this issue, explicitly add the desired field to the SELECT clause:

SELECT foo, bar FROM (SELECT foo, bar FROM table WHERE foo = 42)

Pattern 2: Subquery returns original field name in addition to aliased field name

Currently, if a field is assigned an alias using a SELECT clause, the original name for the field may still be visible outside the subquery. After the update, an aliased field can only be referenced via the alias.

Example query:

SELECT a.foo FROM (SELECT a.foo AS foo_alias FROM table)

To resolve this issue, either remove the alias or reference the field using the alias:

SELECT a.foo FROM (SELECT a.foo FROM table)

or

SELECT foo_alias FROM (SELECT a.foo AS foo_alias FROM table)

-The Google BigQuery Team