[superset] branch master updated: fix: boolean type into SQL 'in' operator (#16107)

[yo...@apache.org]

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

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


The following commit(s) were added to refs/heads/master by this push:
     new bb1d8fe  fix: boolean type into SQL 'in' operator (#16107)
bb1d8fe is described below

commit bb1d8fe4ef505cbefe353a234f7cb8e1bc51a963
Author: Yongjie Zhao <yo...@gmail.com>
AuthorDate: Tue Aug 10 12:21:46 2021 +0100

    fix: boolean type into SQL 'in' operator (#16107)
    
    * fix: boolean type into SQL 'in' operator
    
    * fix ut
    
    * fix ut again
    
    * update url
    
    * remove blank line
---
 .../src/datasource/DatasourceEditor.jsx            |  2 +-
 superset/connectors/base/models.py                 |  2 ++
 superset/utils/core.py                             | 29 ++++++++++++++++
 tests/integration_tests/sqla_models_tests.py       | 39 ++++++++++++++++++++++
 4 files changed, 71 insertions(+), 1 deletion(-)

diff --git a/superset-frontend/src/datasource/DatasourceEditor.jsx b/superset-frontend/src/datasource/DatasourceEditor.jsx
index dfd323e..89b8b7a 100644
--- a/superset-frontend/src/datasource/DatasourceEditor.jsx
+++ b/superset-frontend/src/datasource/DatasourceEditor.jsx
@@ -112,7 +112,7 @@ const ColumnButtonWrapper = styled.div`
 const checkboxGenerator = (d, onChange) => (
   <CheckboxControl value={d} onChange={onChange} />
 );
-const DATA_TYPES = ['STRING', 'NUMERIC', 'DATETIME'];
+const DATA_TYPES = ['STRING', 'NUMERIC', 'DATETIME', 'BOOLEAN'];
 
 const DATASOURCE_TYPES_ARR = [
   { key: 'physical', label: t('Physical (table or view)') },
diff --git a/superset/connectors/base/models.py b/superset/connectors/base/models.py
index f332ce8..844dc48 100644
--- a/superset/connectors/base/models.py
+++ b/superset/connectors/base/models.py
@@ -374,6 +374,8 @@ class BaseDatasource(
                     return None
                 if value == "<empty string>":
                     return ""
+            if target_column_type == utils.GenericDataType.BOOLEAN:
+                return utils.cast_to_boolean(value)
             return value
 
         if isinstance(values, (list, tuple)):
diff --git a/superset/utils/core.py b/superset/utils/core.py
index 9739287..803bafc 100644
--- a/superset/utils/core.py
+++ b/superset/utils/core.py
@@ -423,6 +423,35 @@ def cast_to_num(value: Optional[Union[float, int, str]]) -> Optional[Union[float
         return None
 
 
+def cast_to_boolean(value: Any) -> bool:
+    """Casts a value to an int/float
+
+    >>> cast_to_boolean(1)
+    True
+    >>> cast_to_boolean(0)
+    False
+    >>> cast_to_boolean(0.5)
+    True
+    >>> cast_to_boolean('true')
+    True
+    >>> cast_to_boolean('false')
+    False
+    >>> cast_to_boolean('False')
+    False
+    >>> cast_to_boolean(None)
+    False
+
+    :param value: value to be converted to boolean representation
+    :returns: value cast to `bool`. when value is 'true' or value that are not 0
+              converte into True
+    """
+    if isinstance(value, (int, float)):
+        return value != 0
+    if isinstance(value, str):
+        return value.strip().lower() == "true"
+    return False
+
+
 def list_minus(l: List[Any], minus: List[Any]) -> List[Any]:
     """Returns l without what is in minus
 
diff --git a/tests/integration_tests/sqla_models_tests.py b/tests/integration_tests/sqla_models_tests.py
index ed1358a..f17cedb 100644
--- a/tests/integration_tests/sqla_models_tests.py
+++ b/tests/integration_tests/sqla_models_tests.py
@@ -20,6 +20,8 @@ from typing import Any, Dict, NamedTuple, List, Pattern, Tuple, Union
 from unittest.mock import patch
 import pytest
 
+import sqlalchemy as sa
+
 from superset import db
 from superset.connectors.sqla.models import SqlaTable, TableColumn
 from superset.db_engine_specs.bigquery import BigQueryEngineSpec
@@ -264,6 +266,43 @@ class TestDatabaseModel(SupersetTestCase):
             else:
                 self.assertIn(filter_.expected, sql)
 
+    @pytest.mark.usefixtures("load_birth_names_dashboard_with_slices")
+    def test_boolean_type_where_operators(self):
+        table = self.get_table(name="birth_names")
+        db.session.add(
+            TableColumn(
+                column_name="boolean_gender",
+                expression="case when gender = 'boy' then True else False end",
+                type="BOOLEAN",
+                table=table,
+            )
+        )
+        query_obj = {
+            "granularity": None,
+            "from_dttm": None,
+            "to_dttm": None,
+            "groupby": ["boolean_gender"],
+            "metrics": ["count"],
+            "is_timeseries": False,
+            "filter": [
+                {
+                    "col": "boolean_gender",
+                    "op": FilterOperator.IN,
+                    "val": ["true", "false"],
+                }
+            ],
+            "extras": {},
+        }
+        sqla_query = table.get_sqla_query(**query_obj)
+        sql = table.database.compile_sqla_query(sqla_query.sqla_query)
+        dialect = table.database.get_dialect()
+        operand = "(true, false)"
+        # override native_boolean=False behavior in MySQLCompiler
+        # https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py
+        if not dialect.supports_native_boolean and dialect.name != "mysql":
+            operand = "(1, 0)"
+        self.assertIn(f"IN {operand}", sql)
+
     def test_incorrect_jinja_syntax_raises_correct_exception(self):
         query_obj = {
             "granularity": None,