PeterZaitsev opened a new issue, #40196:
URL: https://github.com/apache/shardingsphere/issues/40196
## 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
A join between non-binding sharded tables whose matching rows are on
**different** data sources, with SQL federation disabled (the default), is
either executed correctly or rejected with an error. The same applies to an `IN
(subquery)` whose rows are on other data sources. The documentation says
cross-instance joins need SQL federation.
### Actual behavior
Both statements run on each data source against only that data source's
rows, and the partial results are merged. The client gets a wrong answer and no
error or warning:
```
=> SELECT count(*) FROM t_other o JOIN t_sharded a ON a.id = o.ref_id;
count
-------
104 -- correct answer: 400 (every ref_id exists in t_sharded);
the 4 data sources return 26 each
=> SELECT count(*) FROM t_sharded WHERE id IN (SELECT ref_id FROM t_other
WHERE okey <= 10);
count
-------
2 -- correct answer: 10; the subquery is routed to ds_0, which
holds only 2 of the 10 referenced ids
```
### Reason analyze (If you can)
Without federation, non-binding tables go through the standard/Cartesian
route per data source, which can only see co-located rows. Nothing detects that
the join condition spans data sources, so a partial result is returned.
Possibly related: #38456 (routing of subqueries with non-binding tables).
### 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)
```
Run the two statements above through the proxy.
### Suggestion
When SQL federation is disabled, raise an error (e.g. "cross-data-source
join requires SQL federation") instead of returning per-data-source partial
results.
--
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]
