Inconsistent Results for Boolean Filters in Bigquery based on whether the table is aliased

Question

I'm getting really really inconsistent results, and I can't tell why. Here is the most obvious example:

Two queries:

select * from my_table
where invitation_deleted = true
-- returns 118 results

select * from my_table t
where t.invitation_deleted = true
-- returns 0 results

But there are other examples! Such as when defining the view in my select statement

 CASE
    WHEN my_field IS NULL THEN FALSE
    ELSE TRUE
 END as my_computed_field
...
-- This will allow me to do "where my_computed_field = true" and get results
-- where as this:

my_field IS NOT NULL AS my_computed_field
-- this will not work if I go "where my_computed_field = true"

There seem to be many flavors of this... where my computed boolean field is extremely finicky, and I can't tell when it will or will not work as expected. Am I fundamentally misunderstanding something about how BigQuery handles booleans?

Seems, your query context is more complex then you show. "invitation_deleted" column in first and second query example may refer to different tables. — ValNik
– ValNik, Commented Jul 7 at 20:59
There is only one table, and all i'm doing is aliasing the table! (I can't show the table exact name as that would reveal internal company data but rest assured, one table!) — matty-d
– matty-d, Commented Jul 7 at 21:37
Can you provide a minimal reproducible example using dbfiddle.uk/HuZKDYgb — Dale K
– Dale K, Commented Jul 7 at 22:20

jggp1094 · Accepted Answer · 2025-07-11 14:17:33Z

Double check if both of your queries are actually referencing the same table in the same dataset/project. Is invitation_deleted column really a boolean type? try to identify what the column really holds and its data type:

 SELECT DISTINCT   invitation_deleted,   
typeof(invitation_deleted) AS col_type 
FROM my_table 
ORDER BY invitation_deleted;

Also observe the behavior of each:

SELECT COUNT(*) FROM my_table WHERE invitation_deleted IS TRUE;
SELECT COUNT(*) FROM my_table WHERE invitation_deleted = TRUE;
SELECT COUNT(*) FROM my_table WHERE CAST(invitation_deleted AS STRING) = 'true';

If it is indeed a single table on the same dataset/project and correct data type, Consider creating a new table with a small subset of your data and running the same tests to see if the issue is reproducible. This can help isolate if it's data-specific or a broader BigQuery behavior.

Collectives™ on Stack Overflow

Inconsistent Results for Boolean Filters in Bigquery based on whether the table is aliased

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related