Description:
json_object("a",f()) wrongly returns NULL if f is a function that
is declared as 'returns JSON' and returns null
json_object("a",null) correctly returns {"a": null}
json_object("a",f()) correctly returns {"a": null} if f is a function that
is declared as 'returns TEXT' and returns null
I think this is the root cause of https://bugs.mysql.com/?id=106180
mysql> create database if not exists bug;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> drop function if exists bug.function_that_returns_null;
Query OK, 0 rows affected (0.00 sec)
mysql> drop function if exists bug.function_that_returns_null_json;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create function bug.function_that_returns_null () returns text return null;
Query OK, 0 rows affected (0.00 sec)
mysql> create function bug.function_that_returns_null_json () returns json return null;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select json_object("a",null) as constant_null,
-> json_object("a",bug.function_that_returns_null()) as function_that_returns_null,
-> json_object("a",bug.function_that_returns_null_json()) as function_that_returns_null_json;
+---------------+----------------------------+---------------------------------+
| constant_null | function_that_returns_null | function_that_returns_null_json |
+---------------+----------------------------+---------------------------------+
| {"a": null} | {"a": null} | NULL |
+---------------+----------------------------+---------------------------------+
1 row in set (0.00 sec)
mysql>
How to repeat:
create database if not exists bug;
drop function if exists bug.function_that_returns_null;
drop function if exists bug.function_that_returns_null_json;
create function bug.function_that_returns_null () returns text return null;
create function bug.function_that_returns_null_json () returns json return null;
select json_object("a",null) as constant_null,
json_object("a",bug.function_that_returns_null()) as function_that_returns_null,
json_object("a",bug.function_that_returns_null_json()) as function_that_returns_null_json;
Description: json_object("a",f()) wrongly returns NULL if f is a function that is declared as 'returns JSON' and returns null json_object("a",null) correctly returns {"a": null} json_object("a",f()) correctly returns {"a": null} if f is a function that is declared as 'returns TEXT' and returns null I think this is the root cause of https://bugs.mysql.com/?id=106180 mysql> create database if not exists bug; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> drop function if exists bug.function_that_returns_null; Query OK, 0 rows affected (0.00 sec) mysql> drop function if exists bug.function_that_returns_null_json; Query OK, 0 rows affected (0.00 sec) mysql> mysql> create function bug.function_that_returns_null () returns text return null; Query OK, 0 rows affected (0.00 sec) mysql> create function bug.function_that_returns_null_json () returns json return null; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select json_object("a",null) as constant_null, -> json_object("a",bug.function_that_returns_null()) as function_that_returns_null, -> json_object("a",bug.function_that_returns_null_json()) as function_that_returns_null_json; +---------------+----------------------------+---------------------------------+ | constant_null | function_that_returns_null | function_that_returns_null_json | +---------------+----------------------------+---------------------------------+ | {"a": null} | {"a": null} | NULL | +---------------+----------------------------+---------------------------------+ 1 row in set (0.00 sec) mysql> How to repeat: create database if not exists bug; drop function if exists bug.function_that_returns_null; drop function if exists bug.function_that_returns_null_json; create function bug.function_that_returns_null () returns text return null; create function bug.function_that_returns_null_json () returns json return null; select json_object("a",null) as constant_null, json_object("a",bug.function_that_returns_null()) as function_that_returns_null, json_object("a",bug.function_that_returns_null_json()) as function_that_returns_null_json;