Description:
When using a `CASE` statement to handle JSON `null` values, the behavior does not match expectations. Specifically, the result of the `CASE` statement is `NULL` instead of the expected `false`, which contradicts the result of a simple equality comparison.
How to repeat:
1. Create a table with a JSON column and insert a row:
```sql
CREATE TABLE `t1` (`a` JSON);
INSERT INTO `t1` VALUES ('{"key1": null}');
```
2. Validate the JSON extraction result:
```sql
SELECT JSON_EXTRACT(t1.a, '$.key1') FROM t1;
```
Output:
```
+------------------------------+
| JSON_EXTRACT(t1.a, '$.key1') |
+------------------------------+
| null |
+------------------------------+
```
3. Compare the extracted JSON value against the string `'null'`:
```sql
SELECT JSON_EXTRACT(t1.a, '$.key1') = 'null' FROM t1;
```
Output:
```
+---------------------------------------+
| JSON_EXTRACT(t1.a, '$.key1') = 'null' |
+---------------------------------------+
| 0 |
+---------------------------------------+
```
This confirms that JSON `null` is not equal to the string `'null'`.
4. Use a `CASE` statement to handle the extracted JSON value:
```sql
SELECT CASE JSON_EXTRACT(t1.a, '$.key1')
WHEN 'null' THEN NULL
WHEN true THEN true
ELSE false
END
FROM t1;
```
Output:
```
+--------------------------------------------------------------------------------------------+
| CASE JSON_EXTRACT(t1.a, '$.key1') WHEN 'null' THEN NULL WHEN true THEN true ELSE false END |
+--------------------------------------------------------------------------------------------+
| NULL |
+--------------------------------------------------------------------------------------------+
```
Given that `JSON_EXTRACT(t1.a, '$.key1') = 'null'` returns `false` (as shown in step 3), the `CASE` statement should:
- Skip the condition `WHEN 'null' THEN NULL` because the values do not match.
- Proceed to the `ELSE` clause and return `false`.
Thus, the expected result for case-SQL is `false`.
Below is a complete example to reproduce the issue:
```sql
CREATE TABLE `t1` (`a` JSON);
INSERT INTO `t1` VALUES ('{"key1": null}');
-- Validate JSON extraction
SELECT JSON_EXTRACT(t1.a, '$.key1') FROM t1;
-- Equality check
SELECT JSON_EXTRACT(t1.a, '$.key1') = 'null' FROM t1;
-- CASE statement
SELECT CASE JSON_EXTRACT(t1.a, '$.key1')
WHEN 'null' THEN NULL
WHEN true THEN true
ELSE false
END
FROM t1;
```
Suggested fix:
1. Ensure that `CASE` statement comparison logic aligns with the behavior of the `=` operator for JSON values.
2. Specifically, improve the handling of JSON `null` to avoid confusion with SQL `NULL` or string `'null'`.
Description: When using a `CASE` statement to handle JSON `null` values, the behavior does not match expectations. Specifically, the result of the `CASE` statement is `NULL` instead of the expected `false`, which contradicts the result of a simple equality comparison. How to repeat: 1. Create a table with a JSON column and insert a row: ```sql CREATE TABLE `t1` (`a` JSON); INSERT INTO `t1` VALUES ('{"key1": null}'); ``` 2. Validate the JSON extraction result: ```sql SELECT JSON_EXTRACT(t1.a, '$.key1') FROM t1; ``` Output: ``` +------------------------------+ | JSON_EXTRACT(t1.a, '$.key1') | +------------------------------+ | null | +------------------------------+ ``` 3. Compare the extracted JSON value against the string `'null'`: ```sql SELECT JSON_EXTRACT(t1.a, '$.key1') = 'null' FROM t1; ``` Output: ``` +---------------------------------------+ | JSON_EXTRACT(t1.a, '$.key1') = 'null' | +---------------------------------------+ | 0 | +---------------------------------------+ ``` This confirms that JSON `null` is not equal to the string `'null'`. 4. Use a `CASE` statement to handle the extracted JSON value: ```sql SELECT CASE JSON_EXTRACT(t1.a, '$.key1') WHEN 'null' THEN NULL WHEN true THEN true ELSE false END FROM t1; ``` Output: ``` +--------------------------------------------------------------------------------------------+ | CASE JSON_EXTRACT(t1.a, '$.key1') WHEN 'null' THEN NULL WHEN true THEN true ELSE false END | +--------------------------------------------------------------------------------------------+ | NULL | +--------------------------------------------------------------------------------------------+ ``` Given that `JSON_EXTRACT(t1.a, '$.key1') = 'null'` returns `false` (as shown in step 3), the `CASE` statement should: - Skip the condition `WHEN 'null' THEN NULL` because the values do not match. - Proceed to the `ELSE` clause and return `false`. Thus, the expected result for case-SQL is `false`. Below is a complete example to reproduce the issue: ```sql CREATE TABLE `t1` (`a` JSON); INSERT INTO `t1` VALUES ('{"key1": null}'); -- Validate JSON extraction SELECT JSON_EXTRACT(t1.a, '$.key1') FROM t1; -- Equality check SELECT JSON_EXTRACT(t1.a, '$.key1') = 'null' FROM t1; -- CASE statement SELECT CASE JSON_EXTRACT(t1.a, '$.key1') WHEN 'null' THEN NULL WHEN true THEN true ELSE false END FROM t1; ``` Suggested fix: 1. Ensure that `CASE` statement comparison logic aligns with the behavior of the `=` operator for JSON values. 2. Specifically, improve the handling of JSON `null` to avoid confusion with SQL `NULL` or string `'null'`.