sadpandajoe opened a new pull request, #45305:
URL: https://github.com/apache/superset/pull/45305
### SUMMARY
A `NOT IN` filter on a SQLAlchemy-backed dataset also removed rows whose
column value is NULL, not only the listed values. For example, ticking "Exclude
selected values" on a dashboard Select filter for `CA` dropped the `CA` rows
and the NULL rows. Only the selected values should be excluded.
`ExploreMixin.get_sqla_query` built `NOT IN` as the plain negation of `IN`.
For a list with no NULL value that compiles to `col NOT IN (...)`, which is not
true for a NULL row under SQL three-valued logic, so the `WHERE` clause
discarded it. With this change, a `NOT IN` whose normalized value list contains
no NULL is built as `col IS NULL OR col NOT IN (...)`. A list that contains
NULL (including the `<NULL>` placeholder, which is coerced to NULL before the
condition is built) still excludes NULL rows, and `IN` is unchanged.
The dashboard scope SQL used by the MCP service builds the same condition
and is updated to match. `UPDATING.md` documents the behavior change, because
saved charts and reports that use `NOT IN` can now return additional NULL rows.
### Root Cause
- **Verified:** in `superset/models/helpers.py`, the IN/NOT_IN branch of
`get_sqla_query` applied `cond = ~cond` to `sqla_col.in_(eq)`. Running real
`SqlaTable.get_sqla_query` queries against SQLite with rows `CA, NY, TX, NULL`:
`NOT IN ['CA']` returned `NY, TX` and `NOT IN ['CA', 'NY']` returned `TX`; the
NULL row was missing in both. A list that contains NULL (`['CA', None]`)
excluded NULL rows by design.
- **Verified:** the native Select filter sends a plain `{op: 'NOT IN', val:
[...]}` through `buildQueryObject` and `processFilters` unchanged (traced for
the native Select path only), so no frontend change is needed.
- **History:** the branch shape comes from the explicit NULL-operand
handling added with the IS NULL operator work; no commit states an intent for
NULL rows under a list without NULL, so this was the SQL default rather than a
documented choice.
- **Not changed here:** `!=`, `NOT LIKE` and `NOT ILIKE` have the same NULL
behavior in separate code paths, as do the multi-value array branch and the
IP/port advanced data types. They are left as they are and are called out in
`UPDATING.md`.
### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF
Not applicable: backend query generation only, no UI change. Regression
guard output (real `get_sqla_query` executed on SQLite with a NULL row):
- Before the fix: `assert {'NY', 'TX'} == {'NY', 'TX', None}` and `assert
{'TX'} == {'TX', None}` (2 failed, 3 passed).
- After the fix: `tests/unit_tests/models/helpers_test.py -k not_in_filter`
passes (9 passed).
### TESTING INSTRUCTIONS
1. Run the new tests:
`pytest tests/unit_tests/models/helpers_test.py -k not_in_filter` and
`pytest tests/unit_tests/mcp_service/test_dashboard_scope_sql.py -k
in_list_null_handling`.
2. Manual check: create a dashboard with a table chart on a dataset whose
text column has some NULL values, add a Select filter on that column with
"Inverse selection" enabled, select one value, and confirm only that value is
excluded while NULL rows remain. Selecting the `<NULL>` option as one of the
excluded values still excludes NULL rows.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Migration is atomic, supports rollback & is backwards-compatible
- [ ] Confirm DB migration upgrade and downgrade tested
- [ ] Runtime estimates and downtime expectations provided
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
