Interval data type in query result leads to exception

[thijs-obj]

Hello,

Not 100% sure whether this is a bug or a feature-request. Additionally I am not really sure whether this is blocked by work on supporting libraries. But basically having an interval data type (https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#interval_type) in the query result leads to a stack trace.

Environment details

Latest sqlalchemy-bigquery on Linux. Created a fresh virtualenv:

virtualenv venv
source venv/bin/activate
pip install sqlalchemy-bigquery

python --version
#  Python 3.8.10

pip freeze
# cachetools==5.0.0
# certifi==2021.10.8
# charset-normalizer==2.0.12
# future==0.18.2
# google-api-core==2.7.2
# google-auth==2.6.5
# google-cloud-bigquery==3.0.1
# google-cloud-bigquery-storage==2.13.1
# google-cloud-core==2.3.0
# google-crc32c==1.3.0
# google-resumable-media==2.3.2
# googleapis-common-protos==1.56.0
# greenlet==1.1.2
# grpcio==1.44.0
# idna==3.3
# numpy==1.22.3
# packaging==21.3
# proto-plus==1.20.3
# protobuf==3.20.0
# pyarrow==6.0.1
# pyasn1==0.4.8
# pyasn1-modules==0.2.8
# pyparsing==3.0.8
# python-dateutil==2.8.2
# requests==2.27.1
# rsa==4.8
# six==1.16.0
# SQLAlchemy==1.4.27
# sqlalchemy-bigquery==1.4.3
# urllib3==1.26.9

Steps to reproduce

Run a query that returns the Interval datatype, see code example

Code example

from sqlalchemy import create_engine

engine = create_engine('bigquery://')
query_nok = "SELECT                   (CAST('2022-01-01' AS TIMESTAMP) - cast('1990-01-01' AS TIMESTAMP))  AS x"
query_ok  = "SELECT EXTRACT(YEAR FROM (CAST('2022-01-01' AS TIMESTAMP) - cast('1990-01-01' AS TIMESTAMP))) AS x"

with engine.connect() as conn:
    res = conn.execute(query_ok)  # OK
    print([row for row in res])

    res = conn.execute(query_nok)  # FAILS
    print([row for row in res])

Stack trace

Traceback (most recent call last):
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/grpc_helpers.py", line 57, in error_remapped_callable
    return callable_(*args, **kwargs)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/grpc/_channel.py", line 946, in __call__
    return _end_unary_response_blocking(state, call, False, None)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/grpc/_channel.py", line 849, in _end_unary_response_blocking
    raise _InactiveRpcError(state)
grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
        status = StatusCode.FAILED_PRECONDITION
        details = "request failed: Unsupported field type: INTERVAL for column x"
        debug_error_string = "{"created":"@1650550774.130390860","description":"Error received from peer ipv4:142.250.179.202:443","file":"src/core/lib/surface/call.cc","file_line":903,"grpc_message":"request failed: Unsupported field type: INTERVAL for column x","grpc_status":9}"
>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "bq_interval.py", line 12, in <module>
    print([row for row in res])
  File "bq_interval.py", line 12, in <listcomp>
    print([row for row in res])
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 381, in iterrows
    for row in self._fetchiter_impl():
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/engine/cursor.py", line 1794, in _fetchiter_impl
    row = fetchone(self, self.cursor)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/engine/cursor.py", line 960, in fetchone
    self.handle_exception(result, dbapi_cursor, e)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/engine/cursor.py", line 941, in handle_exception
    result.connection._handle_dbapi_exception(
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2030, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/sqlalchemy/engine/cursor.py", line 955, in fetchone
    row = dbapi_cursor.fetchone()
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/cloud/bigquery/dbapi/_helpers.py", line 494, in with_closed_check
    return method(self, *args, **kwargs)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 325, in fetchone
    self._try_fetch()
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 256, in _try_fetch
    rows_iterable = self._bqstorage_fetch(bqstorage_client)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 295, in _bqstorage_fetch
    read_session = bqstorage_client.create_read_session(
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/cloud/bigquery_storage_v1/services/big_query_read/client.py", line 616, in create_read_session
    response = rpc(
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/gapic_v1/method.py", line 154, in __call__
    return wrapped_func(*args, **kwargs)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/retry.py", line 283, in retry_wrapped_func
    return retry_target(
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/retry.py", line 190, in retry_target
    return target()
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/grpc_helpers.py", line 59, in error_remapped_callable
    raise exceptions.from_grpc_error(exc) from exc
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/exceptions.py", line 603, in from_grpc_error
    details, err_info = _parse_grpc_error_details(rpc_exc)
  File "/home/thijs/tmp/interval-sqlalchemy/venv/lib/python3.8/site-packages/google/api_core/exceptions.py", line 553, in _parse_grpc_error_details
    status = rpc_status.from_call(rpc_exc)
AttributeError: 'NoneType' object has no attribute 'from_call'

Thanks in advance for looking at this!

[meredithslota]

Hi @thijs-obj — thanks for filing a request! This is still blocked on support in the core library, tracked in these two issues:
googleapis/python-bigquery#826 for support and googleapis/python-bigquery#836 for connectors support.

Tagging this as a feature request. :)

[thijs-obj]

Strangely enough. This seems to work now, even though nothing changed in my code or in the used libraries. Could it be that something server-side changed?

[guyarad]

I am (newly) experiencing this issue - anyone has any resolution?