cucuzi opened a new issue, #16665:
URL: https://github.com/apache/shardingsphere/issues/16665
ShardingSphere version: 5.1.0
Database: Mysql
### Properties:
```
spring:
shardingsphere:
datasource:
names: db0
props:
sql-show: true
rules:
sharding:
tables:
book:
actual-data-nodes: db0.book_$->{0..2}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: table-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
table-inline:
type: INLINE
props:
algorithm-expression: book_$->{id % 3}
```
### Logic SQL:
```
SELECT
UPPER(
LEFT ( bg.class_no, 1 )) class_char,
count(
DISTINCT ( bg.class_no )) class_num,
count( b.id ) book_num,
sum( b.price ) sum_price
FROM
book b
JOIN bibliography bg ON b.bibliography_id = bg.id
WHERE
b.user_id = ?
GROUP BY
class_char
```
Using logic sql and no sharding result:
|classChar|classNum|bookNum|sumPrice|
|--|--|--|--|
|A|2|3|89.10|
|B|2|3|121.60|
|D|2|2|38.80|
|Z|1|2|5.52|
### Actual SQL:
```
SELECT DISTINCT
UPPER(
LEFT ( bg.class_no, 1 )) class_char,
bg.class_no class_num,
count( b.id ) book_num,
sum( b.price ) sum_price
FROM
book_0 b
JOIN bibliography bg ON b.bibliography_id = bg.id
WHERE
b.user_id = ?
ORDER BY
class_char ASC
```
unexpected result:
|classChar|classNum|bookNum|sumPrice|
|--|--|--|--|
|A|2|6|202.90|
|Z|1|4|52.12|
Maybe it is a bug? I don't know. :(
Please give me some help.
The sql file:
[demo1.zip](https://github.com/apache/shardingsphere/files/8450958/demo1.zip)
--
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]
