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 2021/02/09 08:33:49 UTC
[GitHub] [shardingsphere] eschh19 opened a new issue #9399: problems on excute "ON DUPLICATE KEY UPDATE"
eschh19 opened a new issue #9399:
URL: https://github.com/apache/shardingsphere/issues/9399
1、Which version of ShardingSphere did you use?
shardingsphere-jdbc:5.0.0-alpha
2、Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
shardingsphere-jdbc
3、Expected behavior:
excute sql like "INSERT INTO user_info ( id, name, sex, updateTime) VALUES ( ?, ?, ?) ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),updateTime=VALUES(updateTime);",
I want the `name` colume encrypted update
4、Actual behavior:
when excute this sql,throw some exceptions like this:
Caused by: java.lang.ClassCastException: org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ExpressionProjectionSegment cannot be cast to org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment
at org.apache.shardingsphere.infra.binder.segment.insert.values.OnDuplicateUpdateContext.getValue(OnDuplicateUpdateContext.java:87) ~[shardingsphere-infra-binder-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.encrypt.rewrite.parameter.impl.EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter.lambda$rewrite$0(EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter.java:59) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]
at java.util.Optional.ifPresent(Optional.java:159) ~[?:1.8.0_73]
at org.apache.shardingsphere.encrypt.rewrite.parameter.impl.EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter.rewrite(EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter.java:58) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.encrypt.rewrite.parameter.impl.EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter.rewrite(EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter.java:40) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.encrypt.rewrite.context.EncryptSQLRewriteContextDecorator.decorate(EncryptSQLRewriteContextDecorator.java:42) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.encrypt.rewrite.context.EncryptSQLRewriteContextDecorator.decorate(EncryptSQLRewriteContextDecorator.java:34) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.lambda$decorate$0(SQLRewriteEntry.java:83) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]
at java.util.LinkedHashMap.forEach(LinkedHashMap.java:676) ~[?:1.8.0_73]
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.decorate(SQLRewriteEntry.java:83) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.createSQLRewriteContext(SQLRewriteEntry.java:76) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.rewrite(SQLRewriteEntry.java:69) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:54) ~[shardingsphere-infra-context-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:266) ~[shardingsphere-jdbc-core-5.0.0-alpha.jar:5.0.0-alpha]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:198) ~[shardingsphere-jdbc-core-5.0.0-alpha.jar:5.0.0-alpha]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.4.0.jar:3.4.0]
at com.sun.proxy.$Proxy77.execute(Unknown Source) ~[?:?]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.0.jar:3.4.0]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434) ~[mybatis-spring-1.3.0.jar:1.3.0]
... 45 more
5、Reason analyze (If you can):
when excute on line 59 in class EncryptInsertOnDuplicateKeyUpdateValueParameterRewriter and on line 87 in class OnDuplicateUpdateContext,this exception appeared, valueExpression instance of ExpressionProjectionSegment , canot be cast to
LiteralExpressionSegment
6、Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc:
the table DDL is:
CREATE TABLE `anchor_info_1` (
`id` bigint(20) NOT NULL COMMENT '主键id',
`source` varchar(16) NOT NULL COMMENT '主播来源，yy（yy）；后续扩展',
`anchorId` varchar(64) NOT NULL COMMENT '主播id',
`signVideo` tinyint(4) DEFAULT NULL COMMENT '视频直播签约，0-未签约，1-已签约',
`signAudio` tinyint(4) DEFAULT NULL COMMENT '语音直播签约，0-未签约，1-已签约',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '主播昵称',
`realName` varchar(64) NOT NULL DEFAULT '' COMMENT '主播姓名',
`idNo` varchar(20) NOT NULL DEFAULT '' COMMENT '身份证号码',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号码',
`agency` varchar(64) NOT NULL DEFAULT '' COMMENT '经纪公司',
`recommendLevel` int(11) NOT NULL DEFAULT '2' COMMENT '推荐等级',
`frontSide` varchar(1024) NOT NULL DEFAULT '' COMMENT '身份证正面',
`backSide` varchar(1024) NOT NULL DEFAULT '' COMMENT '身份证反面',
`avatar` varchar(1024) NOT NULL DEFAULT '' COMMENT '主播头像',
`sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '主播性别，0-女，1-男',
`sid` varchar(64) NOT NULL DEFAULT '' COMMENT '主频道id',
`ssid` varchar(64) NOT NULL DEFAULT '' COMMENT '子频道id',
`show` tinyint(4) NOT NULL DEFAULT '2' COMMENT '开播状态，1-开播中，2-未开播',
`showId` bigint(20) NOT NULL COMMENT '主播当前开播信息标识',
`startTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '开播时间',
`todayIn` bigint(20) NOT NULL DEFAULT '0' COMMENT '当天打赏金额,单位:分',
`last7DayIn` bigint(20) NOT NULL DEFAULT '0' COMMENT '最近7天打赏金额,单位:分',
`last14DayIn` bigint(20) NOT NULL DEFAULT '0' COMMENT '最近14天打赏金额,单位:分',
`last30DayIn` bigint(20) NOT NULL DEFAULT '0' COMMENT '最近30天打赏金额,单位:分',
`totalIn` bigint(20) NOT NULL DEFAULT '0' COMMENT '累计打赏金额,单位:分',
`dayNo` varchar(16) NOT NULL DEFAULT '' COMMENT '主播打赏数据报表日期,格式:20200801',
`elapse` bigint(20) NOT NULL COMMENT '主播开播总时长',
`times` bigint(20) NOT NULL COMMENT '主播开播总次数',
`fansNum` bigint(20) NOT NULL COMMENT '主播粉丝数量',
`selfIntro` varchar(16) NOT NULL DEFAULT '' COMMENT '个性签名',
`seatSeconds` bigint(20) NOT NULL DEFAULT '0' COMMENT '上麦总时长（单位：秒)',
`autoInviteSeat` tinyint(4) NOT NULL DEFAULT '1' COMMENT '自动邀请上麦开关，1-开启，2-关闭，默认开启',
`extend` text COMMENT '主播所有静态信息格式化的json（如id、头像、昵称）',
`zone` varchar(16) NOT NULL DEFAULT 'bjht' COMMENT '此数据产生的单元信息',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '记录状态，-1-删除，1-有效，默认为1',
`createTime` bigint(20) NOT NULL COMMENT '创建时间，精确到毫秒',
`updateTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '最近修改时间，精确到毫秒',
`lastUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最近的修改时间，服务端专用，用来爬取更新的数据',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `idx_source_anchorId` (`source`,`anchorId`) USING BTREE,
KEY `idx_createTime` (`createTime`) USING BTREE,
KEY `idx_updateTime` (`updateTime`) USING BTREE,
KEY `idx_lastUpdateTime` (`lastUpdateTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='主播信息表';
excute sql is:
<insert id="insertOrUpdateAnchorInfoStartShow" parameterType="com.oppo.cpc.live.common.app.lib.objroom.AnchorInfoModel">
INSERT INTO anchor_info
<trim prefix="(" suffix=")" suffixOverrides="," >
id, source, anchorId, signVideo, signAudio, name, avatar, sex, sid, ssid, `show`, `showId`,
<if test="startTime != null" >
startTime,
</if>
<if test="todayIn != null" >
todayIn,
</if>
<if test="last7DayIn != null" >
last7DayIn,
</if>
<if test="last14DayIn != null" >
last14DayIn,
</if>
<if test="last30DayIn != null" >
last30DayIn,
</if>
<if test="totalIn != null" >
totalIn,
</if>
<if test="dayNo != null" >
dayNo,
</if>
<if test="autoInviteSeat != 0" >
autoInviteSeat,
</if>
<if test="seatSeconds != 0" >
seatSeconds,
</if>
<if test="realName != null" >
realName,
</if>
<if test="idNo != null" >
idNo,
</if>
<if test="mobile != null" >
mobile,
</if>
<if test="agency != null" >
agency,
</if>
<if test="recommendLevel != null" >
recommendLevel,
</if>
<if test="frontSide != null" >
frontSide,
</if>
<if test="backSide != null" >
backSide,
</if>
elapse,times, fansNum, extend, zone, status, createTime, updateTime
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides="," >
#{id,jdbcType=BIGINT}, #{source,jdbcType=VARCHAR}, #{anchorId,jdbcType=VARCHAR},
#{signVideo,jdbcType=VARCHAR}, #{signAudio,jdbcType=VARCHAR},
#{name,jdbcType=VARCHAR}, #{avatar,jdbcType=VARCHAR}, #{sex,jdbcType=TINYINT},
#{sid,jdbcType=VARCHAR}, #{ssid,jdbcType=VARCHAR}, #{show,jdbcType=TINYINT},#{showId,jdbcType=BIGINT},
<if test="startTime != null" >
#{startTime,jdbcType=BIGINT},
</if>
<if test="todayIn != null" >
#{todayIn,jdbcType=BIGINT},
</if>
<if test="last7DayIn != null" >
#{last7DayIn,jdbcType=BIGINT},
</if>
<if test="last14DayIn != null" >
#{last14DayIn,jdbcType=BIGINT},
</if>
<if test="last30DayIn != null" >
#{last30DayIn,jdbcType=BIGINT},
</if>
<if test="totalIn != null" >
#{totalIn,jdbcType=BIGINT},
</if>
<if test="dayNo != null" >
#{dayNo,jdbcType=VARCHAR},
</if>
<if test="autoInviteSeat != 0" >
#{autoInviteSeat,jdbcType=INTEGER},
</if>
<if test="seatSeconds != 0" >
#{seatSeconds,jdbcType=BIGINT},
</if>
<if test="realName != null" >
#{realName,jdbcType=VARCHAR},
</if>
<if test="idNo != null" >
#{idNo,jdbcType=VARCHAR},
</if>
<if test="mobile != null" >
#{mobile,jdbcType=VARCHAR},
</if>
<if test="agency != null" >
#{agency,jdbcType=VARCHAR},
</if>
<if test="recommendLevel != null" >
#{recommendLevel,jdbcType=INTEGER},
</if>
<if test="frontSide != null" >
#{frontSide,jdbcType=VARCHAR},
</if>
<if test="backSide != null" >
#{backSide,jdbcType=VARCHAR},
</if>
#{elapse,jdbcType=BIGINT}, #{times,jdbcType=BIGINT},
#{fansNum,jdbcType=BIGINT}, #{extend,jdbcType=VARCHAR}, #{zone,jdbcType=VARCHAR},
#{status,jdbcType=TINYINT}, #{createTime,jdbcType=BIGINT}, #{updateTime,jdbcType=BIGINT}
</trim>
ON DUPLICATE KEY UPDATE <if test="realName != null">`realName`=VALUES(realName),</if>
updateTime=VALUES(updateTime);
</insert>
sharding rule configuration is:
rules:
- !ENCRYPT
tables:
anchor_info:
columns:
realName:
cipherColumn: realName
encryptorName: aes
encryptors:
aes:
type: AES
props:
aes-key-value: abcdisjckel14003c2f8ea32cc1d59e5
----------------------------------------------------------------
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] dongzl commented on issue #9399: ExpressionProjectionSegment canot be cast to LiteralExpressionSegment
Posted by GitBox <gi...@apache.org>.
dongzl commented on issue #9399:
URL: https://github.com/apache/shardingsphere/issues/9399#issuecomment-785652563
Hi @eschh19 , I will check this problem, if I want some other data, please help me, 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
[GitHub] [shardingsphere] terrymanu closed issue #9399: ExpressionProjectionSegment canot be cast to LiteralExpressionSegment
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #9399:
URL: https://github.com/apache/shardingsphere/issues/9399
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] eschh19 commented on issue #9399: ExpressionProjectionSegment canot be cast to LiteralExpressionSegment
Posted by GitBox <gi...@apache.org>.
eschh19 commented on issue #9399:
URL: https://github.com/apache/shardingsphere/issues/9399#issuecomment-782547028
sharding-jdbc cannot support this sql like "ON DUPLICATE KEY UPDATE name=VALUES(name),updateTime=VALUES(updateTime)",
change this sql to "ON DUPLICATE KEY UPDATE name=#{name},updateTime=#{updateTime}" can be successful excuted,i want to know WHY?
----------------------------------------------------------------
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 #9399: ExpressionProjectionSegment canot be cast to LiteralExpressionSegment
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #9399:
URL: https://github.com/apache/shardingsphere/issues/9399#issuecomment-927252027
No response anymore
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org