Apache Superset Confusion between python False/True and sql false/true in dashboard filters

Bug description

I'm running Superset in kubernetes via helm chart.

I have a postgres table with such a definition

CREATE TABLE example_table (
    id BIGINT primary key,
    client_id bigint,
    full_key TEXT,
    final_value JSONB,
    random_bool boolean
);

I've added values there

id | client_id | full_key | final_value | random_bool 
----+-----------+----------+-------------+-------------
  1 |         1 | animal   | false       | t
  2 |         1 | bird     | false       | t
  3 |         1 | fish     | true        | t
  4 |         1 | sunny    | false       | t
  5 |         1 | name     | "jane"      | t
  6 |         2 | animal   | true        | f
  7 |         2 | bird     | false       | t
  9 |         2 | fish     | true        | t
 10 |         2 | sunny    | true        | f
 11 |         2 | name     | "tom"       | f
....

Then I'm looking in sql lab and see that final_value is converted to Python boolean

I'm looking at the dashboard and seeing False/True in final_value column

If I select another filter it'll show false/true not False/True in final_value column

For now the value is Jsonb and it won't work with the filter so I'm adding to the dataset (to be able to use IN and LIKE )

select * from example_table
where 1=1

{% for filter in get_filters('final_value', remove_filter=True) %}
  {% if filter.get('op') == 'IN' %}
    {% set filter_value = filter.get('val')[0] %}
    {% if filter_value is boolean %}
      AND final_value::text = '{{ 'true' if filter_value else 'false' }}'
    {% else %}
      AND final_value::text LIKE '%' || '{{ filter_value }}' || '%'
    {% endif %}
  {% else %}
    {% set filter_value = filter.get('val')[0] %}
    {% if filter_value is boolean %}
      AND final_value::text = '{{ 'true' if filter_value else 'false' }}'
    {% else %}
      AND final_value::text LIKE '%' || '{{ filter_value }}' || '%'
    {% endif %}
  {% endif %}
{% endfor %}

How to reproduce the bug

go to the dataset, choose False in and see No data returned, because there is not False/True in this column

If I type for like I can type true/false and it shows the stuff correctly

I know if I do my dataset as select id, client_id, animal, final_value::text from example_table I'll get all text values, but I wonder whats happening with the above example between False/True and false/true .

Screenshots/recordings

No response

Superset version

4.0.2

Python version

3.10

Node version

I don't know

Browser

Chrome

Additional context

No response

Checklist

• [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
• [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
• [X] 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.

Comment From: dosubot[bot]

Hey there, @kraftaa! I'm here to help you with any bugs, questions, or contributions you have for Superset. Let's work together to solve the issue you're facing.

To resolve the issue of False/True vs false/true in the final_value column when filtering data in Superset, you can use the getDataRecordFormatter function to ensure consistent boolean string formatting. This function converts various representations of boolean values to a standardized format (TRUE_STRING or FALSE_STRING).

Here is an example of how you can use getDataRecordFormatter to handle this:

import { getDataRecordFormatter, GenericDataType } from 'src/filters/utils';
import { TRUE_STRING, FALSE_STRING } from 'src/utils/common';

const formatter = getDataRecordFormatter();

// Example usage:
const formattedValue1 = formatter('true', GenericDataType.Boolean); // returns TRUE_STRING
const formattedValue2 = formatter('False', GenericDataType.Boolean); // returns FALSE_STRING
const formattedValue3 = formatter(true, GenericDataType.Boolean); // returns TRUE_STRING
const formattedValue4 = formatter(false, GenericDataType.Boolean); // returns FALSE_STRING

console.log(formattedValue1); // "true"
console.log(formattedValue2); // "false"
console.log(formattedValue3); // "true"
console.log(formattedValue4); // "false"

By using this formatter, you can ensure that all boolean values in the final_value column are consistently formatted, which will help in filtering the data correctly. The getDataRecordFormatter function handles various data types and ensures that boolean values are converted to TRUE_STRING or FALSE_STRING based on their truthiness ^[1][2].

To continue talking to Dosu, mention @dosu.

Comment From: kraftaa

hi, thank you. I'm sorry it's not clear to me -where/how should I use it? I'm running superset via helm in kubernetes. I'm changing some config params via values.yaml with configOverrides. thank you

Comment From: dosubot[bot]

To use the getDataRecordFormatter function for consistent boolean string formatting in Superset, you will need to customize the frontend code. Since you are running Superset via Helm in Kubernetes and using values.yaml with configOverrides, you can follow these steps:

1. Create a Custom JavaScript File: Create a custom JavaScript file that uses the getDataRecordFormatter function to format your boolean values.

```ts // customFormatter.js import { getDataRecordFormatter, GenericDataType } from 'src/filters/utils'; import { TRUE_STRING, FALSE_STRING } from 'src/utils/common';

const formatter = getDataRecordFormatter();

export function formatBooleanValues(value) { return formatter(value, GenericDataType.Boolean); } ```

1. Modify the Superset Configuration: Use the configOverrides in your values.yaml to include this custom JavaScript file in your Superset deployment.

yaml configOverrides: CUSTOM_JS: "/app/pythonpath/customFormatter.js"

1. Update the Superset Frontend: Ensure that your custom JavaScript file is loaded and used in the Superset frontend. You might need to modify the frontend code to import and use your custom formatter function where necessary.

2. Deploy the Changes: Deploy your changes by updating your Helm chart and applying the new configuration.

sh helm upgrade superset ./helm/superset -f values.yaml

By following these steps, you can ensure that the boolean values in your final_value column are consistently formatted, which will help in filtering the data correctly. This approach leverages the getDataRecordFormatter function to handle various data types and ensures that boolean values are converted to TRUE_STRING or FALSE_STRING based on their truthiness <sup>[1][2]</sup>.

To continue talking to Dosu, mention @dosu.

Comment From: kraftaa

thank you. I think there is no way to modify Frontend files if I only can change values.yaml for helm chart in kubernetes and not rebuilding the image.

Comment From: rusackas

Hi there! Are you still facing this issue in current versions (4.1.2 or newer)? Sorry if you didn't get the help you need here, but it's at risk of being closed as stale/not-planned if this issue is not still affecting you/others.

Comment From: rusackas

Closing this as not-planed, but you can always access the frontend code by checking out the repo and/or downloading the official ASF release. We're always open to contributions if you want to troubleshoot this and open a PR.