Filter on bool column not working properly in SQL Lab -> Explore

[dolorez]

When a query in SQL Lab is executed and a filter is applied to a boolean column, it errors out on datatype comparison (boolean vs string).
Note - the behavior

Expected results

The filter should work - the generated query should not enclose the field value in single quotes.

Actual results

The query generated encloses the filter value in single quotes so the where clause generated looks (some_bool_flag = 'true') and this that in turn generates type mismatch

How to reproduce the bug

1. Go to SQL Lab -> SQL Editor
2. Write a query that returns a boolean column
3. Execute the query
4. Click on Explore
5. Scroll down to filters
6. Add a filter on a boolean column and make sure you stay in Simple (not Custom SQL)
7. Use equals and set filter value to "true" (without double quotes)
8. Execute the query to see the error messge
9. Click on menu -> View query to inspect that the filter value was enclosed in single quotes

Environment

(please complete the following information):

• superset version: `superset version
  0.34.1
• python version: python --version
  Python 3.6.9
• node.js version: node -v
  v12.18.0
• npm version: npm -v
  6.14.4

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
  I have no control over the environment :(
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

When the filter is entered using the Custom SQL instead of Simple, it works fine

[issue-label-bot]

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.95. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[rumbin]

Could you add the type of database, please?

[dolorez]

It's presto and that particular query goes to hive

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[bkyryliuk]

issue still persists on presto database. It's not possible to replicate for mysql as mysql interpret boolean as tinyint.

Selector looks like this:

Clicking on it results in:

Sqllab correctly sees the type of the column:

@etr2460, @john-bodley - curious if you have similar issue @ airbnb.

[eugeniamz]

I see the same issue with Vertica DB. It does not show the option but if you choose the second one you have a TRUE but the first is empty. I work around by going to CUSTOM SQL but this should be resolved.

[mistercrunch]

Maybe we need conditional operators based on data type. BOOLEAN should only offer IS NULL IS TRUE and IS FALSE

[john-bodley]

@junlincc @zuzana-vej I wonder if we should prioritize this, i.e., not being able to filter on a boolean column using IS TRUE, IS FALSE etc. seems fairly problematic.