Bug #101284 Problem with prepared statements
Submitted: 23 Oct 10:48 Modified: 17 Nov 16:42
Reporter: Damjan Vujnovic Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: JSON Severity:S2 (Serious)
Version:8.0.22 OS:CentOS
Assigned to: CPU Architecture:x86
Tags: json, prepared statements, regression, WL#9384

[23 Oct 10:48] Damjan Vujnovic
After upgrading to MySQL 8.0.22 we are getting errors when using certain prepared statements (that worked just fine with 8.0.21 and older versions).

How to repeat:
create database d1;
use d1;
create table t1(
  c1 json
insert into t1 values ('["a", "b"]'), ('["b", "c"]'), ('["c", "a"]');
select * from t1 where 'a' member of (c1 -> '$');
prepare s1 from "select * from t1 where ? member of (c1 -> '$')";
set @x = 'a';
execute s1 using @x;

On MySQL 8.0.21 and earlier, we get the following:

mysql> execute s1 using @x;
| c1         |
| ["a", "b"] |
| ["c", "a"] |
2 rows in set (0.00 sec)

Whereas on 8.0.22:

mysql> execute s1 using @x;
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

Many thanks,
[23 Oct 10:55] Damjan Vujnovic
Also, it's worth pointing out that changing the invocation to:

set @x = '"a"';
execute s1 using @x;

makes it work, but we would like some clarity on the change of behavior (the release notes do mention changes in prepared statements and potentially having to use CAST, but we would like to know if this is a regression or indeed a new behavior).
[23 Oct 11:17] MySQL Verification Team
Hello Damjan Vujnovic,

Thank you for the report and test case.

[17 Nov 16:42] Jon Stephens
Documented fix in the MySQL 8.0.23 changelog, as follows:

    Work done in MySQL 8.0.22 to cause prepared statements to be
    prepared only once introduced a regression in the handling of
    dynamic parameters to JSON functions. All JSON arguments were
    classified as data type MYSQL_TYPE_JSON, which overlooked the
    fact that JSON functions take two kinds of JSON
    parameters—JSON values and JSON documents—and this
    distinction cannot be made with the data type only. For Bug
    #31667405 this problem was solved for comparison operators and
    the IN() operator by making it possible to tag a JSON argument
    as being a scalar value, while letting arguments to other JSON
    functions be treated as JSON documents.

    The present fix restores for a number of JSON functions their
    treatment of certain arguments as JSON values, as listed here:

    ┬ĚThe first argument to MEMBER OF()

    ┬ĚThe third, fifth, seventh, and subsequent odd-numbered arguments
    to the functions JSON_INSERT(), JSON_REPLACE(), JSON_SET(),