Loading a file with a RANGE<DATE> data type fails with a 500 error

[caleb-palmer]

Attempting to load a json file with a RANGE column type fails with a 500 error (Error: 4550751). This failure occurs for both struct format and RANGE literal string format. However, using Client.insert_rows_json() works as expected. I'm unable to tease out what is creating this failure.

Environment details

• OS type and version: Windows 11
• Python version: 3.12.12
• google-cloud-bigquery version: 3.40.1

Steps to reproduce

1. Create an empty table in bigquery with a RANGE column.
2. Attempt to load a json string.

Code example

from google.cloud import bigquery
from google.cloud.exceptions import NotFound
bq = bigquery.Client()

test_tab = bigquery.Table(
    table_ref = 'example-project.example_dataset.example_table',
    schema = [
        bigquery.SchemaField(name = "Range_Test", field_type = "RANGE", mode = "NULLABLE", range_element_type = "DATE")
    ]
)

try:
    bq.get_table(test_tab)
except NotFound:
    bq.create_table(test_tab)

# test with json struct
bq.load_table_from_json(destination = 'example-project.example_dataset.example_table', json_rows=[{"Range_Test": {"start": "2024-01-01", "end": "2025-01-01"}}]).result()
# InternalServerError: 500 An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: [https://cloud.google.com/bigquery/sla.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) If the error continues to occur please contact support at [https://cloud.google.com/support.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#)https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support.[https://cloud.google.com/bigquery/sla.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) If the error continues to occur please contact support at [https://cloud.google.com/support.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) Error: 4550751; reason: internalError, message: An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support. Error: 4550751

# test with RANGE literal
bq.load_table_from_json(destination = 'prod-edap-ws-ae.PALMER_TEMP.JSON_LOAD_TEST', json_rows=[{"Range_Test": "[2024-01-01, 2025-01-01)"}]).result()
# InternalServerError: 500 An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: [https://cloud.google.com/bigquery/sla.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) If the error continues to occur please contact support at [https://cloud.google.com/support.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#)https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support.[https://cloud.google.com/bigquery/sla.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) If the error continues to occur please contact support at [https://cloud.google.com/support.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) Error: 4550751; reason: internalError, message: An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support. Error: 4550751

bq.load_table_from_json(destination = 'prod-edap-ws-ae.PALMER_TEMP.JSON_LOAD_TEST', json_rows=[{"Range_Test": None}]).result()
# InternalServerError: 500 An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: [https://cloud.google.com/bigquery/sla.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) If the error continues to occur please contact support at [https://cloud.google.com/support.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#)https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support.[https://cloud.google.com/bigquery/sla.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) If the error continues to occur please contact support at [https://cloud.google.com/support.](vscode-file://vscode-app/c:/Users/ptb45737/AppData/Local/Programs/Positron/resources/app/out/vs/code/electron-browser/workbench/workbench.html#) Error: 4550751; reason: internalError, message: An internal error occurred and the request could not be completed. This is usually caused by a transient issue. Retrying the job with back-off as described in the BigQuery SLA should solve the problem: https://cloud.google.com/bigquery/sla. If the error continues to occur please contact support at https://cloud.google.com/support. Error: 4550751

bq.insert_rows_json(test_tab, [{"Range_Test": {"start": "2024-01-01", "end": "2025-01-01"}}])
# []

bq.insert_rows_json(test_tab, [{"Range_Test": None}])
# []