Skip to content

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

Open
@srinify

Description

@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!
  1. Use the Custom SQL tab and manually specify = True.

Image

Image

Image

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions