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 2020/09/22 02:19:18 UTC
[GitHub] [shardingsphere] huangguojun00 opened a new issue #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
huangguojun00 opened a new issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547
I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not.
This is my sql:
`select * from table_1 a where ( a.bill_code = #{billCode} or 1=1 ) and a.gmt_modified BETWEEN #{start} and #{end}";
`
my target: I want to select data using date; but 'billcode' is the keyword of sharding db and table, at first i try to use the above sql without parentheses in order to let sharding-jdbc can route. but it dosn't work. and then i add parentheses . it worked.
this is sql log without parentheses :
2020-09-22 10:03:33.436 [main] INFO ShardingSphere-SQL - Actual SQL: ds1 ::: select * from table_1 a where a.bill_code = ? or 1=1 and a.gmt_modified BETWEEN ? and ? ::: [2, 2020-09-10 11:10:00.0, 2020-09-22 10:03:32.979]
2020-09-22 10:03:33.436 [main] INFO ShardingSphere-SQL - Actual SQL: ds1 ::: select * from table_2 a where a.bill_code = ? or 1=1 and a.gmt_modified BETWEEN ? and ? ::: [2, 2020-09-10 11:10:00.0, 2020-09-22 10:03:32.979]
2020-09-22 10:03:33.436 [main] INFO ShardingSphere-SQL - Actual SQL: ds2 ::: select * from table_1 a where a.bill_code = ? or 1=1 and a.gmt_modified BETWEEN ? and ? ::: [2, 2020-09-10 11:10:00.0, 2020-09-22 10:03:32.979]
2020-09-22 10:03:33.436 [main] INFO ShardingSphere-SQL - Actual SQL: ds2 ::: select * from table_2 a where a.bill_code = ? or 1=1 and a.gmt_modified BETWEEN ? and ? ::: [2, 2020-09-10 11:10:00.0, 2020-09-22 10:03:32.979]
this is sql log with parentheses:
2020-09-22 10:10:03.753 [main] INFO ShardingSphere-SQL - Actual SQL: ds1 ::: select * from table_1 a where ( a.bill_code = ? or 1=1 ) and a.gmt_modified BETWEEN ? and ? ::: [2, 2020-09-10 11:10:00.0, 2020-09-22 10:10:03.277]
----------------------------------------------------------------
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] huangguojun00 edited a comment on issue #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
huangguojun00 edited a comment on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696504202
> @huangguojun00 Can you provide the sharding config? I will try to reproduce this problem.
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.0.0-RC1</version>
</dependency>
database sharding strategy:
ds${(int)(Math.abs(bill_code.hashCode()) % 2 / 1 +1)}
table sharding strategy:
table_${Math.abs(bill_code.hashCode()) % 2 +1}；
actual-data-nodes:
ds${1..2}.table_${1..2}
----------------------------------------------------------------
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 #7547: why (a.bill_code = 1 or 1=1) can route to table but without parentheses not
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696517409
@huangguojun00 @kimmking I have tested similar SQL on the master branch and found that sql with parentheses have also been routed to all tables.
I think the single route result with parentheses may be a bug of version `4.0.0-RC1`, because the `(a.bill_code = #{billCode} or 1=1)` condition is always true, which means that the sharding column routing may be invalid, so sql should be routed to all tables to execute.
----------------------------------------------------------------
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] huangguojun00 commented on issue #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
huangguojun00 commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696504202
----------------------------------------------------------------
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] huangguojun00 commented on issue #7547: why (a.bill_code = 1 or 1=1) can route to table but without parentheses not
Posted by GitBox <gi...@apache.org>.
huangguojun00 commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696520648
@strongduanmu I think so
----------------------------------------------------------------
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 #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696481592
@huangguojun00 Can you provide the sharding config? I will try to reproduce this problem.
----------------------------------------------------------------
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] huangguojun00 edited a comment on issue #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
huangguojun00 edited a comment on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696504202
> @huangguojun00 Can you provide the sharding config? I will try to reproduce this problem.
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.0.0-RC1</version>
</dependency>
database sharding strategy:
ds${(int)(Math.abs(bill_code.hashCode()) % 2 / 1 +1)}
table sharding strategy:
table_${Math.abs(bill_code.hashCode()) % 2 +1}；
actual-data-nodes:
ds${1..2}.table_${1..2}
----------------------------------------------------------------
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] huangguojun00 commented on issue #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
huangguojun00 commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696504202
> @huangguojun00 Can you provide the sharding config? I will try to reproduce this problem.
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.0.0-RC1</version>
</dependency>
database sharding strategy:
ds${(int)(Math.abs(bill_code.hashCode()) % 2 / 1 +1)}
table sharding strategy:
table_${Math.abs(bill_code.hashCode()) % 2 +1}；
----------------------------------------------------------------
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] kimmking closed issue #7547: why (a.bill_code = 1 or 1=1) can route to table but without parentheses not
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547
----------------------------------------------------------------
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] kimmking closed issue #7547: why (a.bill_code = 1 or 1=1) can route to table but without parentheses not
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547
----------------------------------------------------------------
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 #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696481592
----------------------------------------------------------------
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] kimmking commented on issue #7547: why (a.bill_code = 1 or 1=1) can route to table but without parentheses not
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696509997
see this https://zhidao.baidu.com/question/150038476.html
----------------------------------------------------------------
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 #7547: I want to konw why (a.bill_code = 1 or 1=1) can route to table but without parentheses can not
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696481592
@huangguojun00 Can you provide the sharding config? I will try to reproduce this problem.
----------------------------------------------------------------
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] kimmking commented on issue #7547: why (a.bill_code = 1 or 1=1) can route to table but without parentheses not
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7547:
URL: https://github.com/apache/shardingsphere/issues/7547#issuecomment-696509997
see this https://zhidao.baidu.com/question/150038476.html
----------------------------------------------------------------
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