| Bug #112082 | cannot identify where value of a json_col is null or not | ||
|---|---|---|---|
| Submitted: | 16 Aug 2023 9:27 | Modified: | 21 Aug 2023 3:19 |
| Reporter: | yujie wang | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Aug 2023 13:29]
MySQL Verification Team
Hi Mr. wang, Thank you for your bug report. However, this is not a bug. You did not write your query correctly. When written correctly, with a proper alias, you get the proper result: json_extract(json_col,'$.tinyint_col') null xx null Not a bug.
[17 Aug 2023 0:57]
yujie wang
what i mean is that it couldn't tell whether the value of key tinyint_col is null or not i know how to get the value of a key in json column for example, i expect the following query has result,but actually the result is empty mysql> select * from cc where json_extract(json_col,'$.tinyint_col') is null; Empty set (0.00 sec)
[17 Aug 2023 1:06]
yujie wang
mysql> select * from cc where json_extract(json_col,'$.tinyint_col') = null; Empty set (0.00 sec)
[17 Aug 2023 1:07]
yujie wang
mysql> select * from cc where json_extract(json_col,'$.tinyint_col') = 'null'; Empty set (0.00 sec)
[17 Aug 2023 12:24]
MySQL Verification Team
Hi, We do not see why would there be any rows returned for these queries. Can you elaborate on this further ????
[17 Aug 2023 12:34]
MySQL Verification Team
Many thanks in advance ......
[17 Aug 2023 12:40]
yujie wang
cause before the select query,i insert a json_col in the table, and its value is {"tinyint_col",null}
the insert query is "insert into cc values(json_object('tinyint_col',null));"
then i suppose the value of the 'tinyint_col' is null, that means json_extract(json_col,'$.tinyint_col') is null;
[17 Aug 2023 12:40]
yujie wang
mysql> select json_extract(json_col,'$.tinyint_col') from cc; +----------------------------------------+ | json_extract(json_col,'$.tinyint_col') | +----------------------------------------+ | null | +----------------------------------------+ 1 row in set (0.01 sec)
[17 Aug 2023 13:18]
MySQL Verification Team
Hi Mr. wang, Seems that this feature is missing from our JSON domain, so we are verifying your report as a feature request. Thank you.
[20 Aug 2023 12:20]
Roy Lyseng
Posted by developer:
This is correct behavior.
Notice that an SQL NULL and a JSON NULL value is not the same.
You get an indication because "null" is lowercase,
whereas an SQL NULL would be displayed as uppercase.
Notice also this statement which returns true:
select json_extract(json_col,'$.tinyint_col') = cast('null' as json) from cc;
Finally, JSON_VALUE is able to convert between JSON NULL and SQL NULL:
select json_value(json_col, '$.tinyint_col') is null from cc;
The latter query returns a true value.
[21 Aug 2023 3:19]
yujie wang
got it, thank you!
Description: cannot identify where value of a json_col is null or not expect the sql last result of following sqls is 1 How to repeat: mysql> create table cc(json_col json); Query OK, 0 rows affected (0.03 sec) mysql> insert into cc values(json_object('tinyint_col',null)); Query OK, 1 row affected (0.00 sec) mysql> select json_extract(json_col,'$.tinyint_col') from cc; +----------------------------------------+ | json_extract(json_col,'$.tinyint_col') | +----------------------------------------+ | null | +----------------------------------------+ 1 row in set (0.01 sec) mysql> select (select json_extract(json_col,'$.tinyint_col') from cc) is null; +-----------------------------------------------------------------+ | (select json_extract(json_col,'$.tinyint_col') from cc) is null | +-----------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec)