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 2021/05/06 10:21:21 UTC
[GitHub] [shardingsphere] DongJigong opened a new issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
DongJigong opened a new issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
should not check sql which does not contains sharding tables
### Actual behavior
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-841741298
@DongJigong Thank you very much for your feedback. At present, ShardingSphere is not perfect in handling temporary table aliases. I will try to fix this problem in the master branch.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] DongJigong commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
DongJigong commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-833998597
> The subqery not support, or try the mater branch version.
> Pls Reference the [documents](https://shardingsphere.apache.org/document/current/en/features/sharding/use-norms/sql/)
I think these sqls that don't contains sharding tables shall not check grammer and let pass.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-841741298
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-841745745
@DongJigong With 4.1.1 version, you can rewrite sql like this:
```sql
SELECT COUNT(DISTINCT device_code) AS devSum, SUM(qr_num) AS portNum
, SUM(CASE
WHEN state = 0 THEN qr_num
END) AS unUseQrNum, SUM(CASE
WHEN state = 1 THEN qr_num
END) AS useQrNum
, status AS status
FROM (
SELECT dev.device_code, dev.status, COUNT(ite.qr_num) AS qr_num, ite.state
, dev.org_id
FROM charge_match_device dev
LEFT JOIN charge_match_device_items ite ON dev.device_code = ite.device_code
GROUP BY dev.device_code, ite.state
) d
WHERE org_id = 1
OR org_id IN (
SELECT c.org_id
FROM sys_org c
WHERE FIND_IN_SET(1, c.ancestors)
)
GROUP BY status;
```
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] avalon5666 commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
avalon5666 commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-834000508
Try [Hint](https://shardingsphere.apache.org/document/current/en/features/sharding/concept/hint/)
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] DongJigong commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
DongJigong commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-841748171
> @DongJigong With 4.1.1 version, you can rewrite sql like this:
>
> ```sql
> SELECT COUNT(DISTINCT device_code) AS devSum, SUM(qr_num) AS portNum
> , SUM(CASE
> WHEN state = 0 THEN qr_num
> END) AS unUseQrNum, SUM(CASE
> WHEN state = 1 THEN qr_num
> END) AS useQrNum
> , status AS status
> FROM (
> SELECT dev.device_code, dev.status, COUNT(ite.qr_num) AS qr_num, ite.state
> , dev.org_id
> FROM charge_match_device dev
> LEFT JOIN charge_match_device_items ite ON dev.device_code = ite.device_code
> GROUP BY dev.device_code, ite.state
> ) d
> WHERE org_id = 1
> OR org_id IN (
> SELECT c.org_id
> FROM sys_org c
> WHERE FIND_IN_SET(1, c.ancestors)
> )
> GROUP BY status;
> ```
Thanks. I have rewrite my sql to solve this problem. I hope that problems like this can be solved quickly.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] DongJigong commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
DongJigong commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-834008558
> Try [Hint](https://shardingsphere.apache.org/document/current/en/features/sharding/concept/hint/)
HintManager won't help me. I am not sure if Hint can resole my problem. In my option, it only strongly use sharding rule,but my sql shall pass without sharding rule,because sql desn't contains sharding 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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] avalon5666 commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
avalon5666 commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-833986951
The subqery not support, or try the mater branch version.
Pls Reference the [documents](https://shardingsphere.apache.org/document/current/en/features/sharding/use-norms/sql/)
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] DongJigong commented on issue #10257: route sql tables do not have any sharding table,should let pass,do not check sql.
Posted by GitBox <gi...@apache.org>.
DongJigong commented on issue #10257:
URL: https://github.com/apache/shardingsphere/issues/10257#issuecomment-841748171
> @DongJigong With 4.1.1 version, you can rewrite sql like this:
>
> ```sql
> SELECT COUNT(DISTINCT device_code) AS devSum, SUM(qr_num) AS portNum
> , SUM(CASE
> WHEN state = 0 THEN qr_num
> END) AS unUseQrNum, SUM(CASE
> WHEN state = 1 THEN qr_num
> END) AS useQrNum
> , status AS status
> FROM (
> SELECT dev.device_code, dev.status, COUNT(ite.qr_num) AS qr_num, ite.state
> , dev.org_id
> FROM charge_match_device dev
> LEFT JOIN charge_match_device_items ite ON dev.device_code = ite.device_code
> GROUP BY dev.device_code, ite.state
> ) d
> WHERE org_id = 1
> OR org_id IN (
> SELECT c.org_id
> FROM sys_org c
> WHERE FIND_IN_SET(1, c.ancestors)
> )
> GROUP BY status;
> ```
Thanks. I have rewrite my sql to solve this problem. I hope that problems like this can be solved quickly.
--
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org