Bug #116703 `JSON_VALID` has inconsistent behavior for `NULL`
Submitted: 19 Nov 2024 6:06 Modified: 19 Nov 2024 6:25
Reporter: Yang Keao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.33, 8.0.40, 8.4.3, 9.1.0 OS:Any
Assigned to: CPU Architecture:Any

[19 Nov 2024 6:06] Yang Keao
Description:
The document (https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-valid) said the `JSON_VALID` function should return `NULL` for `NULL` argument. However, this behavior is not consistent when the argument is a column or a literal NULL.

For a column, it returns 0 instead of NULL.
```
mysql> create table t(col int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select json_valid(t.col) from t;
+-------------------+
| json_valid(t.col) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)
```

For a NULL literal, it returns NULL

```
mysql> select json_valid(NULL);
+------------------+
| json_valid(NULL) |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

```

How to repeat:
For a column, it returns 0 instead of NULL.
```
mysql> create table t(col int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select json_valid(t.col) from t;
+-------------------+
| json_valid(t.col) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)
```

For a NULL literal, it returns NULL

```
mysql> select json_valid(NULL);
+------------------+
| json_valid(NULL) |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

```

Suggested fix:
Make the result consistent. It'd be better to return NULL for `json_valid(col)` when the column is a `NULL`.
[19 Nov 2024 6:25] MySQL Verification Team
Hello Yang Keao,

Thank you for the report and test case.

regards,
Umesh