Weird error in BigQuery

Question

I trying to execute query directly from web console https://bigquery.cloud.google.com

One time query executed and I got result another time I got the error on the same query:

Error: TABLE_QUERY expressions cannot query BigQuery tables.

I also tried with different query option "Use Cached Results", "Interactive" and "Batch" behaviour the same.

Why it could happens?

Answer 1

TABLE_QUERY filters are intended to query only metadata. For a brief period of time, it was possible to query table data in your TABLE_QUERY filter; we recently closed this loophole.

We're in the process of adding a whitelist for customers who are negatively impacted by this; the whitelist will allow them to query data within the same dataset in their TABLE_QUERY expression, until they can update their queries.

Note that you can still use __TABLE_SUMMARY__ within the TABLE_QUERY experssion if you want to find the max table id, for example. For example:

SELECT foo 
FROM  TABLE_QUERY(my_dataset, 
  'table_id IN (SELECT MAX(table_id) 
   FROM my_dataset.__TABLES_SUMMARY__ 
   WHERE table_id CONTAINS "my_prefix")')
LIMIT 1

If this doesn't work for you, or you want your project to be whitelisted, please contact [email protected].