You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by vi...@apache.org on 2026/10/12 17:58:06 UTC
(superset) 04/05: fix(databricks): enable equality operators for boolean filters (#36765) (#44355)
This is an automated email from the ASF dual-hosted git repository.
villebro pushed a commit to branch 7.0
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 1f58f34e90d5a100fce4b11e57f074b05d4bd1ea
Author: Francesco.Castaldi <in...@francescocastaldi.it>
AuthorDate: Sun Oct 11 23:34:16 2026 +0200
fix(databricks): enable equality operators for boolean filters (#36765) (#44355)
Co-authored-by: Francesco Castaldi <fr...@mapsgroup.it>
(cherry picked from commit 90dc7ac32899b50ac60ea0ec7489ae5a3059d43c)
---
superset/db_engine_specs/base.py | 19 +++
superset/db_engine_specs/databricks.py | 37 +++++-
superset/db_engine_specs/spark.py | 18 +++
superset/models/helpers.py | 5 +-
.../unit_tests/db_engine_specs/test_databricks.py | 133 ++++++++++++++++++++-
tests/unit_tests/models/helpers_test.py | 42 +++++++
6 files changed, 251 insertions(+), 3 deletions(-)
diff --git a/superset/db_engine_specs/base.py b/superset/db_engine_specs/base.py
index f1375edecd2..d2b5fa07eb1 100644
--- a/superset/db_engine_specs/base.py
+++ b/superset/db_engine_specs/base.py
@@ -1863,6 +1863,24 @@ class BaseEngineSpec: # pylint: disable=too-many-public-methods
"""
return None
+ @classmethod
+ def coerce_boolean_for_sql(cls, value: bool | None) -> Any:
+ """
+ Convert a Python boolean to the value used when building SQL filter
+ expressions for boolean columns.
+
+ Most engines accept Python booleans, but some drivers render them as
+ integer literals (0/1), which strict engines like Databricks reject
+ with a DATATYPE_MISMATCH error when the value is compared against a
+ boolean column (#36765). Override this in engine specs whose drivers
+ misrender Python booleans, returning an expression that compiles to an
+ explicit boolean literal (e.g. sqlalchemy.true()/false()).
+
+ :param value: Boolean value (True, False, or None)
+ :return: Value suitable for the engine's filter expressions
+ """
+ return value
+
@classmethod
def handle_boolean_filter(
cls, sqla_col: Any, op: str, value: bool
@@ -1879,6 +1897,7 @@ class BaseEngineSpec: # pylint: disable=too-many-public-methods
:param value: Boolean value (True or False)
:return: SQLAlchemy expression for the boolean filter
"""
+ value = cls.coerce_boolean_for_sql(value)
if cls.use_equality_for_boolean_filters:
return sqla_col == value
else:
diff --git a/superset/db_engine_specs/databricks.py b/superset/db_engine_specs/databricks.py
index c108b911ce0..ae2f0436d17 100644
--- a/superset/db_engine_specs/databricks.py
+++ b/superset/db_engine_specs/databricks.py
@@ -28,7 +28,7 @@ from flask import g
from flask_babel import gettext as __
from marshmallow import fields, Schema
from marshmallow.validate import Range
-from sqlalchemy import text, types
+from sqlalchemy import false, text, true, types
from sqlalchemy.engine.default import DefaultDialect
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy.engine.url import URL
@@ -251,6 +251,8 @@ time_grain_expressions: dict[str | None, str] = {
class DatabricksBaseEngineSpec(BaseEngineSpec):
+ """Base engine specification for Databricks flavors and connection methods."""
+
_time_grain_expressions = time_grain_expressions
# Databricks SQL is Spark SQL under the hood: identifiers are quoted with
@@ -258,6 +260,27 @@ class DatabricksBaseEngineSpec(BaseEngineSpec):
identifier_quote_start: str = "`"
identifier_quote_end: str = "`"
+ # Databricks SQL applies strict type checking: comparing a boolean column
+ # against an integer literal (or a bound Python boolean that the
+ # Hive/Databricks driver renders as 0/1) fails with
+ # DATATYPE_MISMATCH.DATA_DIFF_TYPES (see #36765). Enabling equality
+ # operators ensures boolean filters compile as 'col = true' / 'col = false'.
+ use_equality_for_boolean_filters: bool = True
+
+ @classmethod
+ def coerce_boolean_for_sql(cls, value: bool | None) -> Any:
+ """
+ Render Python booleans as explicit boolean keywords.
+
+ The Hive/Databricks SQLAlchemy dialects render Python booleans as
+ integer literals (0/1) in some binding paths, so emit sqlalchemy
+ true()/false() instead: they always compile to the TRUE/FALSE keywords
+ and never reach the driver as bind parameters (#36765).
+ """
+ if value is None:
+ return None
+ return true() if value else false()
+
@classmethod
def convert_dttm(
cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None = None
@@ -1109,6 +1132,18 @@ class DatabricksHiveEngineSpec(HiveEngineSpec):
# consolidates all Databricks connection methods. This spec exists for
# backwards compatibility with Interactive Cluster connections.
+ # Databricks Interactive Clusters apply the same strict type checking as
+ # Databricks SQL, so boolean filters need equality against boolean keywords.
+ use_equality_for_boolean_filters: bool = True
+
+ @classmethod
+ def coerce_boolean_for_sql(cls, value: bool | None) -> Any:
+ # Same rationale as DatabricksBaseEngineSpec: the Hive driver renders
+ # Python booleans as 0/1 in some binding paths (#36765).
+ if value is None:
+ return None
+ return true() if value else false()
+
_show_functions_column = "function"
_time_grain_expressions = time_grain_expressions
diff --git a/superset/db_engine_specs/spark.py b/superset/db_engine_specs/spark.py
index 5c5360a718d..1fa46b359c5 100644
--- a/superset/db_engine_specs/spark.py
+++ b/superset/db_engine_specs/spark.py
@@ -16,6 +16,9 @@
# under the License.
from __future__ import annotations
+from typing import Any
+
+from sqlalchemy import false, true
from sqlalchemy.dialects import registry
from superset.constants import TimeGrain
@@ -42,11 +45,26 @@ time_grain_expressions: dict[str | None, str] = {
class SparkEngineSpec(HiveEngineSpec):
+ """Engine specification for Apache Spark SQL."""
+
engine = "spark"
registry.register("spark", "pyhive.sqlalchemy_hive", "HiveDialect")
_time_grain_expressions = time_grain_expressions
engine_name = "Apache Spark SQL"
+ # Spark SQL shares syntax rules with Databricks SQL; equality comparisons
+ # are required for boolean filters to avoid type mismatch errors (see #36765).
+ use_equality_for_boolean_filters: bool = True
+
+ @classmethod
+ def coerce_boolean_for_sql(cls, value: bool | None) -> Any:
+ # The pyhive driver renders Python booleans as 0/1 in some binding
+ # paths, and Spark SQL applies the same strict type checking as
+ # Databricks SQL (#36765).
+ if value is None:
+ return None
+ return true() if value else false()
+
metadata = {
"description": "Apache Spark SQL is a module for structured data processing.",
"logo": "apache-spark.png",
diff --git a/superset/models/helpers.py b/superset/models/helpers.py
index 50be2be0dce..f5e06be6d4a 100644
--- a/superset/models/helpers.py
+++ b/superset/models/helpers.py
@@ -4067,7 +4067,10 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
elif value is not None:
value = handle_temporal_value(value)
if target_generic_type == utils.GenericDataType.BOOLEAN:
- return utils.cast_to_boolean(value)
+ boolean_value = utils.cast_to_boolean(value)
+ if db_engine_spec is not None:
+ return db_engine_spec.coerce_boolean_for_sql(boolean_value)
+ return boolean_value
return value
if isinstance(values, (list, tuple)):
diff --git a/tests/unit_tests/db_engine_specs/test_databricks.py b/tests/unit_tests/db_engine_specs/test_databricks.py
index fb06011bf8f..4614f0a10d0 100644
--- a/tests/unit_tests/db_engine_specs/test_databricks.py
+++ b/tests/unit_tests/db_engine_specs/test_databricks.py
@@ -26,18 +26,30 @@ from urllib.parse import parse_qs, urlparse
import pytest
from packaging.requirements import Requirement
from pytest_mock import MockerFixture
-from sqlalchemy import __version__ as sqlalchemy_version, create_engine, text
+from sqlalchemy import (
+ __version__ as sqlalchemy_version,
+ Boolean,
+ Column,
+ create_engine,
+ literal_column,
+ text,
+)
from sqlalchemy.engine.url import make_url
+from sqlalchemy.sql.elements import ColumnElement
from superset.db_engine_specs.base import BaseEngineSpec, OAuth2State
from superset.db_engine_specs.databricks import (
+ DatabricksBaseEngineSpec,
+ DatabricksHiveEngineSpec,
DatabricksNativeEngineSpec,
DatabricksPythonConnectorEngineSpec,
)
+from superset.db_engine_specs.spark import SparkEngineSpec
from superset.errors import ErrorLevel, SupersetError, SupersetErrorType
from superset.exceptions import OAuth2Error, OAuth2RedirectError
from superset.superset_typing import OAuth2ClientConfig
from superset.utils import json
+from superset.utils.core import FilterOperator
from superset.utils.oauth2 import decode_oauth2_state
from tests.unit_tests.db_engine_specs.utils import assert_convert_dttm
from tests.unit_tests.fixtures.common import dttm # noqa: F401
@@ -1468,3 +1480,122 @@ def test_monkeypatch_dialect_preserves_hive_databricks_escaping(
)
== r"'O\'Hara'"
)
+
+
+def test_use_equality_for_boolean_filters_property() -> None:
+ """
+ Test that Databricks engine specs enable use_equality_for_boolean_filters.
+ Databricks SQL applies strict type checking: comparing a boolean column
+ against an integer or a driver-rendered boolean bind fails with
+ DATATYPE_MISMATCH (see #36765), so boolean filters use equality against
+ explicit boolean keywords.
+ """
+ assert DatabricksBaseEngineSpec.use_equality_for_boolean_filters is True
+ assert DatabricksNativeEngineSpec.use_equality_for_boolean_filters is True
+ assert DatabricksPythonConnectorEngineSpec.use_equality_for_boolean_filters is True
+ assert DatabricksHiveEngineSpec.use_equality_for_boolean_filters is True
+ assert SparkEngineSpec.use_equality_for_boolean_filters is True
+
+
+@pytest.mark.parametrize(
+ "engine_spec",
+ [
+ DatabricksBaseEngineSpec,
+ DatabricksNativeEngineSpec,
+ DatabricksPythonConnectorEngineSpec,
+ DatabricksHiveEngineSpec,
+ SparkEngineSpec,
+ ],
+ ids=[
+ "databricks_base",
+ "databricks_native",
+ "databricks_python_connector",
+ "databricks_hive",
+ "spark",
+ ],
+)
+def test_coerce_boolean_for_sql_emits_boolean_keywords(
+ engine_spec: type[BaseEngineSpec],
+) -> None:
+ """
+ Test that boolean filter values are coerced to SQLAlchemy true()/false()
+ expressions on every Databricks and Spark spec, so the compiled SQL always
+ contains the TRUE/FALSE keywords and the values never reach the driver as
+ bind parameters (which pyhive renders as 0/1, see #36765).
+ """
+ true_value = engine_spec.coerce_boolean_for_sql(True)
+ assert isinstance(true_value, ColumnElement)
+ assert str(true_value.compile(compile_kwargs={"literal_binds": True})) == "true"
+
+ false_value = engine_spec.coerce_boolean_for_sql(False)
+ assert isinstance(false_value, ColumnElement)
+ assert str(false_value.compile(compile_kwargs={"literal_binds": True})) == "false"
+
+ assert engine_spec.coerce_boolean_for_sql(None) is None
+
+
+@pytest.mark.parametrize(
+ "engine_spec",
+ [
+ DatabricksBaseEngineSpec,
+ DatabricksNativeEngineSpec,
+ DatabricksPythonConnectorEngineSpec,
+ DatabricksHiveEngineSpec,
+ SparkEngineSpec,
+ ],
+ ids=[
+ "databricks_base",
+ "databricks_native",
+ "databricks_python_connector",
+ "databricks_hive",
+ "spark",
+ ],
+)
+@pytest.mark.parametrize(
+ "filter_operator,expected",
+ [
+ (FilterOperator.IS_TRUE, "is_test_user = true"),
+ (FilterOperator.IS_FALSE, "is_test_user = false"),
+ ],
+)
+def test_handle_boolean_filter_compiles_boolean_keywords(
+ engine_spec: type[BaseEngineSpec],
+ filter_operator: FilterOperator,
+ expected: str,
+) -> None:
+ """
+ Test that handle_boolean_filter produces an equality comparison against an
+ explicit boolean keyword, with no bind parameters left for the driver to
+ render as integers.
+ """
+ bool_col = Column("is_test_user", Boolean)
+ result = engine_spec.handle_boolean_filter(
+ bool_col,
+ filter_operator,
+ filter_operator == FilterOperator.IS_TRUE,
+ )
+ compiled = result.compile()
+ assert str(compiled) == expected
+ assert compiled.params == {}
+
+
+def test_handle_boolean_filter_computed_column_compilation() -> None:
+ """
+ Test that handle_boolean_filter compiles properly on computed boolean
+ expressions.
+ """
+ computed_col = literal_column("(total_amount > 100)")
+ for engine_spec in (
+ DatabricksBaseEngineSpec,
+ DatabricksNativeEngineSpec,
+ DatabricksPythonConnectorEngineSpec,
+ DatabricksHiveEngineSpec,
+ SparkEngineSpec,
+ ):
+ result = engine_spec.handle_boolean_filter(
+ computed_col, FilterOperator.IS_TRUE, True
+ )
+ assert (
+ str(result.compile(compile_kwargs={"literal_binds": True}))
+ == "(total_amount > 100) = true"
+ )
diff --git a/tests/unit_tests/models/helpers_test.py b/tests/unit_tests/models/helpers_test.py
index 448746d894e..a3e8bc56b31 100644
--- a/tests/unit_tests/models/helpers_test.py
+++ b/tests/unit_tests/models/helpers_test.py
@@ -5132,6 +5132,48 @@ def test_temporal_epoch_string_filter_list_is_coerced_for_clickhouse() -> None:
assert str(values[0]) == "toDate('2026-04-27')"
+@pytest.mark.parametrize(
+ "operator",
+ [FilterOperator.IN, FilterOperator.NOT_IN],
+)
+@pytest.mark.parametrize(
+ "values",
+ [[0, 1], [False, True], ["false", "true"]],
+)
+def test_boolean_filter_values_coerced_for_databricks(
+ operator: FilterOperator,
+ values: list[Any],
+) -> None:
+ """
+ Native dashboard filters on boolean columns build `IN` clauses through
+ `filter_values_handler` (#36765). Databricks rejects `col IN (0)` with
+ DATATYPE_MISMATCH, and the Hive/Databricks drivers render bound Python
+ booleans as integers, so values must coerce to boolean keywords.
+ """
+ from sqlalchemy import Boolean, Column
+
+ from superset.db_engine_specs.databricks import DatabricksBaseEngineSpec
+ from superset.db_engine_specs.spark import SparkEngineSpec
+ from superset.models.helpers import ExploreMixin
+
+ for db_engine_spec in (DatabricksBaseEngineSpec, SparkEngineSpec):
+ coerced = ExploreMixin.filter_values_handler(
+ values=values,
+ operator=operator,
+ target_generic_type=GenericDataType.BOOLEAN,
+ is_list_target=True,
+ db_engine_spec=db_engine_spec,
+ )
+ assert isinstance(coerced, list)
+ assert len(coerced) == 2
+ assert all(isinstance(value, ColumnElement) for value in coerced)
+ # the coerced values compile to boolean keywords and never reach the
+ # driver as bind parameters (which pyhive renders as 0/1)
+ compiled = Column("is_test_user", Boolean).in_(coerced).compile()
+ assert str(compiled) == "is_test_user IN (false, true)"
+ assert compiled.params == {}
+
+
@pytest.mark.parametrize(
"target_type,expected",
[