<p dir="auto">TODO:</p>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> create_table_with_out_of_line_check</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> insert_on_duplicate_key_update_with_placeholders</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> select_count_with_sub_with_whitespace</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> select_distinct_function</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> update_with_case_when</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> update_with_where_calculation</li>
</ul>
<p dir="auto">The following SQL is not supported by SQL 92 and should be remove from the tests:</p>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Unsupported SQL of <code class="notranslate">CREATE INDEX order_index ON t_order (order_id, user_id, status)</code></li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Unsupported SQL of <code class="notranslate">CREATE INDEX t_log_index ON t_log (id)</code></li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Unsupported SQL of <code class="notranslate">CREATE UNIQUE INDEX t_log_index ON t_log (id)</code></li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Unsupported SQL of <code class="notranslate">CREATE USER user1 IDENTIFIED BY RANDOM password default role role1</code></li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Unsupported SQL of <code class="notranslate">CREATE USER user1 IDENTIFIED BY RANDOM password default role role1 PASSWORD EXPIRE ACCOUNT LOCK</code></li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Unsupported SQL of <code class="notranslate">TRUNCATE TABLE t_log</code></li>
</ul>
<h2 dir="auto">Bug Report</h2>
<p dir="auto"><strong>For English only</strong>, other languages will not accept.</p>
<p dir="auto">Before report a bug, make sure you have:</p>
<ul dir="auto">
<li>Searched open and closed <a href="https://github.com/apache/shardingsphere/issues">GitHub issues</a>.</li>
<li>Read documentation: <a href="https://shardingsphere.apache.org/document/current/en/overview" rel="nofollow">ShardingSphere Doc</a>.</li>
</ul>
<p dir="auto">Please pay attention on issues you submitted, because we maybe need more details.<br>
If no response anymore and we cannot reproduce it on current information, we will <strong>close it</strong>.</p>
<p dir="auto">Please answer these questions before submitting your issue. Thanks!</p>
<h3 dir="auto">Which version of ShardingSphere did you use?</h3>
<p dir="auto">5.0.0-RC1-SNAPSHOT</p>
<h3 dir="auto">Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?</h3>
<p dir="auto">Both</p>
<h3 dir="auto">Expected behavior</h3>
<p dir="auto">SQL 92 parser test pass</p>
<h3 dir="auto">Actual behavior</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="[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 &lt;&lt;&lt; FAILURE! - in org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest
[ERROR] assertSupportedSQL[create_composite_index (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.007 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.017 s  &lt;&lt;&lt; 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 &gt; 0))
Constraint definitions size assertion error: 

Expected: is &lt;1&gt;
     but: was &lt;0&gt;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[create_unique_index (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &lt;4&gt;
     but: was &lt;5&gt;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[select_count_with_sub_with_whitespace (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0 s  &lt;&lt;&lt; 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 &gt; 1 - 1
Common expression text assertion error: 

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

[ERROR] assertSupportedSQL[select_distinct_function (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0 s  &lt;&lt;&lt; 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: &lt;org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925&gt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &lt;15&gt;
     but: was &lt;1&gt;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[update_with_where_calculation (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &quot;order_id - 2&quot;
     but: was &quot;order_id-2&quot;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[update_with_where_calculation (Placeholder) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &quot;order_id - ?&quot;
     but: was &quot;order_id-?&quot;
	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 &gt; 0))
Constraint definitions size assertion error: 

Expected: is &lt;1&gt;
     but: was &lt;0&gt;
[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 &lt;4&gt;
     but: was &lt;5&gt;
[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 &gt; 1 - 1
Common expression text assertion error: 

Expected: is &quot;1 - 1&quot;
     but: was &quot;1-1&quot;
[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: &lt;org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925&gt; 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 &lt;15&gt;
     but: was &lt;1&gt;
[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 &quot;order_id - 2&quot;
     but: was &quot;order_id-2&quot;
[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 &quot;order_id - ?&quot;
     but: was &quot;order_id-?&quot;
[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"><pre class="notranslate"><code class="notranslate">[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 &lt;&lt;&lt; FAILURE! - in org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest
[ERROR] assertSupportedSQL[create_composite_index (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.007 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.017 s  &lt;&lt;&lt; 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 &gt; 0))
Constraint definitions size assertion error: 

Expected: is &lt;1&gt;
     but: was &lt;0&gt;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[create_unique_index (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &lt;4&gt;
     but: was &lt;5&gt;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[select_count_with_sub_with_whitespace (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0 s  &lt;&lt;&lt; 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 &gt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0 s  &lt;&lt;&lt; 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: &lt;org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925&gt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &lt;15&gt;
     but: was &lt;1&gt;
	at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94)

[ERROR] assertSupportedSQL[update_with_where_calculation (Literal) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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) -&gt; SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest)  Time elapsed: 0.001 s  &lt;&lt;&lt; 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 &gt; 0))
Constraint definitions size assertion error: 

Expected: is &lt;1&gt;
     but: was &lt;0&gt;
[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 &lt;4&gt;
     but: was &lt;5&gt;
[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 &gt; 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: &lt;org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925&gt; 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 &lt;15&gt;
     but: was &lt;1&gt;
[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
</code></pre></div>
<h3 dir="auto">Reason analyze (If you can)</h3>
<h3 dir="auto">Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.</h3>
<p dir="auto">In SQLCasesLoader.java:</p>
<p dir="auto">Modify the method to</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    private static Collection&lt;String&gt; getALlDatabaseTypes() {
        return Arrays.asList(&quot;H2&quot;, &quot;MySQL&quot;, &quot;PostgreSQL&quot;, &quot;Oracle&quot;, &quot;SQLServer&quot;, &quot;SQL92&quot;);
    }"><pre class="notranslate"><code class="notranslate">    private static Collection&lt;String&gt; getALlDatabaseTypes() {
        return Arrays.asList("H2", "MySQL", "PostgreSQL", "Oracle", "SQLServer", "SQL92");
    }
</code></pre></div>
<h3 dir="auto">Example codes for reproduce this issue (such as a github link).</h3>
<p dir="auto">1、Which version of ShardingSphere did you use?<br>
shardingsphere-jdbc:5.0.0-alpha</p>
<p dir="auto">2、Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?<br>
shardingsphere-jdbc</p>
<p dir="auto">3、Expected behavior:<br>
excute sql like "INSERT INTO user_info ( id, name, sex, updateTime) VALUES ( ?, ?, ?) ON DUPLICATE KEY UPDATE <code class="notranslate">name</code>=VALUES(<code class="notranslate">name</code>),updateTime=VALUES(updateTime);",<br>
I want the <code class="notranslate">name</code> colume encrypted update</p>
<p dir="auto">4、Actual behavior:<br>
when excute this sql,throw some exceptions like this:<br>
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<br>
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]<br>
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]<br>
at java.util.Optional.ifPresent(Optional.java:159) ~[?:1.8.0_73]<br>
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]<br>
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]<br>
at org.apache.shardingsphere.encrypt.rewrite.context.EncryptSQLRewriteContextDecorator.decorate(EncryptSQLRewriteContextDecorator.java:42) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]<br>
at org.apache.shardingsphere.encrypt.rewrite.context.EncryptSQLRewriteContextDecorator.decorate(EncryptSQLRewriteContextDecorator.java:34) ~[shardingsphere-encrypt-rewrite-5.0.0-alpha.jar:5.0.0-alpha]<br>
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]<br>
at java.util.LinkedHashMap.forEach(LinkedHashMap.java:676) ~[?:1.8.0_73]<br>
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.decorate(SQLRewriteEntry.java:83) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]<br>
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.createSQLRewriteContext(SQLRewriteEntry.java:76) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]<br>
at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.rewrite(SQLRewriteEntry.java:69) ~[shardingsphere-infra-rewrite-engine-5.0.0-alpha.jar:5.0.0-alpha]<br>
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:54) ~[shardingsphere-infra-context-5.0.0-alpha.jar:5.0.0-alpha]<br>
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]<br>
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]<br>
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]<br>
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]<br>
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]<br>
at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]<br>
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.4.0.jar:3.4.0]<br>
at com.sun.proxy.$Proxy77.execute(Unknown Source) ~[?:?]<br>
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.0.jar:3.4.0]<br>
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.0.jar:3.4.0]<br>
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.4.0.jar:3.4.0]<br>
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.0.jar:3.4.0]<br>
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.0.jar:3.4.0]<br>
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.0.jar:3.4.0]<br>
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.0.jar:3.4.0]<br>
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]<br>
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]<br>
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]<br>
at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]<br>
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434) ~[mybatis-spring-1.3.0.jar:1.3.0]<br>
... 45 more</p>
<p dir="auto">5、Reason analyze (If you can):<br>
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<br>
LiteralExpressionSegment</p>
<p dir="auto">6、Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc:<br>
the table DDL is:<br>
CREATE TABLE <code class="notranslate">anchor_info_1</code> (<br>
<code class="notranslate">id</code> bigint(20) NOT NULL COMMENT '主键id',<br>
<code class="notranslate">source</code> varchar(16) NOT NULL COMMENT '主播来源，yy（yy）；后续扩展',<br>
<code class="notranslate">anchorId</code> varchar(64) NOT NULL COMMENT '主播id',<br>
<code class="notranslate">name</code> varchar(64) NOT NULL DEFAULT '' COMMENT '主播昵称',<br>
<code class="notranslate">avatar</code> varchar(1024) NOT NULL DEFAULT '' COMMENT '主播头像',<br>
<code class="notranslate">status</code> tinyint(4) NOT NULL DEFAULT '1' COMMENT '记录状态，-1-删除，1-有效，默认为1',<br>
<code class="notranslate">createTime</code> bigint(20) NOT NULL COMMENT '创建时间，精确到毫秒',<br>
<code class="notranslate">updateTime</code> bigint(20) NOT NULL DEFAULT '0' COMMENT '最近修改时间，精确到毫秒',<br>
<code class="notranslate">lastUpdateTime</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最近的修改时间，服务端专用，用来爬取更新的数据',<br>
PRIMARY KEY (<code class="notranslate">id</code>) USING BTREE,<br>
UNIQUE KEY <code class="notranslate">idx_source_anchorId</code> (<code class="notranslate">source</code>,<code class="notranslate">anchorId</code>) USING BTREE,<br>
KEY <code class="notranslate">idx_createTime</code> (<code class="notranslate">createTime</code>) USING BTREE,<br>
KEY <code class="notranslate">idx_updateTime</code> (<code class="notranslate">updateTime</code>) USING BTREE,<br>
KEY <code class="notranslate">idx_lastUpdateTime</code> (<code class="notranslate">lastUpdateTime</code>) USING BTREE<br>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='主播信息表';</p>
<p dir="auto">excute sql is:<br>
<br>
INSERT INTO anchor_info (id, source, anchorId, name, avatar, status, createTime, updateTime) VALUES<br>
(#{id,jdbcType=BIGINT}, #{source,jdbcType=VARCHAR}, #{anchorId,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR},<br>
#{avatar,jdbcType=VARCHAR},#{status,jdbcType=TINYINT}, #{createTime,jdbcType=BIGINT}, #{updateTime,jdbcType=BIGINT})<br>
ON DUPLICATE KEY UPDATE <code class="notranslate">name</code>=VALUES(name),<br>
updateTime=VALUES(updateTime);<br>
</p>
<p dir="auto">7、sharding rule configuration is:<br>
rules:</p>
<ul dir="auto">
<li>!ENCRYPT<br>
tables:<br>
anchor_info:<br>
columns:<br>
name:<br>
cipherColumn: name<br>
encryptorName: aes<br>
encryptors:<br>
aes:<br>
type: AES<br>
props:<br>
aes-key-value: abcdisjckel14003c2f8ea32cc1d59e5</li>
</ul>