MySQL Bugs: #101284: Problem with prepared statements

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:	None
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

Description:
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,
damjan

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).

Hello Damjan Vujnovic,

Thank you for the report and test case.

regards,
Umesh

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&mdash;JSON values and JSON documents&mdash;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(),
    JSON_ARRAY_APPEND(), and JSON_ARRAY_INSERT().

Closed.

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:

~~~sql
select @@version; -- 8.0.22
select @@sql_mode; -- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

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.

Hi Rodrigo,

I think your problem will be fixed in the next release.

Thanks for the quick response Roy. Have a nice day =)