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]

This driver doesn't send the string with binary collation.

This driver sends UPDATE resource_json_test SET unicast_idcs = IF(?='', NULL, ?) WHERE id = ? in PREPARE command. And MySQL server decides second ? is string with binary collation. This is just a MySQL servers' behavior, not ours.

There are some ways to avoid it:

• Cast string in IF as JSON. UPDATE resource_json_test SET unicast_idcs = IF(?='', NULL, CAST(? AS JSON)) WHERE id = ?,
• Use interpolateParams=true option. This driver won't send PREPARE with query containing placeholder (?). Instead, this driver sends complete query as string with QUERY command, like MySQL Workbench. UPDATE resource_json_test SET unicast_idcs = IF('"testjson"'='', NULL, '"testjson"') WHERE id = 1

[suiriass]

@methane Thank you for the detailed explanation! The CAST(? AS JSON) workaround and interpolateParams=true option resolved the Error 3144 in ​Case 1, which is very helpful.

However, I’m still confused about ​Case 3 in my original test:

// Case 3: Using float64 for the ID (existing record)  
args3 := []any{`"testjson"`, `"testjson"`, float64(1)}  
// Executes successfully without CAST or interpolateParams  

Questions
​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.
Is this due to implicit type conversion (e.g., float64 → DECIMAL changing MySQL’s placeholder inference rules)?
​2、Is this a driver inconsistency?
Should the driver standardize parameter handling to avoid such type-dependent edge cases?
Or is this a MySQL server behavior that users must explicitly mitigate (e.g., always CAST for JSON fields)?
​Proposed Actions

• 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.
• If it’s a driver behavior, could the driver auto-detect JSON values and apply CAST internally (or warn users)?

Thank you for your time! Let me know if I can provide additional details.

Key Points:

• Acknowledges the fix for Case 1.
• Highlights the unresolved inconsistency in Case 3.
• Asks whether the parameter type’s impact is a MySQL or driver behavior.
• Suggests documentation or driver-level improvements.

[methane]

​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.