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 · Accepted Answer · 2016-06-28 18:36:43Z

1

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".

answered Jun 28, 2016 at 18:36

Danny Kitt

3,26114 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wouter Over a year ago

Thank you Danny! Indeed, the data type juggling got me again. Perhaps it's also a good idea to add in your documentation what the exact data type is of each function's return value. Right now there's only a literal example.

Danny Kitt Over a year ago

Yes, agreed our documentation would benefit from being more specific about return types. We've taken this into account and in our standard SQL function documentation, many of the functions document their input and output types explicitly.

Collectives™ on Stack Overflow

BigQuery query doesn't work inside a view

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related