I spent some time and resources running and verifying a simple query in BigQuery which gave me zero results. I know for sure though that this query should give me results, so I wonder why this is occurring and whether it's swallowing some kind of error. I know it seems more likely that I made a mistake in my query, but I checked this multiple times with different people and really do not see where my mistake could possibly be.
We have our table audit_v3.mutations which contains around 25 billion audit records. These records have a required field sourceId and a required field called deltaStatus which can have the values "new", "stale" or "delta".
Functionally we wouldn't expect it to be possible that there are "stale" records for a given sourceId without there being "new" records, but we did find some occurrences of this, for example
SELECT
deltaStatus
FROM
`project.audit_v3.mutations`
WHERE
sourceId = '497786711'
Results in:
stale
stale
stale
stale
stale
stale
But when we wanted to check how often this occurs in total:
SELECT
sourceId
FROM
`project.audit_v3.mutations`
WHERE
WHERE deltaStatus = "stale"
AND sourceId NOT IN (
SELECT
sourceId
FROM
`project.audit_v3.mutations`
WHERE
deltaStatus = "new" )
This query results in No Results, even though we know it should at least return sourceId '497786711'.
My guess is that the query actually fails somewhere underwater because the "not in" clause is too big, but I would've expected an error as the result instead of "No results". For reference, there are 23 billion "new" records and 1.5 billion "stale".
As a workaround it did work to run the queries on "stale" and "new" separately and store them in their own tables.