Superset - Dremio : Boolean filter becomes 0 or 1

1

I have a dashboard with a boolean filter is_participant_in_watchlist

I am using the field as a dashboard filter for all charts.

When the filter is applied to the charts, the boolean filter’s values becomes 0 and 1.

This caused the below error

Error: Flight returned invalid argument error, with message: Values passed to IN operator must have compatible types
startLine 7
startColumn 7
endLine 8
endColumn 40
SQL Query SELECT DATE_TRUNC('day', as_of_date) AS as_of_date,
       sehk_code AS sehk_code,
       participant_id AS participant_id,
       max(shareholding_amount) AS "MAX(shareholding_amount)"
FROM "l1-hk-trading"."flat"."flat_hk_stock_shareholding_history"
WHERE sehk_code IN ('00001')
  AND is_participant_in_watchlist IN (0,
                                      1)
  AND as_of_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
  AND as_of_date < TO_DATE('2024-05-15', 'YYYY-MM-DD')
GROUP BY DATE_TRUNC('day', as_of_date),
         sehk_code,
         participant_id
ORDER BY "MAX(shareholding_amount)" DESC
LIMIT 10000

==========
Screenshots

  • Boolean filter NOT applied

image
image1642×893 88.2 KB

  • Boolean filter applied

image
image1642×893 85.3 KB

=========

My current workaround is to convery my boolean fields to 0 or 1 just for dashboarding purposes on superset

2

I think what you mean here is that the filter values should be True or False … however why is the filter in place here anyway since having IN (true, false) basically negates the filter doesnt it?

3

Hi @mark_c

That’s right.

The SQL sample is just an example.

The actual use case would of course NOT have both true and false in the filter.

All I wanted to show is that True gets translated to 1, and False gets translated to 0 when using superset against Dremio

Thank you!

4

Hi @Ken so my colleague @Simon_Ashley ran some tests with superset and what we found out was that the default IN filter seems to be the one that comes up when you run a query
image
If you choose the is:true or is:false then the SQL sent through to dremio is valid. You can also use the custom SQL and create a valid query
image (1)
as you can see the valid values are true|false or '0'|'1'

So the problem is really how superset is forming the SQL (presenting those as INT values) and the error you see in Dremio is valid.

2 Likes
5

Hi mark,

THank you for following up on the issue.

I now resort to using STRING (e.g. ‘TRUE’, ‘FALSE’) when it comes to filtering in superset.

It should be fine.

THank you so much for looking into the issue :pray: