Bug #105166 invalid error: Cannot create a JSON value from string with CHARACTER SET 'binary
Submitted: 7 Oct 14:36 Modified: 5 Nov 13:32
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.31, 5.7.35 OS:Any (CentOS Linux release 7.6.1810 (Core))
Assigned to: CPU Architecture:Any

[7 Oct 14:36] Dave Pullin
Description:
strange interaction between user variable, function, if expression, and json, yielding

select bug.noop(if(false,@nothing,json_object()));

ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.

bug.noop is function that accepts and returns json (in the test it does nothing). Problem does not occur without this.

@nothing is a user variable that is not set or set to null. Problem does not occur if the user variable is replaced with explicit null.

if(false ...) even through the user variable is only referenced in the false clause, problem does not occur if the if() is removed.

mysql> create database if not exists bug;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> 
mysql> drop function if exists bug.noop;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create function bug.noop(injson json) returns json return injson;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select bug.noop(if(false,@nothing,json_object()));
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
mysql> select bug.noop(if(false,null,json_object()));
+----------------------------------------+
| bug.noop(if(false,null,json_object())) |
+----------------------------------------+
| {}                                     |
+----------------------------------------+
1 row in set (0.00 sec)

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

mysql> select (if(false,null,json_object()));
+--------------------------------+
| (if(false,null,json_object())) |
+--------------------------------+
| {}                             |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select bug.noop(json_object());
+-------------------------+
| bug.noop(json_object()) |
+-------------------------+
| {}                      |
+-------------------------+
1 row in set (0.00 sec)

How to repeat:
create database if not exists bug;
drop function if exists bug.noop;
create function bug.noop(injson json) returns json return injson;
select bug.noop(if(false,@nothing,json_object()));
[8 Oct 9:28] MySQL Verification Team
Hello Dave,

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

regards,
Umesh
[19 Oct 11:20] Knut Anders Hatlen
This seems to be fixed in MySQL 8.0.22 as part of WL#9384 (Prepare each DML statement once).
[5 Nov 13:32] Jon Stephens
Fixed in MySQL 8.0.22 by WL#9384.

Closed.