cucuzi opened a new issue, #17421:
URL: https://github.com/apache/shardingsphere/issues/17421
I select two using sharding table, but sharing execute full table routing.
I thinking one reason is sql join key is not sharding key.
**SQL**
```
SELECT
bg.*
FROM
bibliography bg
JOIN ( SELECT DISTINCT b.bibliography_id AS bibliography_id FROM book b
WHERE b.user_id = 1 ) b ON b.bibliography_id = bg.id
WHERE
bg.user_id = 1
```
**Actually Runing SQL**
```
SELECT
bg.*
FROM
bibliography_0 bg
JOIN ( SELECT DISTINCT b.bibliography_id AS bibliography_id FROM book_0 b
WHERE b.user_id = 1 ) b ON b.bibliography_id = bg.id
WHERE
bg.user_id = 1;
SELECT
bg.*
FROM
bibliography_1 bg
JOIN ( SELECT DISTINCT b.bibliography_id AS bibliography_id FROM book_1 b
WHERE b.user_id = 1 ) b ON b.bibliography_id = bg.id
WHERE
bg.user_id = 1;
SELECT
bg.*
FROM
bibliography_2 bg
JOIN ( SELECT DISTINCT b.bibliography_id AS bibliography_id FROM book_2 b
WHERE b.user_id = 1 ) b ON b.bibliography_id = bg.id
WHERE
bg.user_id = 1;
```
**Expected SQL**
```
SELECT
bg.*
FROM
bibliography_1 bg
JOIN ( SELECT DISTINCT b.bibliography_id AS bibliography_id FROM book_1 b
WHERE b.user_id = 1 ) b ON b.bibliography_id = bg.id
WHERE
bg.user_id = 1;
```
**Sharding Config**
```
spring:
shardingsphere:
rules:
sharding:
tables:
bibliography:
actual-data-nodes: db0.bibliography_$->{0..3}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: bibliography-table-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
book:
actual-data-nodes: db0.book_$->{0..3}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: book-table-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
bibliography-table-inline:
type: INLINE
props:
algorithm-expression: bibliography_$->{user_id % 3}
book-table-inline:
type: INLINE
props:
algorithm-expression: book_$->{user_id % 3}
binding-tables:
- bibliography, book
```
--
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]
