Bug #114267 Getting different results when executing the same SQL statement about json
Submitted: 7 Mar 2024 17:07 Modified: 8 Mar 2024 7:42
Reporter: ksql- team Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:8.0.32, 8.0.36 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: json, PS

[7 Mar 2024 17:07] ksql- team
Description:
Getting different results when executing the same SQL statement about json.

How to repeat:
create database json_test;
use json_test;
prepare ps2 from 'insert into pt2 values (?)';
set @a=123;
set @b='123';
execute ps2 using @b; --return OK

prepare ps2 from 'insert into pt2 values (?)';
execute ps2 using @a; --return ERROR 3140
execute ps2 using @b; --return ERROR 3140

prepare ps2 from 'insert into pt2 values (?)';
execute ps2 using @b; --return OK
[8 Mar 2024 7:04] MySQL Verification Team
Hello!

Thank you for the report and detailed steps to reproduce.
Verified as described.

Sincerely,
Umesh
[8 Mar 2024 7:42] Roy Lyseng
Posted by developer:
 
This is not a bug.
Using non-JSON values for parameters of type JSON have some complex rules.
But you can avoid most problems by wrapping the parameter indicator in a CAST:

  prepare ps2 from 'insert into pt2 values (CAST(? AS JSON))';