v1.9.0: Error 3144 when updating JSON field with parameterized IF condition (MySQL 8)

[suiriass]

Environment

​MySQL Version: 8.x
​Go Version: go1.23.4 darwin/arm64
​Driver Version: github.com/go-sql-driver/mysql v1.9.0

Problem Description

When executing an UPDATE statement with a parameterized IF condition on a JSON field, the driver returns ​Error 3144 (Cannot create a JSON value from a string with CHARACTER SET 'binary'). However, the same query works in MySQL Workbench and succeeds when:

1. The record ID does not exist.
2. The ID parameter is passed as a float64 instead of int64.
3. The JSON field is updated directly without using IF.

This suggests an inconsistency in parameter handling for JSON fields under certain conditions.

Steps to Reproduce
​1、Table Schema:

CREATE TABLE `resource_json_test` (  
  `id` int NOT NULL AUTO_INCREMENT,  
  `unicast_idcs` json DEFAULT NULL,  
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;  

2、​Initial Data:

INSERT INTO `resource_json_test` VALUES (1, '"dsgfdfgdg"', '2025-03-20 20:26:00', '2025-03-20 20:28:24');  

3、​Test Code:

func TestJsonBug1(t *testing.T) {  
    // Case 1: ID exists (int64) -> Fails with Error 3144  
    args1 := []any{`"testjson"`, `"testjson"`, int64(1)}  
    _, err := mydb.Exec(  
        `UPDATE resource_json_test SET unicast_idcs = IF(?='', NULL, ?) WHERE id = ?`,  
        args1...,  
    )  
    // Output: Error 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.  

    // Case 2: ID does not exist (int64) -> Succeeds  
    args2 := []any{`"testjson"`, `"testjson"`, int64(3)}  
    _, err = mydb.Exec(...) // No error  

    // Case 3: ID exists (float64) -> Succeeds  
    args3 := []any{`"testjson"`, `"testjson"`, float64(1)}  
    _, err = mydb.Exec(...) // No error  

    // Case 4: Direct assignment (no IF condition) -> Succeeds  
    _, err = mydb.Exec(  
        `UPDATE resource_json_test SET unicast_idcs = ? WHERE id = ?`,  
        `"testjson"`, int64(1),  
    ) // No error  
}  

Observed Behavior

​Case 1 (int64 ID + IF condition):
Fails with Error 3144, indicating the driver is passing the JSON string with an incorrect character set (binary).

​Case 2 (Non-existent ID):
Succeeds because no actual JSON update occurs.

​Case 3 (float64 ID):
Succeeds, implying the parameter type affects character set handling.

​Case 4 (Direct assignment):
Succeeds, confirming that the issue is specific to the IF condition with parameterized logic.

Expected Behavior

The parameterized IF condition should handle JSON values consistently regardless of the ID’s data type (int64 or float64).

Additional Notes

• MySQL Workbench executes the equivalent query successfully, proving the SQL syntax is valid.
• The driver may be implicitly converting parameters to a binary character set in certain scenarios.
• This issue affects type-sensitive operations on JSON fields when combined with conditional logic.

Temporary Workaround

Use direct assignment (Case 4) or ensure the ID parameter is passed as float64 (not ideal).

Please let me know if you need further details or logs to debug this!

[methane]

mysql> UPDATE resource_json_test SET unicast_idcs = IF("testjson"='', NULL, "testjson") WHERE id = 1;
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 1 in value for column 'resource_json_test.unicast_idcs'.