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/08/18 11:35:20 UTC
[GitHub] [shardingsphere] 291277058 opened a new issue #6918: Discussion:Will shardingsphere support update sql with table select function
291277058 opened a new issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918
[#6551](https://github.com/apache/shardingsphere/issues/6551)
I read the source code and than I found that shardingsphere does not support table select when executed update sql.
@ see `OptimizeEngineFactory`
Every time update the entity the update sql execute in each table,that is not nesscessary and can be avoid.
So , I think we can change the OptimizeEngine code and support the update sharding function,that would be very nice.
----------------------------------------------------------------
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] 291277058 edited a comment on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-683626235
> But if the value of the sharding key is updated but still exists in the original instance (No data migration), then this row will be `dead`. Since users can not get this record forever.
User can realize it just like I did.Forbiden will sacrifice freedom of application possibility.Now I has problem with 4.1.1 and after version.I can not update entity with jpa method becauth the sql contains shardingkey. If user can not realize they should use @Query annotation , they will block here and difficult to go farther, and they will think why they can't use `save` method, even more they think use the `save` method will not cause any problem as they nerver change the shardingkey.
A compromise approach，use the entity manager , compare the cache entity's shardingkey value.If value changed forbiden the sql commit.
----------------------------------------------------------------
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] 291277058 commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-676032179
> @291277058 Hi thanks for your discussion.
> Sorry, but I can not catch you with these...fragmented expressions.
> What I got currently is that you want to avoid all-routing even though there is no `sharding key` in where condition, right?
> But I can not get your explanation for the solution; I mean how to get it.
>
> If English communication is a little bit difficult for you, maybe some key code clips will be better for us to understand. Or welcome your PR.
>
> Best,
> Trista
Not correctly,but very close. I want avoid all routing when the sql is `update **** where id=*`.
this sql can contain `sharding key` like `update * set shardingkey=* * where id=*`.
I think it canbe down so I open the issue.
----------------------------------------------------------------
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] 291277058 commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-681353605
@tristaZero @terrymanu
Here is my [example](https://github.com/apache/shardingsphere/compare/4.1.1...291277058:4.1.1-dev) for 4.1.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] 291277058 edited a comment on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-681353605
@tristaZero @terrymanu
Here is my [example](https://github.com/apache/shardingsphere/compare/master...291277058:master) for 5.0.0-RC1-SNAPSHOT
----------------------------------------------------------------
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] 291277058 commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-677031025
I can understand，And as I explain before shardingkey should not update value(not forbiden update in the sql,just forbiden update the value of the sharding key). Value change will cause item maybe move to another table, shardingkey definitely not allow change value,but not forbiden to update.
Why I want to update the field in sql and not declear it after the `where` word.
In jpa api save method generate the sql, and define the sql by self sometimes is not nessary ,or not easy to go.
I still insist on my point,update sql canbe special like insert,and need handle alone.
----------------------------------------------------------------
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 #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-676994977
Hi@291277058
Thanks for your clarification. Now, much clear.
However, that will get the wrong route result, IMO.
Suppose the sharding algorithm is mod(2) for `sharding key`, then `A` DB is for records with even sharding key, and `B` DB is for those with odd sharding key.
Let's take `set shardingKey= 1 where id = 0` for example.
There may be a record whose id =0 and shardingKey = 0 (See, target stored in `A` DB). If we use`shardingKey= 1` for routing, this SQL will be routed to `B` DB. As a result, no record is updated.
What's more,`ShardingUpdateStatementValidator` tells us that updating `sharding-key` is not permitted.
I am not sure my explanation is what you expected. If not, please comment here.
----------------------------------------------------------------
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 #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-675877576
@291277058 Hi thanks for your discussion.
Sorry, but I can not catch you with these...fragmented expressions.
What I got currently is that you want to avoid all-routing even though there is no `sharding key` in where condition, right?
But I can not get your explanation for the solution; I mean how to get it.
If English communication is a little bit difficult for you, maybe some key code clips will be better for us to understand. Or welcome your PR.
Best,
Trista
----------------------------------------------------------------
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] 291277058 edited a comment on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-676032179
> @291277058 Hi thanks for your discussion.
> Sorry, but I can not catch you with these...fragmented expressions.
> What I got currently is that you want to avoid all-routing even though there is no `sharding key` in where condition, right?
> But I can not get your explanation for the solution; I mean how to get it.
>
> If English communication is a little bit difficult for you, maybe some key code clips will be better for us to understand. Or welcome your PR.
>
> Best,
> Trista
Not correctly,but very close. I want avoid all routing when the sql is `update **** where id=*`.
this sql can contain `sharding key` like `update * set shardingkey=* * where id=*`.
I think it canbe down so I open the issue.
I am trying use the newest version, as you want, I can try to commit a PR to discuess about it.
Sorry for my english, and have a nice day.
----------------------------------------------------------------
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] 291277058 edited a comment on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-683626235
> But if the value of the sharding key is updated but still exists in the original instance (No data migration), then this row will be `dead`. Since users can not get this record forever.
User can realize it just like I did.Forbiden will sacrifice freedom of application possibility.Now I has problem with 4.1.1 and after version.I can not update entity with jpa method becauth the sql contains shardingkey. If user can not realize they should use `@Query` annotation , they will block here and difficult to go farther, and they will think why they can't use `save` method, even more they think use the `save` method will not cause any problem as they nerver change the shardingkey.
A compromise approach，use the entity manager , compare the cache entity's shardingkey value.If value changed forbiden the sql commit.
----------------------------------------------------------------
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] terrymanu commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-675565526
What's mean table select function? Could you give a example?
----------------------------------------------------------------
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 #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-683601477
But if the value of the sharding key is updated but still exists in the original instance (No data migration), then this row will be `dead`. Since users can not get this record forever.
----------------------------------------------------------------
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] 291277058 commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-683357827
> @291277058 Hi Thanks for your explanation and example. I guess you did a lot of work on this issue.
> To value your work, I wanna catch your point. How to understand **shardingkey should not update value(not forbiden update in the sql,just forbiden update the value of the sharding key)**?
>
> If setting sharding key appears in the update items instead of where-condition like `set shardingID=xxx where 1=1`, is this SQL expected to be executed?
Yes,it should be execude, there is no reason to block sql commit, even canbe hanppened like want update shardingkey,but the value still route into the same table.So of cause expected to be executed.Deside execute or not should in the bessnies application.Not in base compoment,Shardingsphere should simple as it canbe.
Thanks for your kindness.This is a disscuess,it useful for me and my work is always be valueful for my self.
----------------------------------------------------------------
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] 291277058 commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-675811968
When I update a entity ,the logic sql look like this
`update order_item set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=?`
It canbe understand that sql will execute in all table shard, I set other field as the key to sharding table,not the id.
But update sql is special,it can be shard by the updated field,sharding field value is expectd not be change.Of course if not set the shard field the sharding function not work.(which I called table select function).
Now, when I execute update, I got the following result.
Update sql almost like insert sql , but insert can work within expewctations, why not the update.
`if (sqlStatement instanceof InsertStatement) {
return new InsertOptimizeEngine(shardingRule, (InsertStatement)sqlStatement, parameters, generatedKey);
} else {
return !(sqlStatement instanceof SelectStatement) && !(sqlStatement instanceof DMLStatement) ? new QueryOptimizeEngine(sqlStatement.getRouteConditions().getOrCondition(), parameters) : new QueryOptimizeEngine(sqlStatement.getRouteConditions().getOrCondition(), parameters);
}`
In my standpoint, I think it canbe optimized by execute only one sql.
Add UpdateOptimizeEngine maybe can slove 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 closed issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918
----------------------------------------------------------------
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 #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918
----------------------------------------------------------------
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] 291277058 edited a comment on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 edited a comment on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-675811968
When I update a entity ,the logic sql look like this
`update order_item set app_id=?, create_date=?, order_info=?, orderno=?, original_price=?, pay_date=?, pay_source=?, price=?, product_list=?, source_orderno=?, status=?, user_id=? where id=?`
It canbe understand that sql will execute in all table shard, I set other field as the key to sharding table,not the id.
But update sql is special,it can be shard by the updated field,sharding field value is expectd not be change.Of course if not set the shard field the sharding function not work.(which I called table select function).
Now, when I execute update, I got the following result.
Update sql almost like insert sql , but insert can work within expewctations, why not the update.
`if (sqlStatement instanceof InsertStatement) {
return new InsertOptimizeEngine(shardingRule, (InsertStatement)sqlStatement, parameters, generatedKey);
} else {
return !(sqlStatement instanceof SelectStatement) && !(sqlStatement instanceof DMLStatement) ? new QueryOptimizeEngine(sqlStatement.getRouteConditions().getOrCondition(), parameters) : new QueryOptimizeEngine(sqlStatement.getRouteConditions().getOrCondition(), parameters);
}`
In my standpoint, I think it canbe optimized by execute only one sql.
Add UpdateOptimizeEngine maybe can slove 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] tristaZero commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-683287425
@291277058 Hi Thanks for your explanation and example. I guess you did a lot of work on this issue.
To value your work, I wanna catch your point. How to understand **shardingkey should not update value(not forbiden update in the sql,just forbiden update the value of the sharding key)**?
If setting sharding key appears in the update items instead of where-condition like `set shardingID=xxx where 1=1`, is this SQL expected to be executed?
----------------------------------------------------------------
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] 291277058 commented on issue #6918: Discussion:Will shardingsphere support update sql with table select function
Posted by GitBox <gi...@apache.org>.
291277058 commented on issue #6918:
URL: https://github.com/apache/shardingsphere/issues/6918#issuecomment-683626235
> But if the value of the sharding key is updated but still exists in the original instance (No data migration), then this row will be `dead`. Since users can not get this record forever.
User can realize it just like I did.Forbiden will sacrifice freedom of application possibility.Now I has problem with 4.1.1 and after version.I can update entity with jpa method becauth the sql contains shardingkey. If user can not realize they should use @Query annotation , they will block here and difficult to go farther, and they will think why they can't use `save` method, even more they think use the `save` method will not cause any problem as they nerver change the shardingkey.
A compromise approach，use the entity manager , compare the cache entity's shardingkey value.If value changed forbiden the sql commit.
----------------------------------------------------------------
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