BigQuery CASE WHEN ELSE statement incomplete results

Question

When using a large case-when-else statement in BigQuery, the results don't add up.

Sometimes we get 10 buckets but not all. Often we get a few buckets in sequence, like 1 - 8 and then we would expect it to return bucket 9 but it doesn't, it returns 15 or something.

Note that this is just an example, our actual query has over 100 WHEN statements. In this case we get maybe 25 buckets and then it 'restarts' somewhere down the list.

The Query Reference does not state that there is a maximum number of WHEN statements. So what could be causing this behavior?

SELECT Count(Items) AS item_count, 
CASE 
WHEN (a >= 0 AND a < 50) THEN 1 
WHEN (a >= 50 AND a < 100) THEN 2 
WHEN (a >= 100 AND a < 150) THEN 3 
WHEN (a >= 150 AND a < 200) THEN 4 
WHEN (a >= 200 AND a < 250) THEN 5 
WHEN (a >= 250 AND a < 300) THEN 6 
WHEN (a >= 300 AND a < 350) THEN 7 
WHEN (a >= 350 AND a < 400) THEN 8 
WHEN (a >= 400 AND a < 500) THEN 9 
WHEN (b >= 100 AND b < 200) THEN 10 
WHEN (b >= 200 AND b < 300) THEN 11 
WHEN (b >= 300 AND b < 400) THEN 12 
WHEN (b >= 400 AND b < 500) THEN 13 
WHEN (b >= 500 AND b < 1000) THEN 14 
WHEN (b >= 1000 AND b < 2000) THEN 15 
ELSE -1
END AS bucket
FROM [dataset1.sometable]
GROUP BY bucket

Answer 1

It turns out that in our case more than one when can be true which is messing with the result. The CASE-WHEN-ELSE statement is unable to deal with that.