You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ma...@apache.org on 2025/07/17 00:43:35 UTC

(superset) branch fix_bool_snowflake created (now 1610c6f298)

This is an automated email from the ASF dual-hosted git repository.

maximebeauchemin pushed a change to branch fix_bool_snowflake
in repository https://gitbox.apache.org/repos/asf/superset.git


      at 1610c6f298 fix: proper handling of boolean filters with snowflake

This branch includes the following new commits:

     new 1610c6f298 fix: proper handling of boolean filters with snowflake

The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.



(superset) 01/01: fix: proper handling of boolean filters with snowflake

Posted by ma...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

maximebeauchemin pushed a commit to branch fix_bool_snowflake
in repository https://gitbox.apache.org/repos/asf/superset.git

commit 1610c6f298e88d03eb27125ec82102409c515914
Author: Maxime Beauchemin <ma...@gmail.com>
AuthorDate: Wed Jul 16 17:43:04 2025 -0700

    fix: proper handling of boolean filters with snowflake
---
 superset/db_engine_specs/base.py                   | 68 ++++++++++++++++++++++
 superset/db_engine_specs/snowflake.py              |  3 +
 superset/models/helpers.py                         | 42 +++++++------
 tests/unit_tests/db_engine_specs/test_snowflake.py | 38 ++++++++++++
 4 files changed, 133 insertions(+), 18 deletions(-)

diff --git a/superset/db_engine_specs/base.py b/superset/db_engine_specs/base.py
index e3ffdd335d..ec314b94c5 100644
--- a/superset/db_engine_specs/base.py
+++ b/superset/db_engine_specs/base.py
@@ -377,6 +377,10 @@ class BaseEngineSpec:  # pylint: disable=too-many-public-methods
     disallow_uri_query_params: dict[str, set[str]] = {}
     # A Dict of query parameters that will always be used on every connection
     # by driver name
+
+    # Whether to use equality operators (= true/false) instead of IS operators
+    # for boolean filters. Some databases like Snowflake don't support IS true/false
+    use_equality_for_boolean_filters = False
     enforce_uri_query_params: dict[str, dict[str, Any]] = {}
 
     force_column_alias_quotes = False
@@ -1202,6 +1206,70 @@ class BaseEngineSpec:  # pylint: disable=too-many-public-methods
         """
         return None
 
+    @classmethod
+    def handle_boolean_filter(cls, sqla_col: Any, op: str, value: bool) -> Any:
+        """
+        Handle boolean filter operations with engine-specific logic.
+
+        By default, uses SQLAlchemy's IS operator (column IS true/false).
+        Engines that don't support IS for boolean values can override
+        use_equality_for_boolean_filters to use equality operators instead.
+
+        :param sqla_col: SQLAlchemy column element
+        :param op: Filter operator (IS_TRUE or IS_FALSE)
+        :param value: Boolean value (True or False)
+        :return: SQLAlchemy expression for the boolean filter
+        """
+        if cls.use_equality_for_boolean_filters:
+            return sqla_col == value
+        else:
+            return sqla_col.is_(value)
+
+    @classmethod
+    def handle_null_filter(cls, sqla_col: Any, op: str) -> Any:
+        """
+        Handle null/not null filter operations.
+
+        :param sqla_col: SQLAlchemy column element
+        :param op: Filter operator (IS_NULL or IS_NOT_NULL)
+        :return: SQLAlchemy expression for the null filter
+        """
+        from superset.utils import core as utils
+
+        if op == utils.FilterOperator.IS_NULL.value:
+            return sqla_col.is_(None)
+        elif op == utils.FilterOperator.IS_NOT_NULL.value:
+            return sqla_col.isnot(None)
+        else:
+            raise ValueError(f"Invalid null filter operator: {op}")
+
+    @classmethod
+    def handle_comparison_filter(cls, sqla_col: Any, op: str, value: Any) -> Any:
+        """
+        Handle comparison filter operations (=, !=, >, <, >=, <=).
+
+        :param sqla_col: SQLAlchemy column element
+        :param op: Filter operator
+        :param value: Filter value
+        :return: SQLAlchemy expression for the comparison filter
+        """
+        from superset.utils import core as utils
+
+        if op == utils.FilterOperator.EQUALS.value:
+            return sqla_col == value
+        elif op == utils.FilterOperator.NOT_EQUALS.value:
+            return sqla_col != value
+        elif op == utils.FilterOperator.GREATER_THAN.value:
+            return sqla_col > value
+        elif op == utils.FilterOperator.LESS_THAN.value:
+            return sqla_col < value
+        elif op == utils.FilterOperator.GREATER_THAN_OR_EQUALS.value:
+            return sqla_col >= value
+        elif op == utils.FilterOperator.LESS_THAN_OR_EQUALS.value:
+            return sqla_col <= value
+        else:
+            raise ValueError(f"Invalid comparison filter operator: {op}")
+
     @classmethod
     def handle_cursor(cls, cursor: Any, query: Query) -> None:
         """Handle a live cursor between the execute and fetchall calls
diff --git a/superset/db_engine_specs/snowflake.py b/superset/db_engine_specs/snowflake.py
index 7e353f006d..5b064c22c7 100644
--- a/superset/db_engine_specs/snowflake.py
+++ b/superset/db_engine_specs/snowflake.py
@@ -83,6 +83,9 @@ class SnowflakeEngineSpec(PostgresBaseEngineSpec):
     force_column_alias_quotes = True
     max_column_name_length = 256
 
+    # Snowflake doesn't support IS true/false syntax, use = true/false instead
+    use_equality_for_boolean_filters = True
+
     parameters_schema = SnowflakeParametersSchema()
     default_driver = "snowflake"
     sqlalchemy_uri_placeholder = "snowflake://"
diff --git a/superset/models/helpers.py b/superset/models/helpers.py
index 930b84aa34..74a95c717a 100644
--- a/superset/models/helpers.py
+++ b/superset/models/helpers.py
@@ -1853,14 +1853,21 @@ class ExploreMixin:  # pylint: disable=too-many-public-methods
                     if op == utils.FilterOperator.NOT_IN.value:
                         cond = ~cond
                     where_clause_and.append(cond)
-                elif op == utils.FilterOperator.IS_NULL.value:
-                    where_clause_and.append(sqla_col.is_(None))
-                elif op == utils.FilterOperator.IS_NOT_NULL.value:
-                    where_clause_and.append(sqla_col.isnot(None))
+                elif op in {
+                    utils.FilterOperator.IS_NULL.value,
+                    utils.FilterOperator.IS_NOT_NULL.value,
+                }:
+                    where_clause_and.append(
+                        db_engine_spec.handle_null_filter(sqla_col, op)
+                    )
                 elif op == utils.FilterOperator.IS_TRUE.value:
-                    where_clause_and.append(sqla_col.is_(True))
+                    where_clause_and.append(
+                        db_engine_spec.handle_boolean_filter(sqla_col, op, True)
+                    )
                 elif op == utils.FilterOperator.IS_FALSE.value:
-                    where_clause_and.append(sqla_col.is_(False))
+                    where_clause_and.append(
+                        db_engine_spec.handle_boolean_filter(sqla_col, op, False)
+                    )
                 else:
                     if (
                         op
@@ -1876,18 +1883,17 @@ class ExploreMixin:  # pylint: disable=too-many-public-methods
                                 "with comparison operators"
                             )
                         )
-                    if op == utils.FilterOperator.EQUALS.value:
-                        where_clause_and.append(sqla_col == eq)
-                    elif op == utils.FilterOperator.NOT_EQUALS.value:
-                        where_clause_and.append(sqla_col != eq)
-                    elif op == utils.FilterOperator.GREATER_THAN.value:
-                        where_clause_and.append(sqla_col > eq)
-                    elif op == utils.FilterOperator.LESS_THAN.value:
-                        where_clause_and.append(sqla_col < eq)
-                    elif op == utils.FilterOperator.GREATER_THAN_OR_EQUALS.value:
-                        where_clause_and.append(sqla_col >= eq)
-                    elif op == utils.FilterOperator.LESS_THAN_OR_EQUALS.value:
-                        where_clause_and.append(sqla_col <= eq)
+                    if op in {
+                        utils.FilterOperator.EQUALS.value,
+                        utils.FilterOperator.NOT_EQUALS.value,
+                        utils.FilterOperator.GREATER_THAN.value,
+                        utils.FilterOperator.LESS_THAN.value,
+                        utils.FilterOperator.GREATER_THAN_OR_EQUALS.value,
+                        utils.FilterOperator.LESS_THAN_OR_EQUALS.value,
+                    }:
+                        where_clause_and.append(
+                            db_engine_spec.handle_comparison_filter(sqla_col, op, eq)
+                        )
                     elif op in {
                         utils.FilterOperator.ILIKE.value,
                         utils.FilterOperator.LIKE.value,
diff --git a/tests/unit_tests/db_engine_specs/test_snowflake.py b/tests/unit_tests/db_engine_specs/test_snowflake.py
index 68c611c5c0..1b576a319f 100644
--- a/tests/unit_tests/db_engine_specs/test_snowflake.py
+++ b/tests/unit_tests/db_engine_specs/test_snowflake.py
@@ -352,6 +352,44 @@ def test_mask_encrypted_extra_no_fields() -> None:
     )
 
 
+def test_handle_boolean_filter() -> None:
+    """
+    Test that Snowflake uses equality operators for boolean filters instead of IS.
+    """
+    from sqlalchemy import Boolean, Column
+
+    from superset.db_engine_specs.snowflake import SnowflakeEngineSpec
+
+    # Create a mock SQLAlchemy column
+    bool_col = Column("test_col", Boolean)
+
+    # Test IS_TRUE filter
+    result_true = SnowflakeEngineSpec.handle_boolean_filter(bool_col, "IS TRUE", True)
+    # The result should be a equality comparison, not an IS comparison
+    assert (
+        str(result_true.compile(compile_kwargs={"literal_binds": True}))
+        == "test_col = true"
+    )
+
+    # Test IS_FALSE filter
+    result_false = SnowflakeEngineSpec.handle_boolean_filter(
+        bool_col, "IS FALSE", False
+    )
+    assert (
+        str(result_false.compile(compile_kwargs={"literal_binds": True}))
+        == "test_col = false"
+    )
+
+
+def test_use_equality_for_boolean_filters_property() -> None:
+    """
+    Test that Snowflake has the use_equality_for_boolean_filters property set to True.
+    """
+    from superset.db_engine_specs.snowflake import SnowflakeEngineSpec
+
+    assert SnowflakeEngineSpec.use_equality_for_boolean_filters is True
+
+
 def test_unmask_encrypted_extra() -> None:
     """
     Test that the private keys can be reused from the previous `encrypted_extra`.