apache
JDBC datasource and table sharded by one column will route ERROR
Which version of ShardingSphere did you use?
5.3.1
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
Expected behavior
Database DSS1 only has odd tables Database DSS0 only has even numbered tables
demo_table:
# Omitted
actual-data-nodes: dss1.demotable1,demotable3,5 ...., dss0.demotable0,2,4,....
database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: db-demo_table
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: table-demo_table
db-demo_table:
type: INLINE
props:
algorithm-expression: demotable_${id % 64}
table-demo_table:
type: INLINE
props:
algorithm-expression: dss${id % 2}
When i use the SQL
select * from demotable where id in(1,2,3,4)
The sharding will throw the exception ,can not find demotable2
Actual SQL:
select * from dss1.demotable0~64 where id in(1,2,3,4)
select * from dss0.demotable0~64 where id in(1,2,3,4)
Expected behavior:
select * from dss1.demotable1,3,5... where id in(1,3)
select * from dss0.demotable0,2,4... where id in(2,4)
-- OR
select * from dss1.demotable1,3,5... where id in(1,2,3,4)
select * from dss0.demotable0,2,4... where id in(1,2,3,4)
I think it's because ShadingJDBC first look for the indicated sub table route, then execute SQL, and then look for the sub database route
@zt9788
Your expression in db-demo_table is demotable_${id % 64}, maybe what you want is demotable_${id % 2 % 64}?
Your expression in
db-demo_tableisdemotable_${id % 64}, maybe what you want isdemotable_${id % 2 % 64}?
No
for example id = 100
the will save the data in DS0.demotable_36
id = 101 DS1.demotable_37
It is DS${id%2}.demotable_${id%64}
@RaigorJiang
To supplement the question, the bug only appears when using where id in(...), while it works correctly with where id=.
SQL:
Actual SQL: dss1 ::: select distinct max(uid) as id,id,max(...)..... from demotable_xx
WHERE (uid = ? AND id IN (?,?,?,?,?,?)) GROUP BY uid,id ORDER BY create_date DESC
