RaigorJiang opened a new issue, #40227: URL: https://github.com/apache/shardingsphere/issues/40227
### Before reporting a bug - [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview). ### ShardingSphere version 5.5.4-SNAPSHOT, built from commit `0cbdde72383415d3b33be763daddf362dc89b944`. ### ShardingSphere project ShardingSphere-Proxy ### Expected behavior A prepared query binding the Go string `"48"` to a MySQL `BIGINT` column should return the same result through Proxy as it does when connecting directly to MySQL. For the query below, both `"48"` and `int64(48)` should return one row with `employee_count = 1` and `employee_id = 48`. ```sql SELECT count(DISTINCT e.id) AS employee_count, e.id AS employee_id FROM pd_vrs_employee e WHERE e.id = ? GROUP BY e.id; ``` ### Actual behavior With Proxy's backend JDBC connection configured with `useServerPrepStmts=false`, binding `"48"` returns no rows without a SQL error, while binding `int64(48)` returns the expected row. The problem was reproduced for IDs 48 and 49 using GORM `Find`, `Count`, and `Raw(...).Find(...)`. | Connection | String parameters: 6 cases | int64 parameters: 6 cases | | --- | --- | --- | | Direct MySQL | All passed | All passed | | Proxy, backend `useServerPrepStmts=false` | All failed | All passed | | Proxy, backend `useServerPrepStmts=true` | All passed | All passed | Representative client results with backend `useServerPrepStmts=false`: ```text RawFind parameter=48 (string): rows=0 actual=map[] expected_id=48 expected_count=1 Count parameter=48 (string): actual=0 expected=1 RawFind parameter=48 (int64): result=map[employee_count:1 employee_id:48] ``` Proxy's SQL log shows a byte-array parameter for the string case: ```text Actual SQL: ds_0 ::: SELECT count(DISTINCT e.id) AS employee_count, e.id AS employee_id FROM pd_vrs_employee e WHERE e.id = ? GROUP BY e.id ::: [[B@3e93fa00] ``` Connector/J's `profileSQL` output shows the following backend execution with client-side prepared statements: ```sql -- Go string parameter "48": returns no rows SELECT count(DISTINCT e.id) AS employee_count, e.id AS employee_id FROM pd_vrs_employee e WHERE e.id = x'3438' GROUP BY e.id; -- Go int64 parameter 48: returns the expected row SELECT count(DISTINCT e.id) AS employee_count, e.id AS employee_id FROM pd_vrs_employee e WHERE e.id = 48 GROUP BY e.id; ``` With backend `useServerPrepStmts=true`, the same queries pass, although Proxy still logs a byte-array parameter and Connector/J's execution log still renders `x'3438'`. Therefore, the hexadecimal representation alone does not establish whether the query returns the correct result. ### Reason analysis The source code and runtime logs indicate that the string parameter retains binary semantics while passing through Proxy: 1. `go-sql-driver/mysql` 1.8.0 sends the Go string parameter as `MYSQL_TYPE_STRING` (`0xfe`) in `COM_STMT_EXECUTE`. 2. `MySQLComStmtExecutePacket.decodeStringParameterValue()` converts this parameter from `byte[]` to `String` only when `isCharacterColumnType()` recognizes the target column. 3. `isCharacterColumnType()` recognizes `STRING`, `VAR_STRING`, and `VARCHAR`, but does not recognize the `LONGLONG` type corresponding to the tested `BIGINT` column. 4. The parameter therefore remains `byte[]`, and `JDBCBackendStatement.setParameter()` passes it to Connector/J using `PreparedStatement.setObject()`. Relevant source at the tested commit: - [MySQLComStmtExecutePacket](https://github.com/apache/shardingsphere/blob/0cbdde72383415d3b33be763daddf362dc89b944/database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/binary/execute/MySQLComStmtExecutePacket.java#L151-L164) - [JDBCBackendStatement](https://github.com/apache/shardingsphere/blob/0cbdde72383415d3b33be763daddf362dc89b944/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/jdbc/statement/JDBCBackendStatement.java#L91-L96) The earlier STRING parameter decoding change, commit `bdf6cd35c6749b7c80fe90208b1d738e3fb4d74a` (`#38009`), covers character target columns. The numeric-column case described here remains reproducible on the tested commit. The expected correction is to preserve string semantics for this numeric-column binding and let MySQL perform the string-to-number conversion. Binary-column parameters must continue to preserve their original bytes. This report verifies the BIGINT scenario; it does not claim that all numeric column types or backend driver configurations have been tested. ### Steps to reproduce #### Environment - Deployment: Proxy in Standalone mode, using an in-memory H2 JDBC metadata repository. - Backend database: MySQL 8.0.37. - Backend JDBC driver: MySQL Connector/J 8.4.0, manually installed in Proxy's `ext-lib`. - Go: 1.23.4. - `github.com/go-sql-driver/mysql`: 1.8.0. - `gorm.io/driver/mysql`: 1.5.6. - `gorm.io/gorm`: 1.25.8. - Frontend Go driver: `interpolateParams=false`. - Transactions: `LOCAL`. - Physical table: `business_db.pd_vrs_employee`, with an `id BIGINT NOT NULL` column. - Routing: one storage unit and a SINGLE rule, without sharding or read/write splitting. #### Steps 1. Prepare fixture rows with IDs 48 and 49 in `business_db.pd_vrs_employee`, populating the table's other required columns. The verified test inserted the fixtures inside a transaction and rolled them back after each run. 2. Configure Proxy with the following settings. `global.yaml`: ```yaml mode: type: Standalone repository: type: JDBC authority: users: - user: root@% password: "<proxy-password>" admin: true privilege: type: ALL_PERMITTED transaction: defaultType: LOCAL props: sql-show: true ``` `database-business.yaml`: ```yaml databaseName: business_db dataSources: ds_0: url: jdbc:mysql://127.0.0.1:3306/business_db?useSSL=false&allowPublicKeyRetrieval=true&useServerPrepStmts=false&cachePrepStmts=false&profileSQL=true username: root password: "<mysql-password>" maxPoolSize: 4 minPoolSize: 1 rules: - !SINGLE tables: - ds_0.pd_vrs_employee ``` 3. Connect through Proxy using the listed Go driver and GORM versions, with `interpolateParams=false`. 4. Execute the parameterized query with a string parameter and an `int64` parameter. The core GORM call used in the verified reproduction is: ```go query := `SELECT count(DISTINCT e.id) AS employee_count, e.id AS employee_id FROM pd_vrs_employee e WHERE e.id = ? GROUP BY e.id` for _, parameter := range []any{"48", int64(48)} { actual := make(map[string]interface{}) result := tx.Raw(query, parameter).Find(&actual) if result.Error != nil { t.Fatal(result.Error) } if result.RowsAffected != 1 || actual["employee_count"] != int64(1) || actual["employee_id"] != int64(48) { t.Errorf("parameter=%v (%T): rows=%d actual=%v", parameter, parameter, result.RowsAffected, actual) } } ``` Here, `tx` is the GORM transaction containing the fixture rows. Repeat with ID 49. 5. Run the same test directly against MySQL. Both parameter types return the expected result. 6. Change only the backend JDBC setting to `useServerPrepStmts=true`, restart Proxy, and repeat. Both parameter types return the expected result in this configuration. All three verified runs rolled back their fixtures successfully. A final direct MySQL check confirmed that IDs 48 and 49 were absent. ### Example code _No response_ -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
