[explore] Filter Select not working for int and bool

[rumbin]

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.18.2

Expected results

Integer and boolean column values get fetched for the Filter Select drop-downs in explore view.

Actual results

Selecting a column for filtering which is of type integer or boolean will not populate the select field with the column's distinct values.

2017-05-25 07:18:07,705:ERROR:root:1 is not JSON serializable
Traceback (most recent call last):
  File "/opt/apps/superset/venv_py3_test/lib64/python3.4/site-packages/superset/views/base.py", line 49, in wraps
    return f(self, *args, **kwargs)
  File "/opt/apps/superset/venv_py3_test/lib64/python3.4/site-packages/flask_appbuilder/security/decorators.py", line 52, in wraps
    return f(self, *args, **kwargs)
  File "/opt/apps/superset/venv_py3_test/lib64/python3.4/site-packages/superset/views/core.py", line 1090, in filter
    payload = json.dumps(datasource.values_for_column(column))
  File "/usr/lib64/python3.4/json/__init__.py", line 230, in dumps
    return _default_encoder.encode(obj)
  File "/usr/lib64/python3.4/json/encoder.py", line 192, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/lib64/python3.4/json/encoder.py", line 250, in iterencode
    return _iterencode(o, 0)
  File "/usr/lib64/python3.4/json/encoder.py", line 173, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: 1 is not JSON serializable

Steps to reproduce

• register the superset maintenance table main.ab_user in superset
• Enable Filter Select for it and make some columns of type int or bool filterable
• explore the table and select one of the above columns for filtering

[rumbin]

Columns of type float seem to work as expected, though, at least with the SQLite backend.
I tested it by registering another column for the above table, which is defined by id * 0.1 and making this column filterable of course.

[rumbin]

One more remark: If employed in a filter slice, all of the mentioned dtypes (str, int, bool, float) seem to work nicely. It is just the Filter Select in explore that is affected.

For the filter slice, the issue with integers was addressed in #2004.

[mistercrunch]

I just tested in MySQL and things work well for BIGINT. What's the type of 1 in that context?!

[mistercrunch]

Can you try changing the line in the stacktrace from
payload = json.dumps(datasource.values_for_column(column))
to
payload = json.dumps(datasource.values_for_column(column), default=utils.json_int_dttm_ser))?

At least the error should show the type it's struggling with. Most likely some numpy related type.

[rumbin]

@mistercrunch: Your fix seems to work, as tested with the 'active' and 'id' columns of said table.

I forgot to mention — and this might make a difference — the described behavior was investigated using a clean installation in a fresh python 3 venv, with just the default SQLite database.

I will also investigate this in our PostgreSQL environment...

[rumbin]

@mistercrunch: I now tested your solution in combination with a Postgres backend:

Integer columns now seem to work nicely. However, as I already anticipated somehow, boolean columns are not 100% compatible. While the lookup query apparently works, the distinct values are not displayed properly in the drop-down selector.
The selector correctly features two distinct options, however, they are both just blank.

The two options seem to represent the correct false and true values (in alphabetic order), as can be confirmed by querying with any of these filter values set.

I got the idea that the difference between SQLite's and Postgres' boolean types is that the former uses 0 and 1 as representation, while the later employs false and true. So maybe it's all about casting these values to the strings that get printed in the selector list.

[rumbin]

BTW: The very same behavior can be observed, when this (Postgres-)boolean column is included into a filter slice. Not quite sure whether I should file a new issue for this — apparently — Postgres-specific problem...