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/10 09:24:28 UTC
[GitHub] [shardingsphere] fantacy2001 opened a new issue #6753: shardingjdbc do not support case..when sql
fantacy2001 opened a new issue #6753:
URL: https://github.com/apache/shardingsphere/issues/6753
mybatis mapper sql
`<update id="updateByStockFreezeDetailList">
update stock_freeze_detail
<trim prefix="set" suffixOverrides=",">
<trim prefix="row_status=case" suffix="end,">
<foreach collection="list" index="index" item="item">
WHEN (id=#{item.id})
THEN #{item.rowStatus}
</foreach>
</trim>
<trim prefix="update_user=case" suffix="end,">
<foreach collection="list" index="index" item="item">
WHEN (id=#{item.id})
THEN #{item.updateUser}
</foreach>
</trim>
<trim prefix="update_time=case" suffix="end,">
<foreach collection="list" index="index" item="item">
WHEN (id=#{item.id})
THEN #{item.updateTime}
</foreach>
</trim>
</trim>
<trim prefix="where" prefixOverrides="AND | OR" >
and tenant_id = #{tenantId,jdbcType=VARCHAR}
and id in
<foreach collection="list" open="(" close=")" item="item" separator=",">
#{item.id}
</foreach>
</trim>
</update>`
Logic SQL: update stock_freeze_detail
set row_status=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_user=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_time=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end
where tenant_id = ?
and id in
(
?
,
?
,
?
)
Actual SQL: ds_0 ::: update stock_freeze_detail
set row_status=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_user=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_time=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end
where tenant_id = ?
and id in
(
?
,
?
,
?
) ::: [3, 2, 4, 2, 10, 2, 3, ll, 4, ll, 10, ll, 3, 2020-08-10T17:15:25.979+0800, 4, 2020-08-10T17:15:25.979+0800, 10, 2020-08-10T17:15:25.979+0800, jd, 3, 4, 10]
sharding column : tenant_id
sharding sphere vesion : 4.1.1
shardingjdbc searches for the sharding value, sorts according to the field in the where condition, and takes the array subscript. so the value of tenant_id has be finded is '3' , the parameterMarkerIndex is 0
----------------------------------------------------------------
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 #6753: shardingjdbc do not support case..when sql
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #6753:
URL: https://github.com/apache/shardingsphere/issues/6753
----------------------------------------------------------------
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] fantacy2001 commented on issue #6753: shardingjdbc do not support case..when sql
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6753:
URL: https://github.com/apache/shardingsphere/issues/6753#issuecomment-671313193
> @fantacy2001 Can you show more stack info of shardingjdbc?
2020-08-10 19:57:45 [DEBUG] [main] - org.mybatis.spring.SqlSessionUtils.getSqlSession():104 - Creating a new SqlSession
2020-08-10 19:57:45 [DEBUG] [main] - org.mybatis.spring.SqlSessionUtils.getSqlSession():140 - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@583c416c] was not registered for synchronization because synchronization is not active
2020-08-10 19:57:45 [DEBUG] [main] - org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection():110 - Fetching JDBC Connection from DataSource
2020-08-10 19:57:45 [DEBUG] [main] - org.mybatis.spring.transaction.SpringManagedTransaction.openConnection():86 - JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@4140d677] will not be managed by Spring
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockFreeze.StockFreezeDetailDao.updateByStockFreezeDetailList.debug():139 - ==> Preparing: update stock_freeze_detail set row_status=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_time=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end where tenant_id = ? and id in ( ? , ? , ? )
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockFreeze.StockFreezeDetailDao.updateByStockFreezeDetailList.debug():139 - ==> Preparing: update stock_freeze_detail set row_status=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_time=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end where tenant_id = ? and id in ( ? , ? , ? )
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockFreeze.StockFreezeDetailDao.updateByStockFreezeDetailList.debug():139 - ==> Parameters: 3(Long), 2(Integer), 4(Long), 2(Integer), 10(Long), 2(Integer), 3(Long), ll(String), 4(Long), ll(String), 10(Long), ll(String), 3(Long), 2020-08-10 19:57:45.21(Timestamp), 4(Long), 2020-08-10 19:57:45.21(Timestamp), 10(Long), 2020-08-10 19:57:45.21(Timestamp), jd(String), 3(Long), 4(Long), 10(Long)
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockFreeze.StockFreezeDetailDao.updateByStockFreezeDetailList.debug():139 - ==> Parameters: 3(Long), 2(Integer), 4(Long), 2(Integer), 10(Long), 2(Integer), 3(Long), ll(String), 4(Long), ll(String), 10(Long), ll(String), 3(Long), 2020-08-10 19:57:45.21(Timestamp), 4(Long), 2020-08-10 19:57:45.21(Timestamp), 10(Long), 2020-08-10 19:57:45.21(Timestamp), jd(String), 3(Long), 4(Long), 10(Long)
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.octopus.sharding.algorithm.mod.impl.DbModShardingAlgorithm.doSharding():33 - 获取数据库名：ds_ms0
2020-08-10 19:57:45 [INFO] [main] - ShardingSphere-SQL.log():74 - Logic SQL: update stock_freeze_detail
set row_status=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_user=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_time=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end
where tenant_id = ?
and id in
(
?
,
?
,
?
)
2020-08-10 19:57:45 [INFO] [main] - ShardingSphere-SQL.log():74 - SQLStatement: UpdateStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.UpdateStatement@1938f3b3, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@bfe3907), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@bfe3907)
2020-08-10 19:57:45 [INFO] [main] - ShardingSphere-SQL.log():74 - Actual SQL: ds_0 ::: update stock_freeze_detail
set row_status=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_user=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end,
update_time=case WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ?
WHEN (id=?)
THEN ? end
where tenant_id = ?
and id in
(
?
,
?
,
?
) ::: [3, 2, 4, 2, 10, 2, 3, ll, 4, ll, 10, ll, 3, 2020-08-10T19:57:45.210+0800, 4, 2020-08-10T19:57:45.210+0800, 10, 2020-08-10T19:57:45.210+0800, jd, 3, 4, 10]
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockFreeze.StockFreezeDetailDao.updateByStockFreezeDetailList.(): - <== Updates: 3
2020-08-10 19:57:45 [DEBUG] [main] - com.jd.jcloud.wms.inventory.dao.stockFreeze.StockFreezeDetailDao.updateByStockFreezeDetailList.debug():139 - <== Updates: 3
2020-08-10 19:57:45 [DEBUG] [main] - org.mybatis.spring.SqlSessionUtils.closeSqlSession():168 - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@583c416c]
2020-08-10 19:57:45 [DEBUG] [main] - org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection():327 - Returning JDBC Connection to DataSource
2020-08-10 19:57:45 [DEBUG] [main] - org.springframework.test.context.support.DirtiesContextTestExecutionListener.afterTestMethod():86 - After test method: context [DefaultTestContext@17497425 testClass = StockFreezeDetailDaoTest, testInstance = com.jd.jcloud.wms.dao.StockFreezeDetailDaoTest@f0da945, testMethod = testUpdateByStockFreezeDetailList@StockFreezeDetailDaoTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@4803b726 testClass = StockFreezeDetailDaoTest, locations = '{classpath*:spring/config/spring-config.xml, classpath*:spring/config/spring-config-sharding.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]], class dirties context [false], class mode [null], method dirties context [false].
----------------------------------------------------------------
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 #6753: shardingjdbc do not support case..when sql
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6753:
URL: https://github.com/apache/shardingsphere/issues/6753#issuecomment-671305110
@fantacy2001 Can you show more stack info of shardingjdbc?
----------------------------------------------------------------
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 #6753: shardingjdbc do not support case..when sql
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6753:
URL: https://github.com/apache/shardingsphere/issues/6753#issuecomment-673314295
@jingshanglu I expect your checking result as well. :)
----------------------------------------------------------------
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 #6753: shardingjdbc do not support case..when sql
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6753:
URL: https://github.com/apache/shardingsphere/issues/6753#issuecomment-671747719
Ok, i'll check it.
----------------------------------------------------------------
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