PeterZaitsev opened a new issue, #40195:
URL: https://github.com/apache/shardingsphere/issues/40195
## Bug Report
### Which version of ShardingSphere did you use?
5.5.3 (binary distribution), Java 17.
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy, PostgreSQL frontend protocol.
### Expected behavior
Extended-protocol statements whose parameters are sent with type OID 0
(unspecified) are routed by their sharding value, as PostgreSQL itself accepts
them. psql `\bind` and `pgbench -M prepared` send parameters this way.
### Actual behavior
Routing fails with an exception. The point query fails like this:
```
=> SELECT v FROM t_sharded WHERE id = $1 \bind 250 \g
ERROR: Sharding value '250' must implements Comparable.
```
The INSERT fails like this:
```
=> INSERT INTO t_ins (id, v) VALUES ($1, $2) \bind 7 7 \g
ERROR: Unknown exception.
More details: java.lang.ClassCastException: class
org.apache.shardingsphere.database.protocol.postgresql.packet.command.query.extended.bind.PostgreSQLTypeUnspecifiedSQLParameter
cannot be cast to class java.lang.Comparable
```
`pgbench -M prepared` (tpcb-like) fails on its first INSERT the same way.
Adding casts (`$1::int`) did not help. Only the simple protocol (`-M simple`)
works.
### Reason analyze (If you can)
`PostgreSQLTypeUnspecifiedSQLParameter` reaches the sharding algorithm
without being converted to the column's type, and it doesn't implement
`Comparable`. Clients that Describe the statement first, so that the proxy can
infer types, are not affected.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
```yaml
rules:
- !SHARDING
tables:
t_sharded:
actualDataNodes: ds_${0..3}.t_sharded
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: by_id
t_other:
actualDataNodes: ds_${0..3}.t_other
databaseStrategy:
standard:
shardingColumn: okey
shardingAlgorithmName: by_okey
shardingAlgorithms:
by_id:
type: INLINE
props:
algorithm-expression: ds_${(id - 1).intdiv(100)}
by_okey:
type: INLINE
props:
algorithm-expression: ds_${(okey - 1).intdiv(100)}
```
4 PostgreSQL 18.6 data sources (`ds_0`..`ds_3`), rows id 1-100 / 101-200 /
201-300 / 301-400. `global.yaml`: Standalone mode,
`proxy-frontend-database-protocol-type: PostgreSQL`, XA (Atomikos), SQL
federation not enabled (default).
```sql
CREATE TABLE t_sharded (id int PRIMARY KEY, v int NOT NULL, s text);
CREATE TABLE t_other (okey int PRIMARY KEY, ref_id int NOT NULL);
-- t_sharded: id 1..400, v = id % 7, s = 'x' || id
-- t_other: okey 1..400, ref_id = (okey * 37) % 400 + 1 (points to rows
on other data sources)
```
Then run, through the proxy with psql 18:
```
SELECT v FROM t_sharded WHERE id = $1 \bind 250 \g
```
The same failure occurs with `pgbench -M prepared -s 1300` against tables
sharded with the INLINE algorithm on `aid`/`tid`/`bid`.
--
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]
