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.

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.

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

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:

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

// 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);
}

Modify the Superset Configuration: Use the configOverrides in your values.yaml to include this custom JavaScript file in your Superset deployment.
```
configOverrides:
 CUSTOM_JS: "/app/pythonpath/customFormatter.js"
```
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.
Deploy the Changes: Deploy your changes by updating your Helm chart and applying the new configuration.
```
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 ^[1]^[2].

To continue talking to Dosu, mention @dosu.

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.

apache / superset