跨表关联子查询突然失效，是否需全部转为INNER JOIN？求官方确认

Hey Stephanie, sorry to hear this sudden error is derailing your team's analytics work—this kind of unexpected breakage is always frustrating! Let’s break down your questions and fix this query:

1. Is this subquery syntax really no longer supported? #

BigQuery has long had limitations on correlated subqueries that reference external tables, but your specific subquery doesn’t actually reference any fields from accounts_monthly (it’s an uncorrelated subquery in disguise). The likely issue is a recent tightening of BigQuery’s query parser/optimizer rules—previously, it might have automatically "de-correlated" this subquery behind the scenes, but now it’s enforcing the explicit rule more strictly.

So it’s not that this syntax is universally unsupported, but your particular pattern is now triggering the error because the optimizer isn’t auto-handling it anymore.

2. Do all such queries need to be converted to INNER JOIN? #

Not necessarily, but converting to a JOIN (or using a CTE) is the most reliable fix for this scenario, and aligns with BigQuery’s best practices. For your query, since the subquery only returns a single value (the max timestamp), you have a few clean options:

Option 1: Use a CTE to precompute the max date #

This is the most readable approach, especially if you need to reuse the max date elsewhere in the query:

-- standardSQL
WITH latest_revenue_timestamp AS (
  SELECT MAX(TIMESTAMP(date)) AS max_date
  FROM `historical_data.historical_revenue`
)
SELECT am.accounts, am.monthly_timestamp
FROM `custom_query_ingestion.accounts_monthly` am
CROSS JOIN latest_revenue_timestamp lrt
WHERE am.monthly_timestamp <= lrt.max_date

Option 2: Convert to an INNER JOIN #

Since the subquery returns one row, an INNER JOIN works just as well as a CROSS JOIN here:

-- standardSQL
SELECT am.accounts, am.monthly_timestamp
FROM `custom_query_ingestion.accounts_monthly` am
INNER JOIN (
  SELECT MAX(TIMESTAMP(date)) AS max_date
  FROM `historical_data.historical_revenue`
) lrt ON am.monthly_timestamp <= lrt.max_date

Option 3: Rewrite the scalar subquery (quick fix) #

If you want minimal changes, you can wrap the subquery in an extra layer to help the optimizer recognize it as uncorrelated:

-- standardSQL
SELECT accounts, monthly_timestamp
FROM `custom_query_ingestion.accounts_monthly`
WHERE monthly_timestamp <= (
  SELECT max_date FROM (
    SELECT MAX(TIMESTAMP(date)) AS max_date
    FROM `historical_data.historical_revenue`
  )
)

Key Takeaway #

For uncorrelated subqueries like yours, rewriting with a CTE or JOIN ensures compatibility with BigQuery’s current rules and makes your query more explicit for future maintainers. For truly correlated subqueries (where the subquery references fields from the outer table), you’ll need to refactor to use JOINs or window functions, but that’s not the case here.

内容的提问来源于stack exchange，提问作者Stephanie G