BigQuery query doesn't work inside a view

Question

When I select from a view I've created I receive the following error from time to time, but not always:

query: Timestamp evaluation: connection error. (error code: invalidQuery)
Job ID  vex-custom:bquijob_59705b02_155966ddc5f
Start Time  Jun 28, 2016, 11:53:50 AM
End Time    Jun 28, 2016, 11:53:50 AM

Running the query by itself works perfectly well.

There's two 2 special things about this query:

It uses TABLE_DATE_RANGE
It references tables from another project then where the view resides. But we've done this a lot of times without issues

Can someone from Google perhaps check the job id?

Danny Kitt Danny Kitt · Accepted Answer · 2016-06-28T18:36:43

I checked the internal details for your query. The view that your failed query references makes a few problematic calls to TIMESTAMP functions. Here's one example:

SELECT * FROM TABLE_DATE_RANGE([...], TIMESTAMP(DATE_ADD(UTC_USEC_TO_DAY(CURRENT_DATE()), -15, "day")), CURRENT_TIMESTAMP())

Specifically, the call to TIMESTAMP(DATE_ADD(UTC_USEC_TO_DAY(CURRENT_DATE()), -15, "day")) is erroring because:

UTC_USEC_TO_DAY returns an INTEGER, not a TIMESTAMP.
DATE_ADD expects an argument of type TIMESTAMP.

You can wrap the call to UTC_USEC_TO_DAY with USEC_TO_TIMESTAMP to convert the argument to type TIMESTAMP, like so: TIMESTAMP(DATE_ADD(USEC_TO_TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_DATE())), -15, "day"))

We're in the process of rolling out a release that more closely checks the expected input types of many timestamp functions, which is why you are currently seeing inconsistent behavior. These fixes prevent issues where some functions can return malformatted TIMESTAMPs, and also brings our behavior more in line with our documentation on timestamp functions.

Separately, we need to work on making sure errors that occur within the evaluation of timestamps for TABLE_DATE_RANGE return more useful errors than "connection error".

BigQuery query doesn't work inside a view

1 Answers