You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ma...@apache.org on 2018/04/18 05:26:24 UTC

[incubator-superset] branch master updated: [explore] proper filtering of NULLs and '' (#4651)

This is an automated email from the ASF dual-hosted git repository.

maximebeauchemin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-superset.git


The following commit(s) were added to refs/heads/master by this push:
     new eac97ce  [explore] proper filtering of NULLs and '' (#4651)
eac97ce is described below

commit eac97ce9f201be1c8245aa9ad452c05f702f8fd3
Author: Maxime Beauchemin <ma...@gmail.com>
AuthorDate: Tue Apr 17 22:26:21 2018 -0700

    [explore] proper filtering of NULLs and '' (#4651)
    
    * [WiP] [explore] proper filtering of NULLs and ''
    
    TODO: handling of Druid equivalents
    
    * Unit tests
    
    * Some refactoring
    
    * [druid] fix 'Unorderable types' when col has nuls
    
    Error "unorderable types: str() < int()" occurs when grouping by a
    numerical Druid colummn that contains null values.
    
    * druid/pydruid returns strings in the datafram with NAs for nulls
    * Superset has custom logic around get_fillna_for_col that fills in the
    NULLs based on declared column type (FLOAT here), so now we have a mixed
    bag of type in the series
    * pandas chokes on pivot_table or groupby operations as it cannot sorts
    mixed types
    
    The approach here is to stringify and fillna('<NULL>') to get a
    consistent series.
    
    * typo
    
    * Fix druid_func tests
    
    * Addressing more comments
    
    * last touches
---
 superset/assets/package.json                       |  1 +
 .../explore/components/FilterControl_spec.jsx      |  5 +-
 .../javascripts/explore/components/Filter_spec.jsx |  4 +-
 .../assets/spec/javascripts/utils/common_spec.jsx  | 12 ++++-
 superset/assets/src/SqlLab/actions.js              |  3 +-
 superset/assets/src/chart/chartAction.js           |  9 +++-
 superset/assets/src/common.js                      |  6 +++
 superset/assets/src/components/AlteredSliceTag.jsx |  2 +-
 .../src/explore/components/controls/Filter.jsx     |  8 ++-
 superset/assets/src/utils/common.js                | 27 ++++++++++
 superset/connectors/base/models.py                 | 30 +++++++++++
 superset/connectors/druid/models.py                | 60 +++++++++++++---------
 superset/connectors/sqla/models.py                 | 30 +++++------
 tests/druid_func_tests.py                          |  9 ++--
 tests/druid_tests.py                               |  4 +-
 15 files changed, 153 insertions(+), 57 deletions(-)

diff --git a/superset/assets/package.json b/superset/assets/package.json
index ceb34c7..c4911b3 100644
--- a/superset/assets/package.json
+++ b/superset/assets/package.json
@@ -74,6 +74,7 @@
     "moment": "^2.20.1",
     "mousetrap": "^1.6.1",
     "mustache": "^2.2.1",
+    "npm": "^5.7.1",
     "nvd3": "1.8.6",
     "object.entries": "^1.0.4",
     "object.keys": "^0.1.0",
diff --git a/superset/assets/spec/javascripts/explore/components/FilterControl_spec.jsx b/superset/assets/spec/javascripts/explore/components/FilterControl_spec.jsx
index 69f8b27..2b83fff 100644
--- a/superset/assets/spec/javascripts/explore/components/FilterControl_spec.jsx
+++ b/superset/assets/spec/javascripts/explore/components/FilterControl_spec.jsx
@@ -225,12 +225,13 @@ describe('FilterControl', () => {
     wrapper.instance().fetchFilterValues(0, 'col1');
     expect(wrapper.state().activeRequest).to.equal(spyReq);
     // Sets active to null after success
-    $.ajax.getCall(0).args[0].success('choices');
+    $.ajax.getCall(0).args[0].success(['opt1', 'opt2', null, '']);
     expect(wrapper.state().filters[0].valuesLoading).to.equal(false);
-    expect(wrapper.state().filters[0].valueChoices).to.equal('choices');
+    expect(wrapper.state().filters[0].valueChoices).to.deep.equal(['opt1', 'opt2', null, '']);
     expect(wrapper.state().activeRequest).to.equal(null);
   });
 
+
   it('cancels active request if another is submitted', () => {
     const spyReq = sinon.spy();
     spyReq.abort = sinon.spy();
diff --git a/superset/assets/spec/javascripts/explore/components/Filter_spec.jsx b/superset/assets/spec/javascripts/explore/components/Filter_spec.jsx
index 1a4c395..27425de 100644
--- a/superset/assets/spec/javascripts/explore/components/Filter_spec.jsx
+++ b/superset/assets/spec/javascripts/explore/components/Filter_spec.jsx
@@ -46,7 +46,7 @@ describe('Filter', () => {
     expect(wrapper.find(Select)).to.have.lengthOf(2);
     expect(wrapper.find(Button)).to.have.lengthOf(1);
     expect(wrapper.find(SelectControl)).to.have.lengthOf(1);
-    expect(wrapper.find('#select-op').prop('options')).to.have.lengthOf(8);
+    expect(wrapper.find('#select-op').prop('options')).to.have.lengthOf(10);
   });
 
   it('renders five op choices for table datasource', () => {
@@ -58,7 +58,7 @@ describe('Filter', () => {
       filterable_cols: ['country_name'],
     };
     const druidWrapper = shallow(<Filter {...props} />);
-    expect(druidWrapper.find('#select-op').prop('options')).to.have.lengthOf(9);
+    expect(druidWrapper.find('#select-op').prop('options')).to.have.lengthOf(11);
   });
 
   it('renders six op choices for having filter', () => {
diff --git a/superset/assets/spec/javascripts/utils/common_spec.jsx b/superset/assets/spec/javascripts/utils/common_spec.jsx
index 5aa4b43..861c38a 100644
--- a/superset/assets/spec/javascripts/utils/common_spec.jsx
+++ b/superset/assets/spec/javascripts/utils/common_spec.jsx
@@ -1,6 +1,6 @@
 import { it, describe } from 'mocha';
 import { expect } from 'chai';
-import { isTruthy } from '../../../src/utils/common';
+import { isTruthy, optionFromValue } from '../../../src/utils/common';
 
 describe('utils/common', () => {
   describe('isTruthy', () => {
@@ -40,4 +40,14 @@ describe('utils/common', () => {
       expect(isTruthy('false')).to.equal(false);
     });
   });
+  describe('optionFromValue', () => {
+    it('converts values as expected', () => {
+      expect(optionFromValue(false)).to.deep.equal({ value: false, label: '<false>' });
+      expect(optionFromValue(true)).to.deep.equal({ value: true, label: '<true>' });
+      expect(optionFromValue(null)).to.deep.equal({ value: '<NULL>', label: '<NULL>' });
+      expect(optionFromValue('')).to.deep.equal({ value: '', label: '<empty string>' });
+      expect(optionFromValue('foo')).to.deep.equal({ value: 'foo', label: 'foo' });
+      expect(optionFromValue(5)).to.deep.equal({ value: 5, label: '5' });
+    });
+  });
 });
diff --git a/superset/assets/src/SqlLab/actions.js b/superset/assets/src/SqlLab/actions.js
index 04a9a5e..6449470 100644
--- a/superset/assets/src/SqlLab/actions.js
+++ b/superset/assets/src/SqlLab/actions.js
@@ -2,6 +2,7 @@
 import shortid from 'shortid';
 import { now } from '../modules/dates';
 import { t } from '../locales';
+import { COMMON_ERR_MESSAGES } from '../common';
 
 const $ = require('jquery');
 
@@ -163,7 +164,7 @@ export function runQuery(query) {
           }
         }
         if (msg.indexOf('CSRF token') > 0) {
-          msg = t('Your session timed out, please refresh your page and try again.');
+          msg = COMMON_ERR_MESSAGES.SESSION_TIMED_OUT;
         }
         dispatch(queryFailed(query, msg));
       },
diff --git a/superset/assets/src/chart/chartAction.js b/superset/assets/src/chart/chartAction.js
index b9338a9..a2f0116 100644
--- a/superset/assets/src/chart/chartAction.js
+++ b/superset/assets/src/chart/chartAction.js
@@ -1,6 +1,7 @@
 import { getExploreUrlAndPayload, getAnnotationJsonUrl } from '../explore/exploreUtils';
 import { requiresQuery, ANNOTATION_SOURCE_TYPES } from '../modules/AnnotationTypes';
 import { Logger, LOG_ACTIONS_LOAD_EVENT } from '../logger';
+import { COMMON_ERR_MESSAGES } from '../common';
 
 const $ = window.$ = require('jquery');
 
@@ -160,12 +161,16 @@ export function runQuery(formData, force = false, timeout = 60, key) {
             errObject = err.responseJSON;
           } else if (err.stack) {
             errObject = {
-              error: 'Unexpected error: ' + err.description,
+              error: t('Unexpected error: ') + err.description,
               stacktrace: err.stack,
             };
+          } else if (err.responseText && err.responseText.indexOf('CSRF') >= 0) {
+            errObject = {
+              error: COMMON_ERR_MESSAGES.SESSION_TIMED_OUT,
+            };
           } else {
             errObject = {
-              error: 'Unexpected error.',
+              error: t('Unexpected error.'),
             };
           }
           dispatch(chartUpdateFailed(errObject, key));
diff --git a/superset/assets/src/common.js b/superset/assets/src/common.js
index d84f064..cc509eb 100644
--- a/superset/assets/src/common.js
+++ b/superset/assets/src/common.js
@@ -1,5 +1,6 @@
 /* eslint-disable global-require */
 import $ from 'jquery';
+import { t } from './locales';
 
 const utils = require('./modules/utils');
 
@@ -30,3 +31,8 @@ export function appSetup() {
   window.jQuery = $;
   require('bootstrap');
 }
+
+// Error messages used in many places across applications
+export const COMMON_ERR_MESSAGES = {
+  SESSION_TIMED_OUT: t('Your session timed out, please refresh your page and try again.'),
+};
diff --git a/superset/assets/src/components/AlteredSliceTag.jsx b/superset/assets/src/components/AlteredSliceTag.jsx
index eb24424..ad1356b 100644
--- a/superset/assets/src/components/AlteredSliceTag.jsx
+++ b/superset/assets/src/components/AlteredSliceTag.jsx
@@ -61,7 +61,7 @@ export default class AlteredSliceTag extends React.Component {
         return '[]';
       }
       return value.map((v) => {
-        const filterVal = v.val.constructor === Array ? `[${v.val.join(', ')}]` : v.val;
+        const filterVal = v.val && v.val.constructor === Array ? `[${v.val.join(', ')}]` : v.val;
         return `${v.col} ${v.op} ${filterVal}`;
       }).join(', ');
     } else if (controls[key] && controls[key].type === 'BoundsControl') {
diff --git a/superset/assets/src/explore/components/controls/Filter.jsx b/superset/assets/src/explore/components/controls/Filter.jsx
index 49a9751..539a413 100644
--- a/superset/assets/src/explore/components/controls/Filter.jsx
+++ b/superset/assets/src/explore/components/controls/Filter.jsx
@@ -2,8 +2,8 @@ import React from 'react';
 import PropTypes from 'prop-types';
 import Select from 'react-select';
 import { Button, Row, Col } from 'react-bootstrap';
-import SelectControl from './SelectControl';
 import { t } from '../../../locales';
+import SelectControl from './SelectControl';
 
 const operatorsArr = [
   { val: 'in', type: 'array', useSelect: true, multi: true },
@@ -16,6 +16,8 @@ const operatorsArr = [
   { val: '<', type: 'string', havingOnly: true },
   { val: 'regex', type: 'string', datasourceTypes: ['druid'] },
   { val: 'LIKE', type: 'string', datasourceTypes: ['table'] },
+  { val: 'IS NULL', type: null },
+  { val: 'IS NOT NULL', type: null },
 ];
 const operators = {};
 operatorsArr.forEach((op) => {
@@ -90,6 +92,10 @@ export default class Filter extends React.Component {
 
   renderFilterFormControl(filter) {
     const operator = operators[filter.op];
+    if (operator.type === null) {
+      // IS NULL or IS NOT NULL
+      return null;
+    }
     if (operator.useSelect && !this.props.having) {
       // TODO should use a simple Select, not a control here...
       return (
diff --git a/superset/assets/src/utils/common.js b/superset/assets/src/utils/common.js
index f2c3bd2..093882a 100644
--- a/superset/assets/src/utils/common.js
+++ b/superset/assets/src/utils/common.js
@@ -103,3 +103,30 @@ export function isTruthy(obj) {
   }
   return !!obj;
 }
+
+export function optionLabel(opt) {
+  if (opt === null) {
+    return '<NULL>';
+  } else if (opt === '') {
+    return '<empty string>';
+  } else if (opt === true) {
+    return '<true>';
+  } else if (opt === false) {
+    return '<false>';
+  } else if (typeof opt !== 'string' && opt.toString) {
+    return opt.toString();
+  }
+  return opt;
+}
+
+export function optionValue(opt) {
+  if (opt === null) {
+    return '<NULL>';
+  }
+  return opt;
+}
+
+export function optionFromValue(opt) {
+  // From a list of options, handles special values & labels
+  return { value: optionValue(opt), label: optionLabel(opt) };
+}
diff --git a/superset/connectors/base/models.py b/superset/connectors/base/models.py
index 68a020e..8e4a2a2 100644
--- a/superset/connectors/base/models.py
+++ b/superset/connectors/base/models.py
@@ -6,6 +6,7 @@ from __future__ import unicode_literals
 
 import json
 
+from past.builtins import basestring
 from sqlalchemy import (
     and_, Boolean, Column, Integer, String, Text,
 )
@@ -185,6 +186,35 @@ class BaseDatasource(AuditMixinNullable, ImportMixin):
             'verbose_map': verbose_map,
         }
 
+    @staticmethod
+    def filter_values_handler(
+            values, target_column_is_numeric=False, is_list_target=False):
+        def handle_single_value(v):
+            # backward compatibility with previous <select> components
+            if isinstance(v, basestring):
+                v = v.strip('\t\n \'"')
+                if target_column_is_numeric:
+                    # For backwards compatibility and edge cases
+                    # where a column data type might have changed
+                    v = utils.string_to_num(v)
+                if v == '<NULL>':
+                    return None
+                elif v == '<empty string>':
+                    return ''
+            return v
+        if isinstance(values, (list, tuple)):
+            values = [handle_single_value(v) for v in values]
+        else:
+            values = handle_single_value(values)
+        if is_list_target and not isinstance(values, (tuple, list)):
+            values = [values]
+        elif not is_list_target and isinstance(values, (tuple, list)):
+            if len(values) > 0:
+                values = values[0]
+            else:
+                values = None
+        return values
+
     def get_query_str(self, query_obj):
         """Returns a query as a string
 
diff --git a/superset/connectors/druid/models.py b/superset/connectors/druid/models.py
index 25c68ac..26e3c72 100644
--- a/superset/connectors/druid/models.py
+++ b/superset/connectors/druid/models.py
@@ -1277,6 +1277,21 @@ class DruidDatasource(Model, BaseDatasource):
             client.query_builder.last_query.query_dict, indent=2)
         return query_str
 
+    @staticmethod
+    def homogenize_types(df, groupby_cols):
+        """Converting all GROUPBY columns to strings
+
+        When grouping by a numeric (say FLOAT) column, pydruid returns
+        strings in the dataframe. This creates issues downstream related
+        to having mixed types in the dataframe
+
+        Here we replace None with <NULL> and make the whole series a
+        str instead of an object.
+        """
+        for col in groupby_cols:
+            df[col] = df[col].fillna('<NULL>').astype(str)
+        return df
+
     def query(self, query_obj):
         qry_start_dttm = datetime.now()
         client = self.cluster.get_pydruid_client()
@@ -1284,6 +1299,8 @@ class DruidDatasource(Model, BaseDatasource):
             client=client, query_obj=query_obj, phase=2)
         df = client.export_pandas()
 
+        df = self.homogenize_types(df, query_obj.get('groupby', []))
+
         if df is None or df.size == 0:
             raise Exception(_('No data was returned.'))
         df.columns = [
@@ -1322,40 +1339,31 @@ class DruidDatasource(Model, BaseDatasource):
             query=query_str,
             duration=datetime.now() - qry_start_dttm)
 
-    @staticmethod
-    def get_filters(raw_filters, num_cols):  # noqa
+    @classmethod
+    def get_filters(cls, raw_filters, num_cols):  # noqa
+        """Given Superset filter data structure, returns pydruid Filter(s)"""
         filters = None
         for flt in raw_filters:
-            if not all(f in flt for f in ['col', 'op', 'val']):
+            col = flt.get('col')
+            op = flt.get('op')
+            eq = flt.get('val')
+            if (
+                    not col or
+                    not op or
+                    (eq is None and op not in ('IS NULL', 'IS NOT NULL'))):
                 continue
-
-            col = flt['col']
-            op = flt['op']
-            eq = flt['val']
             cond = None
-            if op in ('in', 'not in'):
-                eq = [
-                    types.replace('"', '').strip()
-                    if isinstance(types, string_types)
-                    else types
-                    for types in eq]
-            elif not isinstance(flt['val'], string_types):
-                eq = eq[0] if eq and len(eq) > 0 else ''
-
             is_numeric_col = col in num_cols
-            if is_numeric_col:
-                if op in ('in', 'not in'):
-                    eq = [utils.string_to_num(v) for v in eq]
-                else:
-                    eq = utils.string_to_num(eq)
-
+            is_list_target = op in ('in', 'not in')
+            eq = cls.filter_values_handler(
+                eq, is_list_target=is_list_target,
+                target_column_is_numeric=is_numeric_col)
             if op == '==':
                 cond = Dimension(col) == eq
             elif op == '!=':
                 cond = Dimension(col) != eq
             elif op in ('in', 'not in'):
                 fields = []
-
                 # ignore the filter if it has no value
                 if not len(eq):
                     continue
@@ -1365,10 +1373,8 @@ class DruidDatasource(Model, BaseDatasource):
                     for s in eq:
                         fields.append(Dimension(col) == s)
                     cond = Filter(type='or', fields=fields)
-
                 if op == 'not in':
                     cond = ~cond
-
             elif op == 'regex':
                 cond = Filter(type='regex', pattern=eq, dimension=col)
             elif op == '>=':
@@ -1385,6 +1391,10 @@ class DruidDatasource(Model, BaseDatasource):
                     col, None, eq,
                     upperStrict=True, alphaNumeric=is_numeric_col,
                 )
+            elif op == 'IS NULL':
+                cond = Dimension(col) == None  # NOQA
+            elif op == 'IS NOT NULL':
+                cond = Dimension(col) != None  # NOQA
 
             if filters:
                 filters = Filter(type='and', fields=[
diff --git a/superset/connectors/sqla/models.py b/superset/connectors/sqla/models.py
index 7fbb7ae..86528a3 100644
--- a/superset/connectors/sqla/models.py
+++ b/superset/connectors/sqla/models.py
@@ -11,7 +11,6 @@ from flask import escape, Markup
 from flask_appbuilder import Model
 from flask_babel import lazy_gettext as _
 import pandas as pd
-from past.builtins import basestring
 import six
 import sqlalchemy as sa
 from sqlalchemy import (
@@ -574,28 +573,21 @@ class SqlaTable(Model, BaseDatasource):
         where_clause_and = []
         having_clause_and = []
         for flt in filter:
-            if not all([flt.get(s) for s in ['col', 'op', 'val']]):
+            if not all([flt.get(s) for s in ['col', 'op']]):
                 continue
             col = flt['col']
             op = flt['op']
-            eq = flt['val']
             col_obj = cols.get(col)
+            is_list_target = op in ('in', 'not in')
+            eq = self.filter_values_handler(
+                flt.get('val'),
+                target_column_is_numeric=col_obj.is_num,
+                is_list_target=is_list_target)
             if col_obj:
                 if op in ('in', 'not in'):
-                    values = []
-                    for v in eq:
-                        # For backwards compatibility and edge cases
-                        # where a column data type might have changed
-                        if isinstance(v, basestring):
-                            v = v.strip("'").strip('"')
-                            if col_obj.is_num:
-                                v = utils.string_to_num(v)
-
-                        # Removing empty strings and non numeric values
-                        # targeting numeric columns
-                        if v is not None:
-                            values.append(v)
-                    cond = col_obj.sqla_col.in_(values)
+                    cond = col_obj.sqla_col.in_(eq)
+                    if '<NULL>' in eq:
+                        cond = or_(cond, col_obj.sqla_col == None)  # noqa
                     if op == 'not in':
                         cond = ~cond
                     where_clause_and.append(cond)
@@ -616,6 +608,10 @@ class SqlaTable(Model, BaseDatasource):
                         where_clause_and.append(col_obj.sqla_col <= eq)
                     elif op == 'LIKE':
                         where_clause_and.append(col_obj.sqla_col.like(eq))
+                    elif op == 'IS NULL':
+                        where_clause_and.append(col_obj.sqla_col == None)  # noqa
+                    elif op == 'IS NOT NULL':
+                        where_clause_and.append(col_obj.sqla_col != None)  # noqa
         if extras:
             where = extras.get('where')
             if where:
diff --git a/tests/druid_func_tests.py b/tests/druid_func_tests.py
index 3082bec..c367bd7 100644
--- a/tests/druid_func_tests.py
+++ b/tests/druid_func_tests.py
@@ -34,7 +34,7 @@ class DruidFuncTestCase(unittest.TestCase):
     def test_get_filters_ignores_invalid_filter_objects(self):
         filtr = {'col': 'col1', 'op': '=='}
         filters = [filtr]
-        self.assertEqual(None, DruidDatasource.get_filters(filters, []))
+        self.assertIsNone(DruidDatasource.get_filters(filters, []))
 
     def test_get_filters_constructs_filter_in(self):
         filtr = {'col': 'A', 'op': 'in', 'val': ['a', 'b', 'c']}
@@ -108,7 +108,7 @@ class DruidFuncTestCase(unittest.TestCase):
         filtr1 = {'col': 'A', 'op': 'in', 'val': []}
         filtr2 = {'col': 'A', 'op': 'not in', 'val': []}
         res = DruidDatasource.get_filters([filtr1, filtr2], [])
-        self.assertEqual(None, res)
+        self.assertIsNone(res)
 
     def test_get_filters_constructs_equals_for_in_not_in_single_value(self):
         filtr = {'col': 'A', 'op': 'in', 'val': ['a']}
@@ -119,14 +119,15 @@ class DruidFuncTestCase(unittest.TestCase):
         filtr = {'col': 'A', 'op': '==', 'val': ['a', 'b']}
         res = DruidDatasource.get_filters([filtr], [])
         self.assertEqual('a', res.filter['filter']['value'])
+
         filtr = {'col': 'A', 'op': '==', 'val': []}
         res = DruidDatasource.get_filters([filtr], [])
-        self.assertEqual('', res.filter['filter']['value'])
+        self.assertIsNone(res.filter['filter']['value'])
 
     def test_get_filters_handles_none_for_string_types(self):
         filtr = {'col': 'A', 'op': '==', 'val': None}
         res = DruidDatasource.get_filters([filtr], [])
-        self.assertEqual('', res.filter['filter']['value'])
+        self.assertIsNone(res)
 
     def test_get_filters_extracts_values_in_quotes(self):
         filtr = {'col': 'A', 'op': 'in', 'val': ['  "a" ']}
diff --git a/tests/druid_tests.py b/tests/druid_tests.py
index d03378b..6947a6c 100644
--- a/tests/druid_tests.py
+++ b/tests/druid_tests.py
@@ -61,6 +61,7 @@ GB_RESULT_SET = [
         'timestamp': '2012-01-01T00:00:00.000Z',
         'event': {
             'dim1': 'Canada',
+            'dim2': 'boy',
             'metric1': 12345678,
         },
     },
@@ -69,6 +70,7 @@ GB_RESULT_SET = [
         'timestamp': '2012-01-01T00:00:00.000Z',
         'event': {
             'dim1': 'USA',
+            'dim2': 'girl',
             'metric1': 12345678 / 2,
         },
     },
@@ -165,7 +167,7 @@ class DruidTests(SupersetTestCase):
             'row_limit': 5000,
             'include_search': 'false',
             'metrics': ['count'],
-            'groupby': ['dim1', 'dim2d'],
+            'groupby': ['dim1', 'dim2'],
             'force': 'true',
         }
         # two groupby

-- 
To stop receiving notification emails like this one, please contact
maximebeauchemin@apache.org.