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/07/03 08:11:29 UTC
[GitHub] [shardingsphere] Roronoa-Zoro opened a new issue #6256: query all tables where sql contains a sharding column and non sharding column
Roronoa-Zoro opened a new issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256
## 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?
4.0.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
will only query one table
### Actual behavior
query all tables
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
I have a sql : select * from t_xx_url where xx_url=? and expire_time>?, and xx_url is sharding column, I use standard sharding algorithm, the class logic is Math.abs(xx_url.hashCode()) % 16.
### 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] lcx1989210 commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-653834307
The sharding key is not work. Is this in your first query or is it always there?
----------------------------------------------------------------
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] jacksparrow414 edited a comment on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
jacksparrow414 edited a comment on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-654164568
@Roronoa-Zoro
yes,as you say,ShardingColumn in expressions and functions will lead to full routing.
you could get more detail and unsupported SQL from [Document](https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/use-norms/sql/#%E4%B8%8D%E6%94%AF%E6%8C%81%E7%9A%84sql)
----------------------------------------------------------------
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 #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256
----------------------------------------------------------------
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] jacksparrow414 commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
jacksparrow414 commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-653458848
show you complete config,please
----------------------------------------------------------------
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] Roronoa-Zoro commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
Roronoa-Zoro commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-656008622
> @Roronoa-Zoro Hi Looking forward to your feedback on @jacksparrow414 's comment.
Thanks for you attention, I modifed table column charse.
----------------------------------------------------------------
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] jacksparrow414 commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
jacksparrow414 commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-654164568
@Roronoa-Zoro
yes,as you see,ShardingColumn in expressions and functions will lead to full routing.
you could get more detail and unsupported SQL from [Document](https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/use-norms/sql/#%E4%B8%8D%E6%94%AF%E6%8C%81%E7%9A%84sql)
----------------------------------------------------------------
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] Roronoa-Zoro commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
Roronoa-Zoro commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-654029022
> > show you complete config,please
>
> spring.shardingsphere.datasource.names=ds
>
> spring.shardingsphere.sharding.tables.t_demo.logicTable=t_demo
> spring.shardingsphere.sharding.tables.t_demo.actual-data-nodes=ds.t_demo$->{0..31}
> spring.shardingsphere.sharding.tables.t_demo.table-strategy.standard.sharding-column=demo_key
> spring.shardingsphere.sharding.tables.t_demo.table-strategy.standard.preciseAlgorithmClassName=xx.DemoTableShardingAlgorithm
>
> DemoTableShardingAlgorithm {
> private static final ConcurrentMap<String, List> tables = Maps.newConcurrentMap();
> private static final String TABLE_NAME = "demo";
>
> ```
> @Override
> public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
> String shortKey = shardingValue.getValue();
> int hashCode = Math.abs(shortKey.hashCode());
> int mode = hashCode % availableTargetNames.size();
> List<String> tableList = tables.computeIfAbsent(TABLE_NAME, k -> Lists.newArrayList(availableTargetNames));
> return tableList.get(mode);
> }
> ```
>
> }
I find the problem, select sql is 'select * from t_demo where binary short_key=#{shortKey}', this binary cause querying all tables, I guess sharding-jdbc doesn't support it, right?
----------------------------------------------------------------
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] Roronoa-Zoro commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
Roronoa-Zoro commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-654002721
> The sharding key is not work. Is this in your first query or is it always there?
I don't understand what you mean
----------------------------------------------------------------
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] Roronoa-Zoro commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
Roronoa-Zoro commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-656008081
> @Roronoa-Zoro
> yes,as you say,ShardingColumn in expressions and functions will lead to full routing.
> you could get more detail and unsupported SQL from [Document](https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/use-norms/sql/#%E4%B8%8D%E6%94%AF%E6%8C%81%E7%9A%84sql)
I update table column charset instead of using binary in sql, thank you.
----------------------------------------------------------------
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 commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-656445795
@Roronoa-Zoro Thanks for the feedback, if there are no more questions, I will close this one.
----------------------------------------------------------------
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 commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-655873322
@Roronoa-Zoro Hi Looking forward to your feedback on @jacksparrow414 's comment.
----------------------------------------------------------------
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] Roronoa-Zoro commented on issue #6256: query all tables where sql contains a sharding column and non sharding column
Posted by GitBox <gi...@apache.org>.
Roronoa-Zoro commented on issue #6256:
URL: https://github.com/apache/shardingsphere/issues/6256#issuecomment-654002460
> show you complete config,please
spring.shardingsphere.datasource.names=ds
spring.shardingsphere.sharding.tables.t_demo.logicTable=t_demo
spring.shardingsphere.sharding.tables.t_demo.actual-data-nodes=ds.t_demo$->{0..31}
spring.shardingsphere.sharding.tables.t_demo.table-strategy.standard.sharding-column=demo_key
spring.shardingsphere.sharding.tables.t_demo.table-strategy.standard.preciseAlgorithmClassName=xx.DemoTableShardingAlgorithm
DemoTableShardingAlgorithm {
private static final ConcurrentMap<String, List<String>> tables = Maps.newConcurrentMap();
private static final String TABLE_NAME = "demo";
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
String shortKey = shardingValue.getValue();
int hashCode = Math.abs(shortKey.hashCode());
int mode = hashCode % availableTargetNames.size();
List<String> tableList = tables.computeIfAbsent(TABLE_NAME, k -> Lists.newArrayList(availableTargetNames));
return tableList.get(mode);
}
}
----------------------------------------------------------------
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