BigQuery errors when filters on text columns have apostrophes in them

[jvines]

Bug description

The bug is that BigQuery throws an error 400 POST on filters (and charts) when filters on text columns have apostrophes in them.

Screenshots/recordings

Superset version

4.1.3

Python version

3.10

Node version

16

Browser

Chrome

Additional context

The traceback is as follows:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/dbapi/cursor.py", line 220, in _execute
    rows = client.query_and_wait(
  File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/client.py", line 3611, in query_and_wait
    return _job_helpers.query_and_wait(
  File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/_job_helpers.py", line 509, in query_and_wait
    return job_retry(do_query)()
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 293, in retry_wrapped_func
    return retry_target(
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 153, in retry_target
    _retry_error_helper(
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_base.py", line 212, in _retry_error_helper
    raise final_exc from source_exc
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 144, in retry_target
    result = target()
  File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/_job_helpers.py", line 450, in do_query
    response = retry(client._call_api)(
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 293, in retry_wrapped_func
    return retry_target(
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 153, in retry_target
    _retry_error_helper(
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_base.py", line 212, in _retry_error_helper
    raise final_exc from source_exc
  File "/usr/local/lib/python3.10/site-packages/google/api_core/retry/retry_unary.py", line 144, in retry_target
    result = target()
  File "/usr/local/lib/python3.10/site-packages/google/cloud/bigquery/client.py", line 843, in _call_api
    return call()
  File "/usr/local/lib/python3.10/site-packages/google/cloud/_http/__init__.py", line 494, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 POST https://bigquery.googleapis.com/bigquery/v2/projects/toteat-dte-run/queries?prettyPrint=false: Syntax error: concatenated string literals must be separated by whitespace or comments at [3:38]

And the logs spit out this query:

SELECT sum(`product_quantity`) AS `Cantidad_de_Productos_d078c`
FROM `dataset`.`table` 
WHERE `restaurant_name` IN ('Armando''s') AND `status` != 'CANCELED' AND `canceled_line` = false AND `is_extra` = false AND `order_date` >= CAST('2025-03-27' AS DATE) AND `order_date` < CAST('2025-10-28' AS DATE)"

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Superset escapes apostrophes in filter values by doubling them (e.g., O'Donnell becomes O''Donnell), which is the standard SQL approach and should be accepted by BigQuery. However, the error message you’re seeing—concatenated string literals must be separated by whitespace or comments—suggests a syntax issue in how the query is constructed or formatted, not specifically with the escaping itself. This can happen if the generated SQL ends up with malformed string literals due to how values are concatenated or quoted in the filter clause, especially when special characters are involved cite.

Recent pull requests have addressed string escaping issues for other databases (like Databricks and Athena), and there have been changes to SQL formatting and BigQuery dialect handling in newer Superset versions, but there’s no direct evidence of a fix for this specific BigQuery apostrophe filter bug in 4.1.3 cite cite cite.

As a workaround, you can try using Jinja templating in your dataset SQL to defensively handle filter values, for example:

{% set values = filter_values('restaurant_name') %}
{% if values %}
  AND restaurant_name IN ({{ values | where_in }})
{% endif %}

This ensures that the filter clause is only included if there are values, and uses Superset’s built-in escaping logic. If you’re using virtual datasets, make sure ENABLE_TEMPLATE_PROCESSING is enabled.

If you need partial matching (e.g., LIKE instead of IN), note that Superset 4.x removed the Filter Box component that allowed for this, and the default filters only support exact matches. There’s ongoing discussion about making filter operators configurable, but for now, only exact matches are supported in the UI cite.

If this issue is blocking and you need a quick fix, upgrading to the latest Superset version may help if recent changes addressed related query formatting bugs. Otherwise, using Jinja templating as above or manually editing the generated SQL in SQL Lab are your best options for now.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}