makssent opened a new pull request, #40209:
URL: https://github.com/apache/shardingsphere/pull/40209
Changes proposed in this pull request:
- `FirebirdPrepareStatementCommandExecutor` now describes the parameters
of a `SELECT` with `UNION` by the `WHERE` of every branch, in the order of the
statement.
- Each branch is compiled with its own `WHERE` by
[Firebird](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/pass1.cpp#L2475-L2487),
and `?` in a comparison gets the type of the other operand from
[Firebird](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/BoolNodes.cpp#L436-L462).
- The parameter description is produced in the order of the statement by
[Firebird](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L1129-L1142)
and read by
[Jaybird](https://github.com/FirebirdSQL/jaybird/blob/v6.0.6/src/main/org/firebirdsql/gds/ng/StatementInfoProcessor.java#L161-L175).
- Previously, when a `UNION` or `UNION ALL` had three or more branches
with parameters (for example, three branches `ID = ?` with `UNION ALL`), the
proxy found only the `WHERE` of the last branch and described the other
parameters as `VARCHAR` without a length. The client got `op_response` with
error `isc_random` at prepare. Now the proxy describes each parameter by the
column it is compared with in its branch, as the Firebird server does
(`INTEGER`, `INTEGER`, `INTEGER`).
<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
Table `T_PLAIN (ID INTEGER PRIMARY KEY, NAME VARCHAR(20))` has rows 5001
to 5004 with `NAME` 'a' to 'd'. A query is shown by the conditions of its
branches; a branch is `SELECT ID FROM T_PLAIN WHERE <condition>` (`SELECT ID,
NAME` in the `UNION` query). `NAME = ?` stands for `NAME = ? AND ID > 5000`,
and the queries with three or four branches end with `ORDER BY 1`. The answer
is the parameter types and the rows. Jaybird 5 and 6 give the same results.
Before the fix:
| Query | Proxy | Server | Match |
|---|---|---|---|
| `ID = ? UNION ALL ID = ?`, 5001, 5002 | INTEGER(10), INTEGER(10); rows
5001, 5002 | INTEGER(10), INTEGER(10); rows 5001, 5002 | yes |
| `ID = ? UNION ALL ID = ? UNION ALL ID = ?`, 5001, 5002, 5003 | prepare
error `isc_random` `Unknown exception.` | INTEGER(10), INTEGER(10),
INTEGER(10); rows 5001, 5002, 5003 | no |
| `ID = 5001 UNION ALL ID = ? UNION ALL ID = 5003`, 5002 | the same error
| INTEGER(10); rows 5001, 5002, 5003 | no |
| `NAME = ? UNION ID = ? UNION NAME = ?`, 'a', 5002, 'c' | the same error
| VARCHAR(20), INTEGER(10), VARCHAR(20); rows (5001, a), (5002, b), (5003, c) |
no |
| `ID = ? UNION ALL NAME = ? UNION ALL ID = ? UNION ALL NAME = ?`, 5001,
'b', 5003, 'd' | the same error | INTEGER(10), VARCHAR(20), INTEGER(10),
VARCHAR(20); rows 5001, 5002, 5003, 5004 | no |
After the fix:
| Query | Proxy | Server | Match |
|---|---|---|---|
| `ID = ? UNION ALL ID = ?`, 5001, 5002 | INTEGER(10), INTEGER(10); rows
5001, 5002 | INTEGER(10), INTEGER(10); rows 5001, 5002 | yes |
| `ID = ? UNION ALL ID = ? UNION ALL ID = ?`, 5001, 5002, 5003 |
INTEGER(10), INTEGER(10), INTEGER(10); rows 5001, 5002, 5003 | INTEGER(10),
INTEGER(10), INTEGER(10); rows 5001, 5002, 5003 | yes |
| `ID = 5001 UNION ALL ID = ? UNION ALL ID = 5003`, 5002 | INTEGER(10);
rows 5001, 5002, 5003 | INTEGER(10); rows 5001, 5002, 5003 | yes |
| `NAME = ? UNION ID = ? UNION NAME = ?`, 'a', 5002, 'c' | VARCHAR(20),
INTEGER(10), VARCHAR(20); rows (5001, a), (5002, b), (5003, c) | VARCHAR(20),
INTEGER(10), VARCHAR(20); rows (5001, a), (5002, b), (5003, c) | yes |
| `ID = ? UNION ALL NAME = ? UNION ALL ID = ? UNION ALL NAME = ?`, 5001,
'b', 5003, 'd' | INTEGER(10), VARCHAR(20), INTEGER(10), VARCHAR(20); rows 5001,
5002, 5003, 5004 | INTEGER(10), VARCHAR(20), INTEGER(10), VARCHAR(20); rows
5001, 5002, 5003, 5004 | 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]
