makssent opened a new pull request, #40211:
URL: https://github.com/apache/shardingsphere/pull/40211
Changes proposed in this pull request:
- `CHARACTER SET OCTETS` means a binary string: the value is stored as
bytes without an encoding. Jaybird reads `CHARACTER SET OCTETS` values as
`byte[]` and maps a `VARCHAR(n) CHARACTER SET OCTETS` column to the JDBC type
`VARBINARY` and a `CHAR(n) CHARACTER SET OCTETS` column to the JDBC type
`BINARY`.
- `FirebirdPrepareStatementCommandExecutor` describes the columns and
parameters in the response to `op_prepare`. For a `VARCHAR(n) CHARACTER SET
OCTETS` column (the JDBC type `VARBINARY` in the ShardingSphere metadata),
`FirebirdPrepareStatementCommandExecutor` now takes the column length from
`FirebirdNonFixedLengthColumnSizeRegistry`, as for `CHAR` and `VARCHAR` columns.
- `FirebirdNonFixedLengthColumnSizeRegistry` keeps the lengths of text
columns that the Firebird module reads from the system tables when the metadata
is loaded: the common ShardingSphere metadata (`ShardingSphereColumn`) has no
column length.
- For a `VARCHAR(n) CHARACTER SET OCTETS` column,
`FirebirdReturnColumnPacket` now writes the sub type 1 (`OCTETS`).
- The sub type (`sqlsubtype`) of a text column in the description holds
the character set id, and id 1 means `OCTETS`. The length and sub type of a
text column are produced by
[Firebird](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/dsc.cpp#L1716-L1720),
and
[Jaybird](https://github.com/FirebirdSQL/jaybird/blob/v6.0.6/src/main/org/firebirdsql/jdbc/field/JdbcTypeConverter.java#L113-L114)
maps a column with sub type 1 to `VARBINARY` and reads the column value as
`byte[]`.
- `FirebirdBlrRowMetadata` parses the BLR with which the client describes
the parameter values in `op_execute`; now `FirebirdBlrRowMetadata` also keeps
the character set of `blr_varying2` and `blr_text2` fields.
`FirebirdExecuteStatementPacket` reads the value of a `VARCHAR` parameter in
the `OCTETS` character set as bytes, not as a string in the connection encoding.
- The character set of `blr_varying2` and `blr_text2` fields is written
by
[Jaybird](https://github.com/FirebirdSQL/jaybird/blob/v6.0.6/src/main/org/firebirdsql/gds/ng/wire/DefaultBlrCalculator.java#L183-L198)
and read by
[Firebird](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/parser.cpp#L203-L223).
- Without this change, `FirebirdExecuteStatementPacket` would decode the
bytes of the value as a string in the connection encoding, and bytes that are
not valid UTF-8 (for example, `0xff`) would be stored in the table corrupted.
- Previously, the proxy could not prepare a statement with a `VARCHAR(n)
CHARACTER SET OCTETS` parameter or select column. For example, the proxy
answered the prepare of `INSERT INTO T_PD (ID, VO) VALUES (?, ?)` with the
error `isc_random` ("Unknown exception… "this.columnLength" is null"), because
the proxy had no length of the column `VO`. Now the proxy describes `VARCHAR(n)
CHARACTER SET OCTETS` parameters and columns as the Firebird server does
(`SQL_VARYING`, sub type 1, length 10) and stores in the table the bytes sent
by the client.
---
- `FirebirdBinaryColumnType` chooses the Firebird type of a column
description by the JDBC type of the column in the metadata. For `CHAR(n)
CHARACTER SET OCTETS` (the JDBC type `BINARY`), `FirebirdBinaryColumnType` now
chooses `VARYING` instead of `TEXT`: the proxy describes all `CHAR` columns as
`VARCHAR`, and a `CHAR(n) CHARACTER SET OCTETS` column is now described in the
same way as the `VARCHAR(n) CHARACTER SET OCTETS` column in the first part.
- `FirebirdPrepareStatementCommandExecutor` takes the length of a `CHAR(n)
CHARACTER SET OCTETS` column from `FirebirdNonFixedLengthColumnSizeRegistry`,
and `FirebirdReturnColumnPacket` writes the sub type 1 (`OCTETS`) for a
`CHAR(n) CHARACTER SET OCTETS` column, as for the `VARCHAR(n) CHARACTER SET
OCTETS` column in the first part.
- The length and sub type of a `CHAR` column are produced by
[Firebird](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/dsc.cpp#L1705-L1708),
and
[Jaybird](https://github.com/FirebirdSQL/jaybird/blob/v6.0.6/src/main/org/firebirdsql/jdbc/field/JdbcTypeConverter.java#L113-L114)
maps a column with sub type 1 to a binary type and reads the column value as
`byte[]`.
- The client sends the value of a `CHAR(n) CHARACTER SET OCTETS`
parameter in a `blr_varying2` field with the `OCTETS` character set, because
the proxy describes the parameter as `VARCHAR` with sub type 1;
`FirebirdExecuteStatementPacket` from the first part reads the value of a
`CHAR(n) CHARACTER SET OCTETS` parameter as bytes.
- Previously, the proxy could not prepare a statement with a `CHAR(n)
CHARACTER SET OCTETS` parameter or select column. For example, the proxy
answered the prepare of `INSERT INTO T_CO (ID, CO) VALUES (?, ?)` with the same
error `isc_random`, because the proxy had no length of the column `CO`. Now the
proxy describes `CHAR(n) CHARACTER SET OCTETS` parameters and columns with the
same length and sub type as the Firebird server (length 4, sub type 1) and
passes the values as bytes. The proxy describes the column type as `VARCHAR`,
and the server as `CHAR`: the proxy describes all `CHAR` columns in this way,
and this pull request does not change that rule.
<details>
<summary>Verification</summary>
The same queries were executed directly against a real database and through
the proxy, and the results were compared.
* Server: Firebird 5.0
* Clients: Jaybird 5.0.13 and 6.0.6
Tables `T_PD (ID INTEGER NOT NULL PRIMARY KEY, VO VARCHAR(10) CHARACTER
SET OCTETS)` and `T_CO (ID INTEGER NOT NULL PRIMARY KEY, CO CHAR(4) CHARACTER
SET OCTETS)`, connection encoding UTF8; some values are not valid UTF-8. Both
`SELECT` from `T_PD` also have `ID BETWEEN 4401 AND 4404 ORDER BY ID`, and
`SELECT ... WHERE CO = ?` also has `ID BETWEEN 5101 AND 5103`. The cells show
the precision of parameters and columns (`getPrecision`), the result rows and
the number of changed rows; the values of `VO` and `CO` are read as `byte[]`
and shown in hex. Type names are not compared: the proxy describes every `CHAR`
column as `VARCHAR`, and the server describes `CO` as `CHAR`. Jaybird 5 and 6
give the same results. "Before the fix" is the proxy from `master`.
Before the fix:
| Query | Proxy | Server | Match |
|---|---|---|---|
| `INSERT INTO T_PD (ID, VO) VALUES (?, ?)` | prepare error `isc_random`
`Unknown exception. More details: java.lang.NullPointerException: Cannot invoke
"java.lang.Integer.intValue()" because "this.columnLength" is null` |
parameters 10, 10 | no |
| The same `INSERT` of rows 4401 `0x0102ff`, 4402 empty array, 4403 NULL,
4404 `0x007f8041424344454647` | not executed | each changed 1 | no |
| `UPDATE T_PD SET VO = ? WHERE ID = ?`, `0xc328`, 4402 | the same error |
parameters 10, 10; changed 1 | no |
| `SELECT ID, VO FROM T_PD WHERE VO = ?`, `0x0102ff` | the same error |
parameters 10; columns 10, 10; rows (4401, 0x0102ff) | no |
| `SELECT ID, VO FROM T_PD` | the same error | columns 10, 10; rows (4401,
0x0102ff), (4402, 0xc328), (4403, NULL), (4404, 0x007f8041424344454647) | no |
| Rows of `T_PD` read directly from the server, and `DELETE FROM T_PD
WHERE ID BETWEEN 4401 AND 4404` | no rows; changed 0 | the same 4 rows; changed
4 | no |
| `INSERT INTO T_CO (ID, CO) VALUES (5101, X'0102FF00')` | changed 1 |
changed 1 | yes |
| `INSERT INTO T_CO (ID, CO) VALUES (?, ?)`, 5102 and `0xab000102`, then
5103 and `0x0707` | prepare error `isc_random` `Unknown exception. More
details: java.lang.NullPointerException: Cannot invoke
"java.lang.Integer.intValue()" because "this.columnLength" is null` |
parameters 10, 4; each changed 1 | no |
| `SELECT ID FROM T_CO WHERE CO = ?`, `0x0102ff00` | the same error |
parameters 4; rows (5101) | no |
| `UPDATE T_CO SET CO = ? WHERE ID = 5103`, `0x09090909` | the same error
| parameters 4; changed 1 | no |
| `SELECT ID, CO FROM T_CO` | the same error | columns 10, 4; rows (5101,
0x0102ff00), (5102, 0xab000102), (5103, 0x09090909) | no |
| `DELETE FROM T_CO WHERE ID BETWEEN 5101 AND 5103` | changed 1 | changed
3 | no |
After the fix:
| Query | Proxy | Server | Match |
|---|---|---|---|
| `INSERT INTO T_PD (ID, VO) VALUES (?, ?)` of rows 4401, 4402, 4403 and
4404 | parameters 10, 10; each changed 1 | parameters 10, 10; each changed 1 |
yes |
| `UPDATE T_PD SET VO = ? WHERE ID = ?`, `0xc328`, 4402 | parameters 10,
10; changed 1 | parameters 10, 10; changed 1 | yes |
| `SELECT ID, VO FROM T_PD WHERE VO = ?`, `0x0102ff` | parameters 10;
columns 10, 10; rows (4401, 0x0102ff) | parameters 10; columns 10, 10; rows
(4401, 0x0102ff) | yes |
| `SELECT ID, VO FROM T_PD` | columns 10, 10; rows (4401, 0x0102ff),
(4402, 0xc328), (4403, NULL), (4404, 0x007f8041424344454647) | columns 10, 10;
rows (4401, 0x0102ff), (4402, 0xc328), (4403, NULL), (4404,
0x007f8041424344454647) | yes |
| Rows of `T_PD` read directly from the server, and `DELETE FROM T_PD
WHERE ID BETWEEN 4401 AND 4404` | the same 4 rows; changed 4 | the same 4 rows;
changed 4 | yes |
| `INSERT INTO T_CO (ID, CO) VALUES (5101, X'0102FF00')` | changed 1 |
changed 1 | yes |
| `INSERT INTO T_CO (ID, CO) VALUES (?, ?)`, 5102 and `0xab000102`, then
5103 and `0x0707` | parameters 10, 4; each changed 1 | parameters 10, 4; each
changed 1 | yes |
| `SELECT ID FROM T_CO WHERE CO = ?`, `0x0102ff00` | parameters 4; rows
(5101) | parameters 4; rows (5101) | yes |
| `UPDATE T_CO SET CO = ? WHERE ID = 5103`, `0x09090909` | parameters 4;
changed 1 | parameters 4; changed 1 | yes |
| `SELECT ID, CO FROM T_CO` | columns 10, 4; rows (5101, 0x0102ff00),
(5102, 0xab000102), (5103, 0x09090909) | columns 10, 4; rows (5101,
0x0102ff00), (5102, 0xab000102), (5103, 0x09090909) | yes |
| `DELETE FROM T_CO WHERE ID BETWEEN 5101 AND 5103` | changed 3 | changed
3 | yes |
</details>
---
Before committing this PR, I'm sure that I have checked the following
options:
- [x] My code follows the [code of
conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/)
of this project.
- [x] I have self-reviewed the commit code.
- [ ] I have (or in comment I request) added corresponding labels for the
pull request.
- [ ] I have passed maven check locally : `./mvnw clean install -B -T1C
-Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
- [ ] I have made corresponding changes to the documentation.
- [x] I have added corresponding unit tests for my changes.
- [ ] I have updated the Release Notes of the current development version.
For more details, see [Update Release
Note](https://shardingsphere.apache.org/community/en/involved/contribute/contributor/)
- [ ] I have specified the tool and the affected files or area for
substantial AI assistance, as required by the [AI-assisted contribution policy].
[AI-assisted contribution policy]:
https://github.com/apache/shardingsphere/blob/master/AI_POLICY.md
--
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]
