fix: null value and empty string in filter

[zhaoyongjie]

SUMMARY

closes: #18147
Currently, Superset Filter can't show NULL value and empty string. This PR replaced NULL with <NULL> and replaced "" with <empty string>

Notice that: A known limitation for the current design, users can't filter below text values:

- "<empty string>"
- "<NULL>"

we need to add to escape mechanism somewhere in separated PR.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

After

Before

TESTING INSTRUCTIONS

1. Create a virtual dataset

SELECT '' as name
UNION ALL
SELECT null as name
union all
SELECT 'foo' as name

2. Open the virtual dataset in Explore Page
3. Drag name column in Filter Control
4. You can get 3 options in the filter selection
5. Add all options and open query inspector. The query is:

SELECT count(*) AS count
FROM
  (SELECT '' as name
   UNION ALL SELECT null as name
   union all SELECT 'foo' as name) AS virtual_table
WHERE name IS NULL
  OR name IN ('',
              'foo',)
ORDER BY count DESC
LIMIT 1000;

ADDITIONAL INFORMATION

• Has associated issue: [explore] Filter unable to select NULL and empty string #18147
• Required feature flags:
• Changes UI
• Includes DB Migration (follow approval process in SIP-59)
  • 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

[codecov]

Codecov Report

Merging #18171 (c52b6e3) into master (4b89ac7) will decrease coverage by 0.07%.
The diff coverage is 84.61%.

❗ Current head c52b6e3 differs from pull request most recent head 024d529. Consider uploading reports for the commit 024d529 to get more accurate results

@@            Coverage Diff             @@
##           master   #18171      +/-   ##
==========================================
- Coverage   65.95%   65.87%   -0.08%     
==========================================
  Files        1584     1591       +7     
  Lines       62046    62416     +370     
  Branches     6273     6286      +13     
==========================================
+ Hits        40920    41119     +199     
- Misses      19505    19675     +170     
- Partials     1621     1622       +1     

Flag	Coverage Δ
hive	?
mysql	81.28% <92.56%> (+0.10%)	⬆️
postgres	81.33% <92.56%> (+0.10%)	⬆️
presto	?
python	81.42% <92.56%> (-0.25%)	⬇️
sqlite	81.03% <92.56%> (+0.11%)	⬆️

Flags with carried forward coverage won't be shown. Click here to find out more.

Impacted Files	Coverage Δ
.../superset-ui-core/src/connection/SupersetClient.ts	100.00% <ø> (ø)
...ackages/superset-ui-core/src/utils/featureFlags.ts	100.00% <ø> (ø)
...chart-echarts/src/MixedTimeseries/controlPanel.tsx	80.00% <ø> (ø)
...hart-echarts/src/MixedTimeseries/transformProps.ts	0.00% <0.00%> (ø)
...lugin-chart-echarts/src/Timeseries/transformers.ts	50.83% <ø> (ø)
superset-frontend/src/embedded/index.tsx	0.00% <0.00%> (ø)
...nd/src/explore/components/ExploreActionButtons.tsx	59.45% <0.00%> (-3.40%)	⬇️
...rc/explore/components/controls/TextAreaControl.jsx	80.00% <0.00%> (-4.22%)	⬇️
superset-frontend/src/preamble.ts	0.00% <ø> (ø)
superset-frontend/src/setup/setupClient.ts	0.00% <0.00%> (ø)
... and 66 more

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 4b89ac7...024d529. Read the comment docs.

[villebro]

As a bandaid this should fix the current issue. However, I can't help but feel slightly uncomfortable by these types of workarounds. I think the optimal architectural design would be such, that <NULL> and <empty string> would be the labels in the select component in the frontend, but the value would still be retained in its original form. Did you consider this approach, and if so, was there some reason it doesn't work?

[zhaoyongjie]

As a bandaid this should fix the current issue. However, I can't help but feel slightly uncomfortable by these types of workarounds. I think the optimal architectural design would be such, that <NULL> and <empty string> would be the labels in the select component in the frontend, but the value would still be retained in its original form. Did you consider this approach, and if so, was there some reason it doesn't work?

Nice catch! I have made frontend render of that.

[villebro]

This is great! One last question regarding possible simplification of handle_single_value, other than that LGTM

[michael-s-molina]

Shouldn't we translate the option labels? I notice that the optionLabel function is always returning the values in English.

[villebro]

..then the translation needs to be done where the constant is defined. And if we do that, then we should definitely remove support for using the strings "<NULL>" and "<empty string>" as proxies for None and "" respectively in the backend and assume they'll always just be labels, but never values.

[villebro]

LGTM - there's some additional ideas on how this could be further improved, but let's leave those to follow-up PRs to get this bug fixed first. Thanks @zhaoyongjie for all the iteration, much appreciated!

[zhaoyongjie]

LGTM - there's some additional ideas on how this could be further improved, but let's leave those to follow-up PRs to get this bug fixed first. Thanks @zhaoyongjie for all the iteration, much appreciated!

there's a related PR, I will work together with @stephenLYZ to improve this.