Issue 1:
TODO:

create_table_with_out_of_line_check

insert_on_duplicate_key_update_with_placeholders

select_count_with_sub_with_whitespace

select_distinct_function

update_with_case_when

update_with_where_calculation

The following SQL is not supported by SQL 92 and should be remove from the tests:

Unsupported SQL of CREATE INDEX order_index ON t_order (order_id, user_id, status)

Unsupported SQL of CREATE INDEX t_log_index ON t_log (id)

Unsupported SQL of CREATE UNIQUE INDEX t_log_index ON t_log (id)

Unsupported SQL of CREATE USER user1 IDENTIFIED BY RANDOM password default role role1

Unsupported SQL of CREATE USER user1 IDENTIFIED BY RANDOM password default role role1 PASSWORD EXPIRE ACCOUNT LOCK

Unsupported SQL of TRUNCATE TABLE t_log

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.

If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.0.0-RC1-SNAPSHOT

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Both

Expected behavior

SQL 92 parser test pass

Actual behavior

[INFO] --- maven-surefire-plugin:2.22.0:test (default-test) @ shardingsphere-sql-parser-test ---
[INFO]
[INFO] -------------------------------------------------------
[INFO] T E S T S
[INFO] -------------------------------------------------------
[INFO] Running org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest
line 1:7 no viable alternative at input 'CREATEINDEX'
line 1:7 no viable alternative at input 'CREATEINDEX'
line 1:7 no viable alternative at input 'CREATEINDEX'
line 1:7 no viable alternative at input 'CREATEINDEX'
line 1:145 no viable alternative at input 'HASH'
line 1:145 mismatched input 'HASH' expecting {LINEAR, LIST, RANGE}
line 1:7 no viable alternative at input 'CREATEUNIQUE'
line 1:7 no viable alternative at input 'CREATEUNIQUE'
line 1:7 no viable alternative at input 'CREATEUSER'
line 1:7 no viable alternative at input 'CREATEUSER'
line 1:7 no viable alternative at input 'CREATEUSER'
line 1:7 no viable alternative at input 'CREATEUSER'
line 1:0 no viable alternative at input 'TRUNCATE'
line 1:0 mismatched input 'TRUNCATE' expecting {SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, SET, COMMIT, ROLLBACK}
[ERROR] Tests run: 1899, Failures: 7, Errors: 6, Skipped: 0, Time elapsed: 7.002 s <<< FAILURE! - in org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest
[ERROR] assertSupportedSQL[create_composite_index (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.007 s <<< ERROR!
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE INDEX order_index ON t_order (order_id, user_id, status)`
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93)

[ERROR] assertSupportedSQL[create_index (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR!
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE INDEX t_log_index ON t_log (id)`
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93)

[ERROR] assertSupportedSQL[create_table_with_out_of_line_check (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.017 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : create_table_with_out_of_line_check
SQL : CREATE TABLE t_order (order_id INT, user_id INT, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10), CHECK (order_id > 0))
Constraint definitions size assertion error:

Expected: is <1>
but: was <0>
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[create_unique_index (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR!
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE UNIQUE INDEX t_log_index ON t_log (id)`
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93)

[ERROR] assertSupportedSQL[create_user_with_password (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR!
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE USER user1 IDENTIFIED BY RANDOM password default role role1`
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93)

[ERROR] assertSupportedSQL[create_user_with_password_expire_lock (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR!
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE USER user1 IDENTIFIED BY RANDOM password default role role1 PASSWORD EXPIRE ACCOUNT LOCK`
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93)

[ERROR] assertSupportedSQL[insert_on_duplicate_key_update_with_placeholders (Placeholder) -> MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : insert_on_duplicate_key_update_with_placeholders
SQL : INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status = ?
SQL Params : [1, 1, 'init', 'init']

Parameter markers count assertion error:

Expected: is <4>
but: was <5>
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[select_count_with_sub_with_whitespace (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : select_count_with_sub_with_whitespace
SQL : SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1
Common expression text assertion error:

Expected: is "1 - 1"
but: was "1-1"
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[select_distinct_function (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : select_distinct_function
SQL : SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id
Projection type assertion error:

Expected: an instance of org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.expression.ExpectedExpressionProjection
but: <org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925> is a org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[truncate_table (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR!
org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `TRUNCATE TABLE t_log`
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93)

[ERROR] assertSupportedSQL[update_with_case_when (Placeholder) -> MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : update_with_case_when
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 ? end where tenant_id = ?
SQL Params : [3, 2, 4, 2, 10, 2, 3, 'll', 4, 'll', 10, 'll', 3, '2020-08-10T17:15:25.979+0800', 'jd']

Parameter markers count assertion error:

Expected: is <15>
but: was <1>
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[update_with_where_calculation (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : update_with_where_calculation
SQL : UPDATE t_order SET status = 1 WHERE order_id = order_id - 2 AND user_id = 3
Common expression text assertion error:

Expected: is "order_id - 2"
but: was "order_id-2"
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[update_with_where_calculation (Placeholder) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE!
java.lang.AssertionError:

SQL Case ID : update_with_where_calculation
SQL : UPDATE t_order SET status = ? WHERE order_id = order_id - ? AND user_id = ?
SQL Params : [1, 2, 3]

Common expression text assertion error:

Expected: is "order_id - ?"
but: was "order_id-?"
at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[INFO] Running org.apache.shardingsphere.sql.parser.integrate.engine.UnsupportedSQLParserParameterizedTest
[INFO] Tests run: 0, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.006 s - in org.apache.shardingsphere.sql.parser.integrate.engine.UnsupportedSQLParserParameterizedTest
[INFO] Running org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.SQLCasesLoaderTest
[INFO] Tests run: 6, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0 s - in org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.SQLCasesLoaderTest
[INFO]
[INFO] Results:
[INFO]
[ERROR] Failures:
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : create_table_with_out_of_line_check
SQL : CREATE TABLE t_order (order_id INT, user_id INT, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10), CHECK (order_id > 0))
Constraint definitions size assertion error:

Expected: is <1>
but: was <0>
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : insert_on_duplicate_key_update_with_placeholders
SQL : INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status = ?
SQL Params : [1, 1, 'init', 'init']

Parameter markers count assertion error:

Expected: is <4>
but: was <5>
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : select_count_with_sub_with_whitespace
SQL : SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1
Common expression text assertion error:

Expected: is "1 - 1"
but: was "1-1"
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : select_distinct_function
SQL : SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id
Projection type assertion error:

Expected: an instance of org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.expression.ExpectedExpressionProjection
but: <org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925> is a org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : update_with_case_when
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 ? end where tenant_id = ?
SQL Params : [3, 2, 4, 2, 10, 2, 3, 'll', 4, 'll', 10, 'll', 3, '2020-08-10T17:15:25.979+0800', 'jd']

Parameter markers count assertion error:

Expected: is <15>
but: was <1>
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : update_with_where_calculation
SQL : UPDATE t_order SET status = 1 WHERE order_id = order_id - 2 AND user_id = 3
Common expression text assertion error:

Expected: is "order_id - 2"
but: was "order_id-2"
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:94
SQL Case ID : update_with_where_calculation
SQL : UPDATE t_order SET status = ? WHERE order_id = order_id - ? AND user_id = ?
SQL Params : [1, 2, 3]

Common expression text assertion error:

Expected: is "order_id - ?"
but: was "order_id-?"
[ERROR] Errors:
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ...
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ...
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ...
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ...
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ...
[ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ...
[INFO]
[ERROR] Tests run: 1905, Failures: 7, Errors: 6, Skipped: 0

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

In SQLCasesLoader.java:

Modify the method to

private static Collection<String> getALlDatabaseTypes() {
return Arrays.asList("H2", "MySQL", "PostgreSQL", "Oracle", "SQLServer", "SQL92");
}

Example codes for reproduce this issue (such as a github link).

Issue 2:
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',

name varchar(64) NOT NULL DEFAULT '' COMMENT '主播昵称',

avatar varchar(1024) NOT NULL DEFAULT '' 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 INTO anchor_info (id, source, anchorId, name, avatar, status, createTime, updateTime) VALUES

(#{id,jdbcType=BIGINT}, #{source,jdbcType=VARCHAR}, #{anchorId,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR},

#{avatar,jdbcType=VARCHAR},#{status,jdbcType=TINYINT}, #{createTime,jdbcType=BIGINT}, #{updateTime,jdbcType=BIGINT})

ON DUPLICATE KEY UPDATE name=VALUES(name),

updateTime=VALUES(updateTime);

7、sharding rule configuration is:

rules:

!ENCRYPT

tables:

anchor_info:

columns:

name:

cipherColumn: name

encryptorName: aes

encryptors:

aes:

type: AES

props:

aes-key-value: abcdisjckel14003c2f8ea32cc1d59e5