Filtering on Boolean columns has a limitation in Snowflake (and possibly other databases)

[srinify]

Bug description

In chart creator, I can't apply a filter on a Boolean column (e.g. to just return rows where the column is True) when using Snowflake.

The generated Snowflake SQL isn't valid, because IS True isn't valid in Snowflake. This is the generated SQL:

SELECT COUNT(*) AS "count" 
FROM flights.fct_flight_stats 
WHERE "IS_CANCELLED" IS true
 LIMIT 5000

IS can only be used to evaluate NULL-ness in Snowflake, not for filtering values (read more here and here). I suspect this is also true for some other databases, but I haven't researched thoroughly.

The alternative would be to have menu options for = True and = False, for Boolean columns.

Version

Preset Cloud // Apache Superset 4.2.0.5

Workarounds

1. The IN option can be used in combination with and limiting the values to True.

• However, if the generated SQL errored previously, the IN option mysteriously disappears in the UI for this Boolean column. I suspect this is because Superset isn't aware of valid values to fetch that would work for IN. I'm happy to open a separate issue for this if the community would like!

2. Use the Custom SQL tab and manually specify = True.

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

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]

Hello @srinify! I'm Dosu, here to help you solve bugs, answer questions, and guide you in becoming a contributor. I'm on standby while you wait for a human maintainer. How can I assist you today?

The issue you're encountering is due to the way Superset generates SQL queries for Boolean columns, which isn't compatible with Snowflake's SQL syntax. Superset uses IS True for filtering Boolean columns, but Snowflake requires = True instead ^[1].

To address this, you can use the SQL_QUERY_MUTATOR configuration in Superset to customize the SQL query before execution. This function allows you to modify the SQL query, potentially replacing IS True with = True for Snowflake. Here's a basic example of how you might implement this:

def SQL_QUERY_MUTATOR(sql, security_manager=security_manager, database=database):
    if 'IS True' in sql:
        sql = sql.replace('IS True', '= True')
    return sql

This function checks for the presence of IS True in the SQL query and replaces it with = True, making it compatible with Snowflake ^[2].

Alternatively, you can manually use the Custom SQL tab to specify the correct syntax (= True) for your queries.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}