You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/10 06:41:21 UTC
[GitHub] [shardingsphere] cucuzi opened a new issue, #16665: Generate a unexpected Actual SQL
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1186075083
It seems that the bibliography table is a single table. Currently, if the sql federation feature is not used, ShardingSphere cannot support the cross-database join query between the sharding table and the single table. @cucuzi
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] cucuzi commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
cucuzi commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1186080724
Hi @strongduanmu, the sql federation feature is mean setting sql-federation-enabled property?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1186104836
> Hi @strongduanmu, the sql federation feature is mean setting sql-federation-enabled property?
Yes, you can try it.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] cucuzi commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
cucuzi commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1186083382
@strongduanmu And the question key point why my GROUP change to ORDER. If shardingsphere-jdbc not support the sql, I will change to sharding rules.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1272350074
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] closed issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #16665: Generate a unexpected Actual SQL
URL: https://github.com/apache/shardingsphere/issues/16665
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1186074674
Hi @cucuzi , what is the configuration of bibliography table?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #16665: Generate a unexpected Actual SQL
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-1186105275
>
@cucuzi I will check the rewrite logic.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Generate a unexpected Actual SQL [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #16665:
URL: https://github.com/apache/shardingsphere/issues/16665#issuecomment-2026020823
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org