Bug #102594 json variable set to null is not <=> null
Submitted: 14 Feb 22:31 Modified: 15 Feb 7:41
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.31, 5.7.33 OS:CentOS (CentOS Linux release 7.6.1810 (Core))
Assigned to: CPU Architecture:Any

[14 Feb 22:31] Dave Pullin
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.
[15 Feb 7:41] MySQL Verification Team
Hello Dave,

Thank you for the report and test case.
Verified as described.

regards,
Umesh