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
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S2 (Serious)
• Version: 8.0.32, 8.0.36
• OS: CentOS
• 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))';