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/01/03 03:01:31 UTC
[GitHub] [incubator-shardingsphere] joewee opened a new issue #3856: Parse
`ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
joewee opened a new issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856
## Bug Report
when execute sql that likes follow
insert into product_source_list (product_code,store_code,primary_supplier,condition_no,supplier_code,return_mark,purchase_price,price_unit,order_unit,article_no,begin_time,expiration_time,type,creation_date,issued_time,external_id,archived,version) values (?, ?,?, ?,?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,0) on duplicate key update product_code = if(@update_condition :=(issued_time < values(issued_time)),values(product_code),product_code),store_code = if(@update_condition,values(store_code),store_code),supplier_code = if(@update_condition,values(supplier_code),supplier_code),return_mark = if(@update_condition,values(return_mark),return_mark),purchase_price = if(@update_condition,values(purchase_price),purchase_price),price_unit = if(@update_condition,values(price_unit),price_unit),order_unit = if(@update_condition,values(order_unit),order_unit),primary_supplier = if(@update_condition,values(primary_supplier),primary_supplier),article_no = if(@update_condition,values(article_no),article_no),begin_time = if(@update_condition,values(begin_time),begin_time),expiration_time = if(@update_condition,values(expiration_time),expiration_time),type = if(@update_condition,values(type),type),archived = if(@update_condition,values(archived),archived),external_id = if(@update_condition,values(external_id),external_id),version = if(@update_condition,version +1,version),issued_time = if(@update_condition,values(issued_time),issued_time)
occurs IllegaStateException
details:
java.lang.IllegalStateException
at com.google.common.base.Preconditions.checkState(Preconditions.java:158)
at org.apache.shardingsphere.core.parse.core.extractor.util.ExtractorUtils.getFirstChildNode(ExtractorUtils.java:48)
at org.apache.shardingsphere.core.parse.core.extractor.impl.common.column.ColumnExtractor.getColumnSegment(ColumnExtractor.java:45)
at org.apache.shardingsphere.core.parse.core.extractor.impl.common.column.ColumnExtractor.extract(ColumnExtractor.java:41)
at org.apache.shardingsphere.core.parse.core.extractor.impl.common.column.ColumnsExtractor.extract(ColumnsExtractor.java:44)
at org.apache.shardingsphere.core.parse.core.extractor.SQLSegmentsExtractorEngine.extract(SQLSegmentsExtractorEngine.java:52)
at org.apache.shardingsphere.core.parse.core.SQLParseKernel.parse(SQLParseKernel.java:62)
at org.apache.shardingsphere.core.parse.SQLParseEngine.parse0(SQLParseEngine.java:71)
at org.apache.shardingsphere.core.parse.SQLParseEngine.parse(SQLParseEngine.java:53)
at org.apache.shardingsphere.core.route.router.masterslave.MasterSlaveRouter.route(MasterSlaveRouter.java:56)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.MasterSlavePreparedStatement.<init>(MasterSlavePreparedStatement.java:63)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.MasterSlavePreparedStatement.<init>(MasterSlavePreparedStatement.java:51)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.MasterSlaveConnection.prepareStatement(MasterSlaveConnection.java:76)
at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1555)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:935)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:365)
at com.cbest.platform.product.core.service.impl.ProductSourceListServiceImpl.saveOrUpdateIfNecessary(ProductSourceListServiceImpl.java:127)
at com.cbest.platform.product.core.service.impl.ProductSourceListServiceImpl$$FastClassBySpringCGLIB$$78117739.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.cbest.platform.product.core.service.impl.ProductSourceListServiceImpl$$EnhancerBySpringCGLIB$$d043c704.saveOrUpdateIfNecessary(<generated>)
at com.cbest.platform.product.job.SyncSourceListJob.execute(SyncSourceListJob.java:88)
at com.xxl.job.core.thread.JobThread.run(JobThread.java:152)
### Which version of ShardingSphere did you use?
4.0.0-RC3
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
### Expected behavior
insert into product_source_list (product_code,store_code,primary_supplier,condition_no,supplier_code,return_mark,purchase_price,price_unit,order_unit,article_no,begin_time,expiration_time,type,creation_date,issued_time,external_id,archived,version) values (?, ?,?, ?,?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,0) on duplicate key update product_code = if(@update_condition :=(issued_time < values(issued_time)),values(product_code),product_code),store_code = if(@update_condition,values(store_code),store_code),supplier_code = if(@update_condition,values(supplier_code),supplier_code),return_mark = if(@update_condition,values(return_mark),return_mark),purchase_price = if(@update_condition,values(purchase_price),purchase_price),price_unit = if(@update_condition,values(price_unit),price_unit),order_unit = if(@update_condition,values(order_unit),order_unit),primary_supplier = if(@update_condition,values(primary_supplier),primary_supplier),article_no = if(@update_condition,values(article_no),article_no),begin_time = if(@update_condition,values(begin_time),begin_time),expiration_time = if(@update_condition,values(expiration_time),expiration_time),type = if(@update_condition,values(type),type),archived = if(@update_condition,values(archived),archived),external_id = if(@update_condition,values(external_id),external_id),version = if(@update_condition,version +1,version),issued_time = if(@update_condition,values(issued_time),issued_time)
should execute successfully
### 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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] SteNicholas closed issue #3856: Parse
`ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
SteNicholas closed issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] SteNicholas commented on issue #3856:
Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
SteNicholas commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-574484477
> Hi, is there any update about this issue ? @SteNicholas @terrymanu
Sorry for the delay, and today I would validate the 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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] joewee commented on issue #3856: Parse
`ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
joewee commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-574542715
> > Hi, is there any update about this issue ? @SteNicholas @terrymanu
>
> Sorry for the delay, and today I would validate the 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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] SteNicholas commented on issue #3856:
Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
SteNicholas commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-575416627
@joewee Could you please provide small SQL to me for validation?
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] SteNicholas commented on issue #3856:
Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
SteNicholas commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-577000714
@joewee This SQL has no problem. @xiyelife has aleady helped to test this.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] joewee commented on issue #3856: Parse
`ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
joewee commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-575551814
> @joewee Could you please provide small SQL to me for validation?
@SteNicholas Yes, of course! Please try the following small SQL
insert into product_sharding (product_code,brand_code,name,update_time,archived,version) values (?,?, ?,?, ?, 0) on duplicate key update brand_code = if(@update_condition :=(update_time < values(update_time)),values(brand_code),brand_code),name = if(@update_condition ,values(name),name),archived = if(@update_condition,values(archived),archived),version = if(@update_condition,version +1,version),update_time = if(@update_condition,values(update_time),update_time)
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] joewee commented on issue #3856: Parse
`ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
joewee commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-574463572
Hi, is there any update about this issue ? @SteNicholas @terrymanu
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] joewee commented on issue #3856: Parse
`ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
joewee commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-575551596
> @joewee Could you please provide small SQL to me for validation?
@SteNicholas Yes, of course! Please try the following small SQL
insert into product_sharding (product_code,brand_code,`name`,update_time,archived,version) values (?,?, ?,?, ?, 0) on duplicate key update brand_code = if(@update_condition :=(update_time < values(update_time)),values(brand_code),brand_code),name = if(@update_condition ,values(name),name),archived = if(@update_condition,values(archived),archived),version = if(@update_condition,version +1,version),update_time = if(@update_condition,values(update_time),update_time)
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] joewee removed a comment on issue #3856:
Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
joewee removed a comment on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-575551596
> @joewee Could you please provide small SQL to me for validation?
@SteNicholas Yes, of course! Please try the following small SQL
insert into product_sharding (product_code,brand_code,`name`,update_time,archived,version) values (?,?, ?,?, ?, 0) on duplicate key update brand_code = if(@update_condition :=(update_time < values(update_time)),values(brand_code),brand_code),name = if(@update_condition ,values(name),name),archived = if(@update_condition,values(archived),archived),version = if(@update_condition,version +1,version),update_time = if(@update_condition,values(update_time),update_time)
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3856:
Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-571154269
@SteNicholas, Thank you, assigned.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] SteNicholas commented on issue #3856:
Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
Posted by GitBox <gi...@apache.org>.
SteNicholas commented on issue #3856: Parse `ON DUPLICATE KEY UPDATE xxx= VALUES(xxx)` error
URL: https://github.com/apache/incubator-shardingsphere/issues/3856#issuecomment-571153587
@terrymanu Please assign to me for fixing. I thought that this SQL is too long, therefore I need to validate this whether is bug.
----------------------------------------------------------------
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
With regards,
Apache Git Services