Description:
A function sets a variable, declared as JSON, to null, and then tests it against null using null-safe equals (<=>). It returns FALSE, but should return TRUE.
(Changing the declaration from JSON to LONGTEXT eliminates the bug).
mysql> delimiter ^
mysql> create database if not exists bug^
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> drop function if exists bug.func^
Query OK, 0 rows affected (0.00 sec)
mysql> create function bug.func()returns boolean
-> begin
-> declare x json;
-> set x=null;
-> return x<=>null;
-> end^
Query OK, 0 rows affected (0.00 sec)
mysql> select bug.func()^
+------------+
| bug.func() |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
---------------------------
mysql> delimiter ^
mysql> create database if not exists bug^
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> drop function if exists bug.func^
Query OK, 0 rows affected (0.00 sec)
mysql> create function bug.func()returns boolean
-> begin
-> declare x longtext;
-> set x=null;
-> return x<=>null;
-> end^
Query OK, 0 rows affected (0.00 sec)
mysql> select bug.func()^
+------------+
| bug.func() |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
How to repeat:
delimiter ^
create database if not exists bug^
drop function if exists bug.func^
create function bug.func()returns boolean
begin
declare x json;
set x=null;
return x<=>null;
end^
select bug.func()^
Suggested fix:
declare x ANYTYPE;
set x=null;
x<=>null should be true, regardless of the type of the variable.
Description: A function sets a variable, declared as JSON, to null, and then tests it against null using null-safe equals (<=>). It returns FALSE, but should return TRUE. (Changing the declaration from JSON to LONGTEXT eliminates the bug). mysql> delimiter ^ mysql> create database if not exists bug^ Query OK, 1 row affected, 1 warning (0.00 sec) mysql> drop function if exists bug.func^ Query OK, 0 rows affected (0.00 sec) mysql> create function bug.func()returns boolean -> begin -> declare x json; -> set x=null; -> return x<=>null; -> end^ Query OK, 0 rows affected (0.00 sec) mysql> select bug.func()^ +------------+ | bug.func() | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) --------------------------- mysql> delimiter ^ mysql> create database if not exists bug^ Query OK, 1 row affected, 1 warning (0.00 sec) mysql> drop function if exists bug.func^ Query OK, 0 rows affected (0.00 sec) mysql> create function bug.func()returns boolean -> begin -> declare x longtext; -> set x=null; -> return x<=>null; -> end^ Query OK, 0 rows affected (0.00 sec) mysql> select bug.func()^ +------------+ | bug.func() | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) How to repeat: delimiter ^ create database if not exists bug^ drop function if exists bug.func^ create function bug.func()returns boolean begin declare x json; set x=null; return x<=>null; end^ select bug.func()^ Suggested fix: declare x ANYTYPE; set x=null; x<=>null should be true, regardless of the type of the variable.