Reusable column aliases in BigQuery

With reusable column aliases on, a SELECT alias can be used by later SELECT items and the WHERE of the same query. How to turn the option on per project and region, where an alias is visible, which name wins on a clash, and what BigQuery refuses.

A reusable column alias is a name you give an expression in the SELECT list and then use again, further right in the same list or in the WHERE of the same query. SELECT net_price * 1.25 AS gross_price, gross_price * 0.5 AS discounted works without a subquery or a CTE. It only works when the project option reusable_column_aliases_mode is on, and it is off by default. With the option off, the same query fails with Unrecognized name.

It is off until someone turns it on

SELECT net_price, net_price * 1.25 AS gross_price, gross_price * 0.5 AS discounted
FROM UNNEST([8.0, 20.0, 40.0]) AS net_price

BigQuery looks for gross_price among the columns of the FROM clause, finds none, and stops. The usual fix is a subquery or a CTE that defines gross_price one level down.

With the option on, the same query runs. Each alias is visible to the items after it, left to right:

SELECT net_price, net_price * 1.25 AS gross_price, gross_price * 0.5 AS discounted
FROM UNNEST([8.0, 20.0, 40.0]) AS net_price

gross_price is 10 for the first row, and discounted is half of it. The alias can also drive the WHERE:

SELECT product, net_price * 1.25 AS gross_price
FROM UNNEST([STRUCT('pen' AS product, 4.0 AS net_price), ('lamp', 20.0), ('desk', 160.0)])
WHERE gross_price BETWEEN 5 AND 100
ORDER BY gross_price

Turn it on

The option is a project setting. You need the BigQuery Admin role, or the bigquery.projects.update permission on the project. Set it with ALTER PROJECT and a region qualifier:

ALTER PROJECT `my-project`
SET OPTIONS (`region-us.reusable_column_aliases_mode` = "on");

The qualifier and the option name go in one pair of backticks. Without the region, BigQuery refuses the statement and says the option “is a regional project option and must be backtick-enclosed and preceded by a region qualifier”.

A few facts that decide whether it works for you:

• It is per project and per region. Setting it for region-us does nothing for a job that runs in EU or us-central1, and does nothing for another project.
• It takes about two minutes to apply. For a while after the statement succeeds, some jobs still behave as if the option were off. Retry before you trust a result.
• The value is a string. "on", "On" and 'on' are the same. TRUE fails with a type error and an unknown word such as "bogus" fails with 'bogus' is not a valid value.
• "off" and NULL both turn it off. Setting it to NULL restores the default.
• An organization can set it too. ALTER ORGANIZATION SET OPTIONS (region-us.reusable_column_aliases_mode = "on") takes the same value and needs the bigquery.organizations.update permission. We checked that the statement validates with a dry run but did not run it.

Check whether it is on

The cheapest check is a dry run of a query that reads an alias. It processes 0 bytes and needs no access to INFORMATION_SCHEMA:

SELECT 1 AS a, a + 1 AS b

If the query validates, the option is on for that project and region. Unrecognized name: a means it is off. Run the dry run in the project and location your queries use, because the answer is per region.

You can also read the setting. INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS has a row for the option while it is set, with the value ON. No row means the default, which is off. PROJECT_OPTIONS lists it as well, and it can take about 20 seconds to show a change. Both need permission to read the views, and the effective view scans enough data to be billed the 10 MB minimum. See INFORMATION_SCHEMA.

Where an alias is visible

With the option on, an alias can be read:

• by later items in the same SELECT list, never by earlier ones;
• in the WHERE, GROUP BY, HAVING, ORDER BY, QUALIFY and WINDOW clauses of the same query block, and inside OVER (...);
• by scalar, EXISTS and IN subqueries in the select list or the WHERE;
• after *, and under DISTINCT and SELECT AS STRUCT;
• through a struct path: after STRUCT(1 AS a) AS s, the expression s.a + 1 works.

Names are matched without regard to case, backticked names included. An alias of an aggregate or a window function can be used inside a plain expression in a later item.

A subquery sees the alias too:

SELECT amount * 2 AS doubled, (SELECT doubled * 10) AS scaled
FROM UNNEST([1, 2]) AS amount

Order matters. An item cannot read an alias that is defined after it:

SELECT gross_price * 0.5 AS discounted, net_price * 1.25 AS gross_price
FROM UNNEST([8.0, 20.0]) AS net_price

An alias is computed once per row. SELECT RAND() AS r, r = r returns true, because both reads see the same value.

Where it is not visible

JOIN ... ON cannot read an alias. The ON condition belongs to the FROM clause, not to the select list:

WITH orders AS (
  SELECT 1 AS id, 10 AS customer_id UNION ALL SELECT 2, 20
),
customers AS (
  SELECT 11 AS id, 'Ada' AS name UNION ALL SELECT 21, 'Grace'
)
SELECT o.id, o.customer_id + 1 AS customer_key, c.name
FROM orders AS o
JOIN customers AS c ON c.id = customer_key

Branches of a UNION do not share aliases either. Pipe syntax has no reusable aliases inside one operator. The outputs of an operator are visible to the operators after it, but not to its own expressions:

FROM UNNEST([1, 2, 3]) AS x
|> EXTEND x * 2 AS doubled, doubled + 1 AS plus_one

Put the second alias in a second |> EXTEND and it works. Pipe |> SELECT cannot use lateral aliases, and the documentation lists Graph Query Language as unsupported too.

When the name is also something else

A name in a query can be a column, an alias, a column of an outer query, an argument of a function, or a script variable. BigQuery picks in this order:

1. a column of the FROM clause;
2. an alias of the select list;
3. a column of the outer query;
4. an argument of the user-defined function the query runs in;
5. a script variable.

A table column wins over an alias. Here price is both, and price + 1 uses the column:

SELECT 100 AS price, price + 1 AS plus_one
FROM UNNEST([5, 7]) AS price

The output column price is 100, but plus_one is 6 and 8, built from the column values 5 and 7.

An alias beats the outer-query column, the function argument and the script variable. This is the part to watch when you turn the option on, because a query that worked yesterday can change its answer. The query does not fail, so nothing tells you. The documentation calls this semantic drift.

An outer column is shadowed by an alias in a subquery. The query below compares id with category_id, and the subquery also defines an alias called category_id. We run it with the option off first:

WITH product AS (
  SELECT 'pen' AS name, 1 AS category_id UNION ALL SELECT 'lamp', 2
)
SELECT name, category_id,
  (SELECT AS STRUCT id AS matched_id, 100 AS category_id
   FROM UNNEST([1, 2, 100]) AS id
   WHERE id = category_id) AS hit
FROM product

The WHERE compares with the outer row’s category_id, 1 for the pen and 2 for the lamp, so each row matches its own id. With the option on, the subquery’s 100 AS category_id takes over:

WITH product AS (
  SELECT 'pen' AS name, 1 AS category_id UNION ALL SELECT 'lamp', 2
)
SELECT name, category_id,
  (SELECT AS STRUCT id AS matched_id, 100 AS category_id
   FROM UNNEST([1, 2, 100]) AS id
   WHERE id = category_id) AS hit
FROM product

Now both rows get matched_id 100, because the WHERE reads the alias.

A script variable is shadowed the same way. With the option off, price_factor * 2 uses the variable:

DECLARE price_factor INT64 DEFAULT 10;
SELECT net_price * 3 AS price_factor, price_factor * 2 AS doubled
FROM UNNEST([2, 4]) AS net_price

With the option on, it uses the alias:

DECLARE price_factor INT64 DEFAULT 10;
SELECT net_price * 3 AS price_factor, price_factor * 2 AS doubled
FROM UNNEST([2, 4]) AS net_price

A function argument follows the same rule. In the documentation’s example, a function with an argument val runs SELECT AS STRUCT 5 AS val, val + 10. With the option off it returns 110, from the argument 100. With the option on it returns 15, from the alias. Subqueries with their own FROM still win over the outer alias: (SELECT y FROM UNNEST([7]) y) reads 7.

The safe habit is to give an alias a name that no column, argument or variable in scope uses.

What BigQuery refuses

With the option on, BigQuery refuses a few reads that would be ambiguous or have no defined value. The errors below are BigQuery’s own.

The same name twice

If two items define the same alias and a later item reads it, the read is ambiguous: Column name y is ambiguous. A duplicate that nobody reads is fine, and the result columns are named y and y_1. Only duplicates defined before the read count, so SELECT 1 AS y, y + 1 AS z, 2 AS y is accepted. In the WHERE, all duplicates count.

The name that comes from a column path counts as an alias for this rule. s.price is called price:

SELECT s.price, 2 AS price, price + 1 AS next_price
FROM (SELECT STRUCT(1 AS price) AS s)

Grouped queries

When the query groups or aggregates, an alias of a grouping expression cannot be read in the WHERE or inside the argument of an aggregate. Here next_day is an alias of the grouping column plus one:

SELECT order_day + 1 AS next_day, COUNT(*) AS orders
FROM UNNEST([1, 1, 2, 3]) AS order_day
WHERE next_day > 2
GROUP BY order_day

The same message appears for an alias of an aggregate (SUM(x) AS s ... WHERE s > 0, or SUM(s)). It also fires when the read is nested: inside a subquery in the WHERE, inside a subquery in an aggregate argument, and with GROUP BY ALL. An aggregate that sits inside a nested subquery does not trigger it.

HAVING and ORDER BY run after the groups, so they accept the alias:

SELECT order_day + 1 AS next_day, COUNT(*) AS orders
FROM UNNEST([1, 1, 2, 3]) AS order_day
GROUP BY order_day
HAVING next_day > 2
ORDER BY next_day

Window function aliases

An alias of a window function cannot be read in the WHERE, in PARTITION BY or in a window ORDER BY. A window function cannot take another window function’s alias as an argument either:

SELECT sensor, COUNT(*) OVER () AS total
FROM UNNEST(['a', 'b', 'c']) AS sensor
WHERE total > 2

SELECT COUNT(*) OVER () AS c, SUM(c) OVER () fails with Analytic function cannot be an argument of another analytic function. A window over a plain alias is fine, and so is ROW_NUMBER() OVER (ORDER BY y) for an alias y. With the option off, all of these are Unrecognized name. See window functions.

Subqueries without a FROM clause

A subquery with no FROM cannot read the alias of an aggregate or a window function:

SELECT SUM(amount) AS total, (SELECT total) AS again
FROM UNNEST([10, 20]) AS amount

Give the subquery a FROM, even one over UNNEST([1]), and it reads the computed value.

In Querylab.io

The editor checks the option for you. It dry-runs the check query SELECT 1 AS a, a + 1 AS b in the project and location of the active query tab, for free, and follows the answer. With the option on, an alias read is no longer flagged, completions offer the aliases defined so far, hover shows them, and the refusals above show up as errors with BigQuery’s wording. With the option off, the read is flagged as an unrecognized name. If the check cannot tell, for example because the dry run failed, the editor treats the option as off and tries again after five minutes. The answer is kept per project and location for the session. When you switch to a tab in another project or region, the editor checks that one and follows it. See Code quality.

Learn and Querylab.io Local run on the Querylab.io engine, which starts with the option off, as BigQuery does. ALTER PROJECT ... SET OPTIONS (region-us.reusable_column_aliases_mode = "on") turns it on for that session. The engine then follows the rules on this page, with BigQuery’s error texts. INFORMATION_SCHEMA.PROJECT_OPTIONS and EFFECTIVE_PROJECT_OPTIONS show the setting there.