Bug #101284 Problem with prepared statements
Submitted: 23 Oct 2020 10:48 Modified: 17 Nov 2020 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 2020 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 2020 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 2020 11:17] MySQL Verification Team
Hello Damjan Vujnovic,

Thank you for the report and test case.

[17 Nov 2020 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(),

[17 Dec 2020 22:50] Rodrigo Pedra Brum
It is not clear to me if the 8.0.23 changelog from the last comment would fix this scenario.

But when using prepared statements and mixing `JSON_SET()` it affects all other parameters.

For example, this test script fails in 8.0.22:

select @@version; -- 8.0.22

drop schema if exists `dummy`;
create schema `dummy`;

use `dummy`;

create table `dummy`.`table`
    `id` bigint unsigned not null,
    `properties` json null

insert into `dummy`.`table` (`id`, `properties`) VALUES (1, '{}');
insert into `dummy`.`table` (`id`, `properties`) VALUES (2, '{}');

prepare stmt from 'update `dummy`.`table` set `properties` = json_set(`properties`, ''$."foo"'', ?), `properties` = json_set(`properties`, ''$."bar"'', ?) where `id` in (?, ?)';

set @foo = 'foo';
set @bar = 'bar';
set @id = '1'; -- works with 1 (as an integer)
set @other = '2'; -- works with 2 (as an integer)

execute stmt using @foo, @bar, @id, @other;

deallocate prepare stmt;

select * from `dummy`.`table`;

I also tested with `where id = ?` and using only one parameter, and it fails if the bound value is a string.

I also tested dropping the `unsigned` from the `id` column, or using the `int` datatype.

This worked fine in previous versions.

If this would be a different bug, let me know and I will open another bug report.
[18 Dec 2020 8:04] Roy Lyseng
Hi Rodrigo,

I think your problem will be fixed in the next release.
[18 Dec 2020 15:19] Rodrigo Pedra Brum
Thanks for the quick response Roy. Have a nice day =)