_TABLE_SUFFIX BETWEEN syntax is not selecting any tables

Question

I'm looking at the public GitHub events dataset githubarchive.day.YYYYMMDD to pull public events that belong to me.

For this I use a simple query like:

SELECT id, actor.login, type
FROM `githubarchive.day.2*`
WHERE
  _TABLE_SUFFIX BETWEEN '20200520' AND '20200528'
AND actor.login='ahmetb'

This BETWEEN clause doesn't seem to be matching any tables according to this message

Query complete (0.4 sec elapsed, 0 B processed)

If I use a simpler syntax like this it works:

SELECT id, actor.login, type
FROM `githubarchive.day.202005*`
WHERE actor.login='ahmetb'

Query complete (2.2 sec elapsed, 2.4 GB processed)

However using the wildcard syntax directly in FROM is not an option for me as I determine the table suffix dynamically through a query parameter.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-05-29T02:33:49

Below is correct version

SELECT id, actor.login, type
FROM `githubarchive.day.2*`
WHERE
  _TABLE_SUFFIX BETWEEN '0200520' AND '0200528'
AND actor.login='ahmetb'

Note: you needed to remove first 2 in dates in below line

_TABLE_SUFFIX BETWEEN '0200520' AND '0200528'

Or you might wanted below one

SELECT id, actor.login, type
FROM `githubarchive.day.*`
WHERE
  _TABLE_SUFFIX BETWEEN '20200520' AND '20200528'
AND actor.login='ahmetb'

which makes more sense to me