Environment MySQL Version: 8.x Go Version: go1.23.4 darwin/arm64 Driver Version: .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: The record ID does not exist. The ID parameter is passed as a float64 instead of int64. 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: <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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; "> 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: <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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 } "> 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!

1、Why does the parameter type (int64 vs float64) affect the behavior? When using int64 for the ID, MySQL treats the second ? as a BINARY string, causing the JSON error. But with float64, the same query succeeds without CAST or interpolateParams. I don't know. Please ask to MySQL. 2、Is this a driver inconsistency? No Should the driver standardize parameter handling to avoid such type-dependent edge cases? No Or is this a MySQL server behavior that users must explicitly mitigate (e.g., always CAST for JSON fields)? Yes, maybe. But ask to bugs.mysql.com. If this is a MySQL server limitation, adding a note to the driver’s documentation about JSON fields and placeholder inference would help users avoid similar issues. No. There are massive amount of edges in MySQL. We can not create and maintain such a note. If it’s a driver behavior, could the driver auto-detect JSON values and apply CAST internally (or warn users)? Impossible.

v1.9.0: Error 3144 when updating JSON field with parameterized IF condition (MySQL 8) · go-sql-driver mysql