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/14 10:29:33 UTC
[GitHub] [shardingsphere] victormferrara opened a new issue #7440: Tuples comparision not working properly
victormferrara opened a new issue #7440:
URL: https://github.com/apache/shardingsphere/issues/7440
## Bug Report
When making a query with the sharding key in a tuple in the WHERE clause, shardingsphere is hitting all the tables as it's not able to extract the sharding key value. I'm talking using a format like: `WHERE (id, some_int) = (:id, :some_int)`, being the `id` the sharding key.
There is a workaround for this adding and additional conditional: `WHERE id = :id AND (id, some_int) = (:id, :some_int)`.
But there's a very strange behavior in the following situation: `WHERE (some_int, id) = (:some_int, :id) and id = :id`. In this case it's grabbing the value of `some_int` as sharding key, like it's messing up with the order of the fields. The result is that no row is returned, which is a bug.
The sharding configuration is very simple:
```
tableRuleConfig = new TableRuleConfiguration("sharded", "db.sharded${0..1}");
tableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "sharded${id % 2}"));
```
And the table:
```
CREATE TABLE IF NOT EXISTS `sharded1` (
`id` int(10) unsigned NOT NULL,
`some_int` int(10) unsigned,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
```
Is it expected for the tuples comparision to work? I've found nothing related in the [documentation](https://shardingsphere.apache.org/document/legacy/4.x/document/en/features/sharding/use-norms/sql/). It's specially dangerous for the last case explained as it's not crashing but it's not getting the expected value.
Thank you very much!
### Which version of ShardingSphere did you use?
4.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Sharding-JDBC with Hibernate 5.4.10.Final
### Expected behavior
Shardingsphere only hitting the table with the value.
### Actual behavior
Query sent to all the tables.
----------------------------------------------------------------
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] jingshanglu commented on issue #7440: Tuples comparision not working properly
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #7440:
URL: https://github.com/apache/shardingsphere/issues/7440#issuecomment-692654225
> Hi @victormferrara
> The query like `WHERE (id, some_int) = (:id, :some_int)` is unsupported for all the 4.x release. Though we give it parsing support for such SQLs, I can not guarantee it will work well.
>
> @jingshanglu: For 5.x release, we can not make this SQL work well either in the rewritten and route module, can we?
>
> My suggestion is to modify your SQL without `(id, some_int) = (:id, :some_int)`.
Yes, parser is ok, but maybe it is't work on route and rewrite.
----------------------------------------------------------------
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 #7440: Tuples comparision not working properly
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #7440:
URL: https://github.com/apache/shardingsphere/issues/7440
----------------------------------------------------------------
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 #7440: Tuples comparision not working properly
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7440:
URL: https://github.com/apache/shardingsphere/issues/7440#issuecomment-692435163
Hi @victormferrara
The query like `WHERE (id, some_int) = (:id, :some_int)` is unsupported for all the 4.x release. Though we give it parsing support for such SQLs, I can not guarantee it will work well.
@jingshanglu: For 5.x release, we can not make this SQL work well either in the rewritten and route module, can we?
My suggestion is to modify your SQL without `(id, some_int) = (:id, :some_int)`.
----------------------------------------------------------------
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 #7440: Tuples comparision not working properly
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7440:
URL: https://github.com/apache/shardingsphere/issues/7440#issuecomment-693147284
Thanks @victormferrara
We are working on this feature, it is not easy though. :)
----------------------------------------------------------------
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] victormferrara commented on issue #7440: Tuples comparision not working properly
Posted by GitBox <gi...@apache.org>.
victormferrara commented on issue #7440:
URL: https://github.com/apache/shardingsphere/issues/7440#issuecomment-692531690
Thank you for the quick answer!
We'll change the code to avoid the tuples comparision. That kind of syntax is automatically generated by Hibernate when comparing composite keys, so maybe it's interesting considering supporting it in the future.
Thanks!
----------------------------------------------------------------
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