Unexpected results for a nested query with multiple _TABLE_SUFFIX conditions

user_8921651 0
Viewed 135

Consider the following query (standard syntax):

WITH
  v AS (
  SELECT
    _TABLE_SUFFIX AS y,
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod*`
  WHERE
    _TABLE_SUFFIX > '2000' )
SELECT
  y,
  COUNT(1) c
FROM
  v
WHERE
  y IN ('2016',
    '2017')
GROUP BY
  y

I was expecting it to return two rows:

2016 4292866     
2017 3683760

However, only the first one is returned. I find it very weird. Is there any reason for this?

If you remove WHERE _TABLE_SUFFIX > '2000' from the subquery, both rows are returned as expected. Also, if you remove 2016 from the list at the bottom, the single row for 2017 is returned correctly.

Is this the expected behavior?

google-bigquery
1 Answers

One can work it around by casting _table_suffix to integer
Credit goes to Przemek Iwańczak.

WITH
  v AS (
  SELECT
    _TABLE_SUFFIX AS y,
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod*`
  WHERE CAST(_TABLE_SUFFIX as INT64) > 2000
    )
SELECT
  y,
  COUNT(1) c
FROM
  v
WHERE
  y IN ('2016',
    '2017')
GROUP BY
  y
user_38051310
Related