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