sharding sql rewrite error #21035
Description
Bug Report
Which version of ShardingSphere did you use?
shardingsphere 5.1.2
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
Expected behavior
execute jpa sql
@Modifying(clearAutomatically = true,flushAutomatically = true)
@transactional
@query(value="update mail_label set label_name = :labelName ,label_color = :labelColor, update_user_id = :updateUserId,update_user_name = :updateUserName,update_time = :updateTime where org_id = :orgId and user_id = :userId and id in( :id )", nativeQuery = true)
int upLabelNameAndColorByOrgUserAndId(@param("orgId") Integer orgId, @param("userId") Integer userId, @param("id") Long id, @param("labelName") String labelName, @param("labelColor") String labelColor, @param("updateUserId") String updateUserId, @param("updateUserName") String updateUserName,@param("updateTime") Date updateTime);
Actual behavior
the logicSQL sql show
update mail_label set create_time=?, create_user_id=?, is_delete=?, label_color=?, label_name=?, label_sort=?, org_id=?, update_time=?, update_user_id=?, user_id=? where id=?
Reason analyze (If you can)
maybe spring.jpa.open-in-view the reason
Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
When I request the interface through the id, I first query the entity through the id, and then execute the update, and the update logic sql reports an error！
When I junittest the findMailLabelById and upLabelNameAndColorByOrgUserAndId, it is working right! So,I don't know what happened！
shardingkey:
databasekey: orgid%10
tablekey:user_id%50
@query(value="select * from mail_label where org_id = :orgId and user_id = :userId and id = :id ", nativeQuery = true)
MailLabel findMailLabelById(@param("orgId") Integer orgId, @param("userId") Integer userId, @param("id") Long id);
@Modifying(clearAutomatically = true,flushAutomatically = true)
@Transactional
@Query(value="update mail_label set label_name = :labelName ,label_color = :labelColor, update_user_id = :updateUserId,update_user_name = :updateUserName,update_time = :updateTime where org_id = :orgId and user_id = :userId and id in( :id )", nativeQuery = true)
int upLabelNameAndColorByOrgUserAndId(@Param("orgId") Integer orgId, @Param("userId") Integer userId, @Param("id") Long id, @Param("labelName") String labelName, @Param("labelColor") String labelColor, @Param("updateUserId") String updateUserId, @Param("updateUserName") String updateUserName,@Param("updateTime") Date updateTime);
Example codes for reproduce this issue (such as a github link).
public Result updateMailLabel(UpdateMailLabelCondition updateMailLabelCondition)
throws Exception {
try {
MailLabel mailLabel = mailLabelRepository.findMailLabelById(NumberUtils.toInt(updateMailLabelCondition.getOrgId()),
NumberUtils.toInt(updateMailLabelCondition.getUpdateUserId()),updateMailLabelCondition.getId());
if(mailLabel != null) {
BeanUtils.copyProperties(mailLabel,updateMailLabelCondition);
mailLabel.setUpdateTime(new Date());
int count= mailLabelRepository.upLabelNameAndColorByOrgUserAndId(NumberUtils.toInt(updateMailLabelCondition.getOrgId()),
NumberUtils.toInt(updateMailLabelCondition.getUpdateUserId()),updateMailLabelCondition.getId(),updateMailLabelCondition.getLabelName(),updateMailLabelCondition.getLabelColor(),updateMailLabelCondition.getUpdateUserId(),updateMailLabelCondition.getUpdateUserName(),new Date());
updateMailLabelCondition.getUpdateUserId(),updateMailLabelCondition.getUpdateUserName(),new Date());
if(count<1){
return ResultUtil.error(0, "更新失败!!!");
}
return ResultUtil.success(1, "修改成功!!!", mailLabel);
}else {
return ResultUtil.error(0, "修改失败!!!修改对象不存在!!!");
}
} catch (Exception e) {
e.printStackTrace();
return ResultUtil.error(0, "修改失败!!!");
}
}