BigQuery: timestamps not populated correctly for parameterized queries

[tswast]

• Stacktrace

$ python sync_query_params.py timestamp
Traceback (most recent call last):
  File "sync_query_params.py", line 176, in <module>
    sync_query_timestamp_params()
  File "sync_query_params.py", line 124, in sync_query_timestamp_params
    query_results.run()
  File "/Users/swast/src/google-cloud-python/bigquery/google/cloud/bigquery/query.py", line 360, in run
    method='POST', path=path, data=self._build_resource())
  File "/Users/swast/venvs/bq-dml/lib/python3.5/site-packages/google/cloud/_http.py", line 335, in api_request
    error_info=method + ' ' + url)
google.cloud.exceptions.BadRequest: 400 Unparseable query parameter `ts_value` in type `TYPE_TIMESTAMP`, Invalid timestamp: '1.4810976E9' value: '1.4810976E9' (POST https://www.googleapis.com/bigquery/v2/projects/cloud-samples-tests/queries)

• Steps to reproduce

Attempt to use TIMESTAMP type in a scalar query parameter.

• Code example

GoogleCloudPlatform/python-docs-samples@a788ea1

The timestamp type should not be sent as a floating point value. It should take this format: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type Note that this is different from RFC 3339. There is an issue open at https://code.google.com/p/google-bigquery/issues/detail?id=842 for accepting RFC 3339 in TIMESTAMP query parameters.

[tseaver]

@tswast I have passing system tests which show the back-end sending TIMESTAMP values back as floating-point values. Are you saying that the back-end expects those values to be sent as string timestamps, in spite of how it returns them? For instance, here is the result from a query including a timestamp value:

{'_client': <google.cloud.bigquery.client.Client object at 0x7f399dfb33d0>,
 '_configuration': <google.cloud.bigquery.query._SyncQueryConfiguration object at 0x7f399e250090>,
 '_job': None,
 '_properties': {u'cacheHit': True,
                 u'jobComplete': True,
                 u'jobReference': {u'jobId': u'job_N75jt8A_drBKYhdW9gbvuImxAWQ',
                                   u'projectId': u'<MY-PROJECT-ID>'},
                 u'kind': u'bigquery#queryResponse',
                 u'rows': [{u'f': [{u'v': u'1.480941669E9'}]}],
                 u'schema': {u'fields': [{u'mode': u'NULLABLE',
                                          u'name': u'f0_',
                                          u'type': u'TIMESTAMP'}]},
                 u'totalBytesProcessed': u'0',
                 u'totalRows': u'1'},
 '_query_parameters': (),
 '_udf_resources': (),
 'query': 'SELECT TIMESTAMP "2016-12-05 12:41:09"'}

[tswast]

Your example seems to be using legacy SQL. Data types are different in standard SQL.

But yes, the format expected by query parameters is different from the format in the response.

[tseaver]

Your example seems to be using legacy SQL. Data types are different in standard SQL.

Nope, all the queries in that system test pass the useLegacySql flag as false.

But yes, the format expected by query parameters is different from the format in the response.

That is a definite API smell, if not an outright bug: there should be one-and-only-one canonical way to spell the wire format for any given data type (as opposed to the formats humans might type in a query).

[tswast]

Agreed, that's why I filed https://code.google.com/p/google-bigquery/issues/detail?id=842

[tswast]

Thanks! I verified this works with the sample I've written.