"not in" filter is not working for 0 value

[kkalyan]

The following filter does not get applied.

Output Table still contains entries with 0.

[apitts]

Have you tried wrapping the 0 in quotes? i.e. '0'.

[kkalyan]

@apitts I did with '0' as well, but does not work.

[apitts]

Mmmm....I'm not sure then @kkalyan. You may want to try using the having clause in the SQL input instead.

[kkalyan]

SQL where clause shows up only for Tables, not Druid Datasources.

[x4base]

None/NaN values are displayed as 0 (by using df.fillna(0) in https://github.com/airbnb/caravel/blob/d5b8414fde5e1a000f0faab9b801ba899959560a/caravel/viz.py#L161), so id_type=0 should be filtered out successfully, what you see should be id_type=None or NaN.
If you want to verify this, you can group by id_type and see if there are more than one records with id_type=0. If there are two records, then one of them is id_type=0, while the other one is id_type=None/NaN.
@mistercrunch Should we do something like fillna('(NaN)') or just don't fillna at all?

[kkalyan]

Thanks @x4base for looking into this.

group by id_type

Filter in '0' is also not working.

[x4base]

Do you mean
filter "in 0": no records
filter "not in 0" 1 record
If so, there is only id_type=null in your datasource.

If you know how to send Druid query in the terminal, it will be clear to see the difference.

[kkalyan]

this seems to be fixed in 0.10.0

[kkalyan]

Re-opening the issue as I understood the issue correctly.

• when the data stored in druid in null, Caravel shows as 0.
• users may add the value shown in the filter, which will result in druid query like this
  "filter": { "type": "selector", "dimension": "id_type", "value": 0 }
• If the user uses null in the filter, the resulting druid query is 'null'
  "filter": { "type": "selector", "dimension": "id_type", "value": 'null' }
• Both of these are incorrect, the following filter will work.
  "filter": { "type": "selector", "dimension": "id_type", "value": null }

I'll send PR for this.
cc @x4base and @mistercrunch please suggest.

[kkalyan]

workaround for this is to use regex match with an expression like .+

[xrmx]

@kkalyan Please add some references when reopening a bug more than one year later :)

[kkalyan]

@xrmx I think this got re-opened accidentally :) closing it now.