PeterZaitsev opened a new issue, #40194:
URL: https://github.com/apache/shardingsphere/issues/40194
## Bug Report
### Which version of ShardingSphere did you use?
5.5.3 (binary distribution
`apache-shardingsphere-5.5.3-shardingsphere-proxy-bin`), Java 17.
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy, PostgreSQL frontend protocol.
### Expected behavior
A prepared `SELECT id FROM t_sharded WHERE v = $1 ORDER BY s LIMIT 3`
returns **one** column, `id`, as PostgreSQL does. The merge-only column
`ORDER_BY_DERIVED_0`, which the proxy adds internally to sort the merged
result, is not sent to the client.
### Actual behavior
With the extended query protocol (Parse / Describe / Bind / Execute, as Go's
`lib/pq` and JDBC send it), the result set has **two** columns, `id` and
`order_by_derived_0`. Code that scans one value per row fails:
```
== ShardingSphere-Proxy
columns returned: [id order_by_derived_0]
105 <nil>
112 <nil>
119 <nil>
Scan into one variable: 0 sql: expected 2 destination arguments in Scan, not
1
== the same query on a PostgreSQL data source directly
columns returned: [id]
Scan into one variable: 105 <nil>
```
The same statement over the simple query protocol (psql) returns one column
correctly.
### Reason analyze (If you can)
The derived ORDER BY column is stripped from the RowDescription for simple
queries. For the extended protocol, the column list given to the client
(Describe / RowDescription) appears to come from the rewritten SQL rather than
the logical one.
### 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)
```
```go
db, _ := sql.Open("postgres", "host=127.0.0.1 port=3307 user=tpcc
password=... dbname=probe sslmode=disable") // github.com/lib/pq v1.10.9
stmt, _ := db.Prepare("SELECT id FROM t_sharded WHERE v = $1 ORDER BY s
LIMIT 3")
rows, _ := stmt.Query(0)
cols, _ := rows.Columns() // [id order_by_derived_0]
var id int
err := stmt.QueryRow(0).Scan(&id) // sql: expected 2 destination arguments
in Scan, not 1
```
### Impact
Running go-tpc TPC-C through the proxy, every by-last-name Payment (`SELECT
c_id FROM customer WHERE c_w_id = $1 AND c_d_id = $2 AND c_last = $3 ORDER BY
c_first`) failed. That is 60% of all payments.
--
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]
