search

evidence-dev / evidence

Business intelligence as code: build fast, interactive data visualizations in pure SQL and markdown
https://evidence.dev
MIT License
4.22k stars 200 forks source link

[Bug]: Dropdown containing `null` will not load #2179

Closed hughess closed 2 months ago

hughess commented 3 months ago

Describe the bug

If the query feeding a dropdown component contains a null value, the input will not be set, even if a default value is supplied.

Steps to Reproduce

Reproducible example below. If null is included in the selection query, the DataTable will not load. If you change that to "def", it will load.

```sql selection
select 'abc' as option
union all
select null as option
union all
select 'ghi' as option
```sql filtered select * from ( select 'abc' as option, 100 as sales union all select 'def' as option, 200 as sales union all select 'ghi' as option, 300 as sales ) where option = '${inputs.withnulls.value}' ``` ```` ### Logs _No response_ ### System Info _No response_ ### Severity annoyance ### Additional Information, or Workarounds Workaround by filtering nulls out of the query feeding the dropdown
archiewood commented 3 months ago

what's the intended behaviour here?

throw an error?

or allow the user to have null as a pickable option?

hughess commented 3 months ago

I could see both being helpful for different situations:

  1. legitimate nulls - you want to filter a chart to see a null series, where a value was not set for some group of data
    • In this case want nulls to appear (ideally without having to convert to string)
  2. Illegitimate nulls - null sneaks into your dropdown
    • In this case would want an error
    • Or would want the dropdown to automatically exclude nulls

I think including nulls is probably not possible with the current inputs setup - if it was in a string column, you’d have to wrap the input in single quotes in your sql, and then the nulls wouldn’t match

andrejohansson commented 3 months ago

I just had troubles with null, and we have a legitimate case where I want null to be one of the selectable options. I also stubled on some issues with the query but AI explained very well to me why I was wrong.

I would want to be able to have null as a value in a combobox (preferable with a human readable label) and that the filter gets created in the correct way:

WHERE (field is null OR field IN (1,2,3,4))

image