[MDEV-19770] Prepared statement fails using JSON_OBJECT, UTF-8 value and null value Created: 2019-06-15  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Character Sets, JSON, Prepared Statements
Affects Version/s: 10.2.22, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Patrice Bulat Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux Gentoo running MariaDB 10.2.22


 Description   

Executing a prepared statement using JSON_OBJECT(), a UTF-8 value and a null value fails.

CREATE TABLE bug_json_utf8 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
var text COLLATE utf8mb4_unicode_ci);
PREPARE stmt FROM "INSERT INTO bug_json_utf8 SET id = NULL, var = JSON_OBJECT('text1',?,'text2',?)";

  1. This fails with Incorrect string value: '\xE9", "t...' for column 'var' at row 1
    EXECUTE stmt USING 'café',null;
  1. These work :
    EXECUTE stmt USING 'café','café';
    EXECUTE stmt USING null,null;

DEALLOCATE PREPARE stmt;

 Comments   
Comment by Alice Sherepa [ 2019-06-17 ]

Thanks! I reproduced it on 10.2-10.4:

set names utf8;
 
prepare stmt from "select JSON_OBJECT('1','é','2',?);";
 
execute stmt using 1;
 
execute stmt using null;


MariaDB [test]> execute stmt using 1;
 
+-----------------------------+
 
| JSON_OBJECT('1','é','2',?)  |
 
+-----------------------------+
 
| {"1": "é", "2": 1}          |
 
+-----------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> execute stmt using null;
 
+-----------------------------+
 
| JSON_OBJECT('1','é','2',?)  |
 
+-----------------------------+
 
| {"1": "�", "2": null}        |
 
+-----------------------------+
 
1 row in set (0.000 sec)

Comment by Patrice Bulat [ 2019-06-21 ]

Note that both queries fail on MariaDB 10.2.18 under Windows 10 :

MariaDB [(none)]> execute stmt using 1;
-----------------------------

JSON_OBJECT('1','é','2',?)

-----------------------------

NULL

-----------------------------
1 row in set (0.000 sec)

MariaDB [(none)]> execute stmt using null;
-----------------------------

JSON_OBJECT('1','é','2',?)

-----------------------------

NULL

-----------------------------
1 row in set (0.000 sec)

Generated at Thu Feb 08 08:54:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.