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 2022/08/09 01:14:45 UTC
[GitHub] [shardingsphere] jinyingxiong opened a new issue, #20000: Oracle SQLParser Error
jinyingxiong opened a new issue, #20000:
URL: https://github.com/apache/shardingsphere/issues/20000
## Bug Report
### Which version of ShardingSphere did you use?
*5.1.2*
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
*ShardingSphere-JDBC*
### Expected behavior
*I'm using Quartz(2.3.2) in oracle, And use ShardingSphere-JDBC for encrypt some log record, but some simple SQL(generated by Quartz) can't parse by SQLParser and throw Exception*
### Reason analyze (If you can)
I don't know why
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
*See test case*
### Example codes for reproduce this issue (such as a github link).
#### Test Case
``` java
package com.ccesun.pipmp.sql.test;
import org.antlr.v4.runtime.Parser;
import org.antlr.v4.runtime.atn.PredictionMode;
import org.antlr.v4.runtime.misc.ParseCancellationException;
import org.apache.shardingsphere.sql.parser.api.parser.SQLParser;
import org.apache.shardingsphere.sql.parser.core.ParseASTNode;
import org.apache.shardingsphere.sql.parser.core.SQLParserFactory;
import org.apache.shardingsphere.sql.parser.core.database.parser.DatabaseTypedSQLParserFacadeFactory;
import org.apache.shardingsphere.sql.parser.spi.DatabaseTypedSQLParserFacade;
/**
* @author ZYB
* @since 2022/8/8 18:28:06
**/
public class OracleParseTest {
public static void main(String[] args) {
String databaseType = "Oracle"; //change to mysql can correct pass sql parse test
String sqls[] = new String[]{
"INSERT INTO QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP, DESCRIPTION, NEXT_FIRE_TIME, PREV_FIRE_TIME, TRIGGER_STATE, TRIGGER_TYPE, START_TIME, END_TIME, CALENDAR_NAME, MISFIRE_INSTR, JOB_DATA, PRIORITY) VALUES('IpmpQuartzScheduler', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
"SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'IpmpQuartzScheduler' AND TRIGGER_STATE = ? AND NEXT_FIRE_TIME <= ? AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= ?)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC",
"SELECT A.TRIGGER_NAME, A.TRIGGER_GROUP, A.NEXT_FIRE_TIME, A.PRIORITY FROM QRTZ_TRIGGERS A WHERE SCHED_NAME = 'IpmpQuartzScheduler' AND TRIGGER_STATE = ? AND NEXT_FIRE_TIME <= ? AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= ?)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC",
"SELECT * FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'IpmpQuartzScheduler' ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC",
};
for (String sql : sqls) {
parseSQLTest(databaseType, sql);
}
}
/**
* Test code copy from @{@link org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor#twoPhaseParse(String)}
* @param databaseType
* @param sql
*/
private static void parseSQLTest(String databaseType, String sql) {
ParseASTNode parse = null;
DatabaseTypedSQLParserFacade sqlParserFacade = DatabaseTypedSQLParserFacadeFactory.getInstance(databaseType);
SQLParser sqlParser = SQLParserFactory.newInstance(sql, sqlParserFacade.getLexerClass(), sqlParserFacade.getParserClass());
Throwable t = null;
try {
((Parser) sqlParser).getInterpreter().setPredictionMode(PredictionMode.SLL);
parse = (ParseASTNode) sqlParser.parse();
} catch (final ParseCancellationException ex) {
t = ex;
((Parser) sqlParser).reset();
((Parser) sqlParser).getInterpreter().setPredictionMode(PredictionMode.LL);
try {
parse = (ParseASTNode) sqlParser.parse();
} catch (final ParseCancellationException e) {
t = e;
}
}
if(parse != null){
System.out.println(parse.getRootNode().toStringTree((Parser) sqlParser));
}else{
System.err.println(String.format("%s - %s", t.getCause().getClass().getName(), sql));
}
}
}
```
### Test case run output:
```
org.antlr.v4.runtime.NoViableAltException - INSERT INTO QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP, DESCRIPTION, NEXT_FIRE_TIME, PREV_FIRE_TIME, TRIGGER_STATE, TRIGGER_TYPE, START_TIME, END_TIME, CALENDAR_NAME, MISFIRE_INSTR, JOB_DATA, PRIORITY) VALUES('IpmpQuartzScheduler', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
org.antlr.v4.runtime.NoViableAltException - SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'IpmpQuartzScheduler' AND TRIGGER_STATE = ? AND NEXT_FIRE_TIME <= ? AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= ?)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC
org.antlr.v4.runtime.NoViableAltException - SELECT A.TRIGGER_NAME, A.TRIGGER_GROUP, A.NEXT_FIRE_TIME, A.PRIORITY FROM QRTZ_TRIGGERS A WHERE SCHED_NAME = 'IpmpQuartzScheduler' AND TRIGGER_STATE = ? AND NEXT_FIRE_TIME <= ? AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= ?)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC
(select (selectSubquery (queryBlock SELECT (selectList (unqualifiedShorthand *)) (selectFromClause FROM (fromClauseList (fromClauseOption (selectTableReference (queryTableExprClause (queryTableExpr (queryTableExprSampleClause (queryTableExprTableClause (tableName (name (identifier QRTZ_TRIGGERS))))))))))) (whereClause WHERE (expr (booleanPrimary (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier SCHED_NAME))))))) (comparisonOperator =) (predicate (bitExpr (simpleExpr (literals (stringLiterals 'IpmpQuartzScheduler'))))))))) (orderByClause ORDER BY (orderByItem (columnName (name (identifier NEXT_FIRE_TIME))) ASC)) rowLimitingClause))
```
### ShardingSphere config file
``` yml
props:
sql-show: true
dataSources:
#names: unique_ds
unique_ds:
dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
driver-class-name: oracle.jdbc.OracleDriver
filters: mylog,stat,wall
initialSize: 5
maxActive: 50
maxWait: 60000
minEvictableIdleTimeMillis: 300000
minIdle: 5
password: test
poolPreparedStatements: true
testOnBorrow: false
testOnReturn: false
testWhileIdle: true
timeBetweenEvictionRunsMillis: 60000
url: jdbc:oracle:thin:@192.168.0.1:1521:orcl
username: test
validationQuery: SELECT 1 FROM DUAL
rules:
- !ENCRYPT
tables:
PIPMP_QUARTZ_EXEC_LOG:
columns:
JOB_CLASS_NAME:
plainColumn: JOB_CLASS_NAME
cipherColumn: JOB_CLASS_NAME_ENC
encryptorName: def-encryptor
queryWithCipherColumn: true
encryptors:
def-encryptor:
type: SM4
props:
sm4-key: '337E6B39466F756F2344257858235071' #'3~k9Fouo#D%xX#Pq'
sm4-mode: CBC
sm4-iv: '39405C433D52407E3B392B2F29745E76' #'9@\C=R@~;9+/)t^v'
sm4-padding: PKCS5Padding
```
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu closed issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #20000: Oracle SQLParser Error
URL: https://github.com/apache/shardingsphere/issues/20000
--
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] strongduanmu commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1209098293
Hi @tianhao960, can you submit a pr to add `PRIORITY` to unreservedWord?
--
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] tianhao960 commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
tianhao960 commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1209049776
@jinyingxiong , it seems that the sqls contain oracle keyword "PRIORITY" which cause the exception, I have try to add double quote(") around PRIORITY to the second sql you provided, it does not throw exception. you can try.
` String sql = "SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, \"PRIORITY\" FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'IpmpQuartzScheduler' AND TRIGGER_STATE = ? AND NEXT_FIRE_TIME <= ? AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= ?)) ORDER BY NEXT_FIRE_TIME ASC, \"PRIORITY\" DESC";
`
and the parse tree out put as below:
(select (selectSubquery (queryBlock SELECT (selectList (selectProjection (selectProjectionExprClause (expr (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier TRIGGER_NAME)))))))))) , (selectProjection (selectProjectionExprClause (expr (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier TRIGGER_GROUP)))))))))) , (selectProjection (selectProjectionExprClause (expr (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier NEXT_FIRE_TIME)))))))))) , (selectProjection (selectProjectionExprClause (expr (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier "PRIORITY"))))))))))) (selectFromClause FROM (fromClauseList (fromClauseOption (selectTableReference (queryTableExprClause (queryTableExpr (queryTableExprSampleClause (queryTableExprTableClause (tableName (name (identifier QRTZ_TRIGGERS))))))))))) (whereClause WHERE (expr (expr (expr (expr (booleanPrimary (booleanPrimary (predicate (bitExpr
(simpleExpr (columnName (name (identifier SCHED_NAME))))))) (comparisonOperator =) (predicate (bitExpr (simpleExpr (literals (stringLiterals 'IpmpQuartzScheduler'))))))) (andOperator AND) (expr (booleanPrimary (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier TRIGGER_STATE))))))) (comparisonOperator =) (predicate (bitExpr (simpleExpr (parameterMarker ?))))))) (andOperator AND) (expr (booleanPrimary (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier NEXT_FIRE_TIME))))))) (comparisonOperator <=) (predicate (bitExpr (simpleExpr (parameterMarker ?))))))) (andOperator AND) (expr ( (expr (expr (booleanPrimary (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier MISFIRE_INSTR))))))) (comparisonOperator =) (predicate (bitExpr (simpleExpr (literals (numberLiterals - 1))))))) (orOperator OR) (expr ( (expr (expr (booleanPrimary (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier MISFIRE_I
NSTR))))))) (comparisonOperator !=) (predicate (bitExpr (simpleExpr (literals (numberLiterals - 1))))))) (andOperator AND) (expr (booleanPrimary (booleanPrimary (predicate (bitExpr (simpleExpr (columnName (name (identifier NEXT_FIRE_TIME))))))) (comparisonOperator >=) (predicate (bitExpr (simpleExpr (parameterMarker ?))))))) ))) ))))) (orderByClause ORDER BY (orderByItem (columnName (name (identifier NEXT_FIRE_TIME))) ASC) , (orderByItem (columnName (name (identifier "PRIORITY"))) DESC)) rowLimitingClause))
--
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] jinyingxiong commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
jinyingxiong commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1209081608
@tianhao960 Thanks,it does work.
But I think the SQLParser should improve the error description, so we can resolve the sql problem myself.
Thanks anyway
--
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] tianhao960 commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
tianhao960 commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1208825555
I would like to try
--
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] tianhao960 commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
tianhao960 commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1209189286
>
I'll try
--
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] strongduanmu commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1208829280
@tianhao960 Welcome, I will assign this issue to you.
--
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] strongduanmu commented on issue #20000: Oracle SQLParser Error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #20000:
URL: https://github.com/apache/shardingsphere/issues/20000#issuecomment-1208794404
@jinyingxiong Thank you for your feedback. Let's see if the community's contributors are interested in this issue.
--
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