Legacy SQL查询异常编译问题：未替换占位符仍执行是否为预期行为？

Yes, this is expected behavior in BigQuery Legacy SQL—let me break down what's happening here, and how you can avoid these silent failures going forward:

Why your query worked (even with the un-replaced placeholder) #

Legacy SQL has a quirky implicit rule for strings containing [[...]] syntax. When it encounters the string literal '[[date]]', it interprets that as a shortcut to the current date (formatted as YYYY-MM-DD). So your query effectively ran as:

SELECT DATE(ComputationDate) as date 
FROM [project:dataset.table] 
WHERE DATE(ComputationDate) < CURRENT_DATE() 
ORDER BY date

That's why it returned all rows with a ComputationDate before today—no error, just unintended (but technically valid) results.

Why Standard SQL throws an error #

Standard SQL was designed to be stricter and more predictable. It doesn't recognize [[date]] as any special token, so '[[date]]' is just an invalid date string (it doesn't match the required YYYY-MM-DD format for date comparisons). This strictness is intentional—it prevents silent, unexpected behavior like what you saw in Legacy SQL.

How to catch unprocessed placeholders in your code #

Since Legacy SQL lets these slip through without warnings, you'll need to add safeguards to your code:

• Validate query strings before execution: Add a check that scans for remaining [[ or ]] substrings. If any are found, throw an error instead of sending the query to BigQuery.
• Migrate to Standard SQL: If possible, switching to Standard SQL will make these issues impossible to miss—invalid literals will immediately trigger an error, so you'll know right away if your placeholder replacement failed.
• Use parameterized queries: Instead of string replacement, use BigQuery's query parameter support (available in both dialects). This is a safer approach: you define a parameter in your query (like ? in Legacy SQL) and pass the date value separately, eliminating the risk of un-replaced placeholders entirely.

内容的提问来源于stack exchange，提问作者Joseph Yourine