You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "makssent (via GitHub)" <gi...@apache.org> on 2026/10/23 11:14:18 UTC
[I] Firebird Proxy closes the connection when selecting integer arithmetic expressions [shardingsphere]
makssent opened a new issue, #40356:
URL: https://github.com/apache/shardingsphere/issues/40356
### 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, master, commit [44e364e6410c8fa79a78b571ba5990bc830aed2a](https://github.com/apache/shardingsphere/commit/44e364e6410c8fa79a78b571ba5990bc830aed2a).
### ShardingSphere project
ShardingSphere-Proxy
### Expected behavior
Selecting integer arithmetic expressions should return the same column types, names, labels, scales and values as a direct connection to Firebird, and leave the connection usable.
For the INTEGER expressions below, Firebird returns BIGINT values with scale 0 and column names ADD, SUBTRACT, MULTIPLY and DIVIDE; an explicit alias changes the column label.
### Actual behavior
The proxy describes `ID + 1` as INTEGER with column name `+`, but sends an eight-byte BIGINT value.
The client interprets part of that value as the next protocol operation and closes the connection.
Each SELECT below runs on a separate new connection after inserting rows `(5101, 'a')` and `(5102, 'b')` into `T_PLAIN`.
The final DELETE also runs on its own new connection.
Column descriptions below use `name/label type` and all server scales are 0.
| Step | Firebird server | Firebird Proxy |
| --- | --- | --- |
| Select `ID + 1` for ID 5101 | ADD/ADD BIGINT; 5102 | `Unsupported or unexpected operation code 5102 in processOperation` |
| Select `ID - 1, ID * 2, ID / 2` for ID 5101 | SUBTRACT/SUBTRACT BIGINT, MULTIPLY/MULTIPLY BIGINT, DIVIDE/DIVIDE BIGINT; 5100, 10202, 2550 | `Unsupported or unexpected operation code 10202 in processOperation` |
| Select `ID + 1 - 2 AS SHIFTED, 3 * ID` for ID 5101 | SUBTRACT/SHIFTED BIGINT, MULTIPLY/MULTIPLY BIGINT; 5100, 15303 | `Unsupported or unexpected operation code 0 in processOperation` |
| Select `RDB$FIELD_POSITION + 1` for the NAME column of T_PLAIN | ADD/ADD BIGINT; 2 | `Unsupported or unexpected operation code 2 in processOperation` |
| Select `ID, ID + 10` for both rows, ordered by ID | ID/ID INTEGER, ADD/ADD BIGINT; (5101, 5111), (5102, 5112) | `Unsupported or unexpected operation code 5111 in processOperation` |
| Delete the two inserted rows on a new connection | 2 rows deleted | 2 rows deleted |
Each arithmetic query independently fails while reading its result, even on a fresh connection.
The successful DELETE confirms that cleanup works on a new connection.
Jaybird 5.0.13 and 6.0.7 produced the same results.
### Reason analysis
1. [FirebirdPrepareStatementCommandExecutor.processExpressionProjection](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L414-L423) determines a binary expression's result description from the operator alone.
[Its getOperationType method](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L467-L474) returns JDBC type 4 (INTEGER) for every operator other than concatenation, without considering operand types.
[Its getOperationName method](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L458-L465) returns the operator symbol instead of the arithmetic column name.
2. [FirebirdBinaryRowBuilder.build](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/FirebirdBinaryRowBuilder.java#L43-L48) encodes each result value using the backend JDBC type.
For `ID + 1`, the advertised four-byte width therefore disagrees with the actual eight-byte value.
3. Firebird derives arithmetic types from the operands in [ArithmeticNode::makeDialect3](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1022-L1325), including the [multiplication result table](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/dsc.cpp#L832-L850).
[Arithmetic operation names](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.h#L59-L80) also differ from the operator symbols.
A fix must keep the result description consistent with the returned values; assigning BIGINT to every arithmetic expression is not sufficient.
Related work includes [issue #40275](https://github.com/apache/shardingsphere/issues/40275) for timestamp arithmetic and [PR #39759](https://github.com/apache/shardingsphere/pull/39759) for result descriptions and row encoding.
This report concerns integer arithmetic and does not assume that those changes resolve it.
#### Previous implementation attempt
[PR #40210](https://github.com/apache/shardingsphere/pull/40210) attempted to fix this issue.
I am opening this issue to hand the task over to the community and intend to close that PR because I can no longer reliably track its accumulated changes.
The PR and its reviews provide implementation history, but its current patch should not be treated as a completed fix.
The [review discussion](https://github.com/apache/shardingsphere/pull/40210#pullrequestreview) identifies regression cases to retain when implementing a new fix.
In particular, the actual outer select list must determine the returned column descriptors, and quoted aliases differing only in case must remain distinct and ordered.
Examples to cover in addition to the reproduction are:
```sql
SELECT 1 FROM (SELECT ID + 1 AS X FROM T_PLAIN) D;
SELECT * FROM (SELECT 1 AS "x", 2147483648 AS "X" FROM T_PLAIN) D;
```
The first query must retain its INTEGER outer result; the second must retain both columns and their respective INTEGER and BIGINT types.
Tests should also cover integer literals, SMALLINT and BIGINT operands, nested arithmetic, aliases and system-table columns according to Firebird's actual type rules.
### Steps to reproduce
#### Environment
Firebird server 5.0.
Clients: Jaybird 5.0.13 and 6.0.7.
#### Steps
1. Create a dedicated test database using SQL dialect 3, then create this table directly on Firebird before starting the proxy:
```sql
CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20));
COMMIT;
```
2. Install the Jaybird driver in the proxy's driver directory and start ShardingSphere-Proxy with the following configuration, replacing connection placeholders.
`global.yaml`
```yaml
authority:
users:
- user: root@%
password: root
privilege:
type: ALL_PERMITTED
props:
proxy-frontend-database-protocol-type: Firebird
```
`database-firebird.yaml`
```yaml
databaseName: firebird
dataSources:
ds_0:
url: jdbc:firebirdsql://<server-host>:<server-port>/<database-path>
username: <database-user>
password: <database-password>
rules:
- !SINGLE
tables:
- "*.*"
```
3. Pass the connection URL, user and password to the example and run it once directly against Firebird and once through the proxy.
Each SELECT and the final DELETE must open their own connection.
Compare the results with the table under Actual behavior.
The program deletes and inserts IDs 5101 and 5102, so use the dedicated test table.
```sh
java -cp jaybird-6.0.7.jar ArithmeticExpressionDescribe.java "jdbc:firebirdsql://<server-host>:<server-port>/<database-path>" "<database-user>" "<database-password>"
java -cp jaybird-6.0.7.jar ArithmeticExpressionDescribe.java "jdbc:firebirdsql://<proxy-host>:<proxy-port>/firebird" root root
```
### Example code
This reproduction was run directly against Firebird and through the proxy on both client versions listed above.
It prints column descriptions before reading rows, then prints values or errors; each SELECT and the final DELETE use a fresh connection.
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ArithmeticExpressionDescribe {
static final String[] QUERIES = {
"SELECT ID + 1 FROM T_PLAIN WHERE ID = 5101",
"SELECT ID - 1, ID * 2, ID / 2 FROM T_PLAIN WHERE ID = 5101",
"SELECT ID + 1 - 2 AS SHIFTED, 3 * ID FROM T_PLAIN WHERE ID = 5101",
"SELECT RDB$FIELD_POSITION + 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'T_PLAIN' AND RDB$FIELD_NAME = 'NAME'",
"SELECT ID, ID + 10 FROM T_PLAIN WHERE ID BETWEEN 5101 AND 5102 ORDER BY ID"};
public static void main(final String[] args) throws Exception {
String url = args[0];
Properties props = new Properties();
props.setProperty("user", args[1]);
props.setProperty("password", args[2]);
props.setProperty("encoding", "UTF8");
props.setProperty("wireCrypt", "DISABLED");
props.setProperty("soTimeout", "15000");
try (Connection connection = DriverManager.getConnection(url, props)) {
cleanup(connection);
update(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (5101, 'a')");
update(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (5102, 'b')");
}
for (String sql : QUERIES) {
try (Connection connection = DriverManager.getConnection(url, props)) {
select(connection, sql);
}
}
try (Connection connection = DriverManager.getConnection(url, props)) {
update(connection, "DELETE FROM T_PLAIN WHERE ID BETWEEN 5101 AND 5102");
}
}
/**
* Deletes the rows of the scenario without printing the count: a run through a proxy that breaks the connection on the first query leaves them in the table.
*/
static void cleanup(final Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
statement.executeUpdate("DELETE FROM T_PLAIN WHERE ID BETWEEN 5101 AND 5102");
}
}
static void update(final Connection connection, final String sql) {
try (Statement statement = connection.createStatement()) {
System.out.println(sql + " -> " + statement.executeUpdate(sql));
} catch (final SQLException ex) {
System.out.println(sql + " -> " + error(ex));
}
}
static void select(final Connection connection, final String sql) {
try (Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(sql)) {
ResultSetMetaData metaData = rs.getMetaData();
StringBuilder result = new StringBuilder();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
result.append(1 == i ? "" : ", ").append(metaData.getColumnName(i)).append('/').append(metaData.getColumnLabel(i)).append(' ').append(metaData.getColumnTypeName(i)).append(" scale ").append(metaData.getScale(i));
}
System.out.println("DESCRIBE " + sql + " -> " + result);
result.append(';');
while (rs.next()) {
result.append(" [");
for (int i = 1; i <= metaData.getColumnCount(); i++) {
result.append(1 == i ? "" : ", ").append('\'').append(rs.getString(i)).append('\'');
}
result.append(']');
}
System.out.println(sql + " -> " + result);
} catch (final SQLException ex) {
System.out.println(sql + " -> " + error(ex));
}
}
static String error(final SQLException ex) {
return "error " + ex.getErrorCode() + " " + String.valueOf(ex.getMessage()).split("\n")[0].replaceAll(" \\[SQLState.*", "");
}
}
```
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Firebird Proxy closes the connection when selecting integer arithmetic expressions [shardingsphere]
Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on issue #40356:
URL: https://github.com/apache/shardingsphere/issues/40356#issuecomment-6379469715
Hi, I'd like to work on this. In FirebirdPrepareStatementCommandExecutor, getOperationType/getOperationName describe +, -, *, / as INTEGER named after the operator, so I'd return BIGINT with ADD/SUBTRACT/MULTIPLY/DIVIDE names and add tests. I can open a PR within 3 days.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org