Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-2305

Allow single dimension DECIMAL in CAST

    Details

    • Steps to Reproduce:
      Hide
      1. create a new file named "mysql-decimal-issue.ddl" , under path: {debezium-project-home}/debezium-connector-mysql/src/test/resources/ddl/;
      2. copy above SQL into step 1's file
      3. add below code into MySqlSchemaTest.java
        @Test
            public void testForDecimal() {
                mysql = build
                        .with(DatabaseHistory.SKIP_UNPARSEABLE_DDL_STATEMENTS, true)
                        .storeDatabaseHistoryInFile(TEST_FILE_PATH)
                        .serverName(SERVER_NAME)
                        .createSchemas();
                mysql.start();
                source.setBinlogStartPoint("binlog-001", 400);
                mysql.applyDdl(source, "db1", "SET " + MySqlSystemVariables.CHARSET_NAME_SERVER + "=utf8mb4", this::printStatements);
        
                mysql.applyDdl(source, "db1", readFile("ddl/mysql-decimal-issue.ddl"), this::printStatements);
        
               
                assertHistoryRecorded();
            }
        
      1. run unit test as:
        mvn test -Dtest=MySqlSchemaTest#testForDecimal
        

        you will get the following exception:

      io.debezium.text.ParsingException: no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))\nBEGIN CREATE TEMPORARY TABLE temp1\nSELECT\n    SUM(round(case\n                   when\n                        bod.return_product_weight is not null\n                   then\n                        bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100\n                   when\n                        bod.return_product_num is not null\n                   then\n                        bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10)'
      	at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40)
      	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
      	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
      	at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
      	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121)
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849)
      	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68)
      	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)
      
      Show
      create a new file named "mysql-decimal-issue.ddl" , under path: {debezium-project-home}/debezium-connector-mysql/src/test/resources/ddl/; copy above SQL into step 1's file add below code into MySqlSchemaTest.java @Test public void testForDecimal() { mysql = build .with(DatabaseHistory.SKIP_UNPARSEABLE_DDL_STATEMENTS, true ) .storeDatabaseHistoryInFile(TEST_FILE_PATH) .serverName(SERVER_NAME) .createSchemas(); mysql.start(); source.setBinlogStartPoint( "binlog-001" , 400); mysql.applyDdl(source, "db1" , "SET " + MySqlSystemVariables.CHARSET_NAME_SERVER + "=utf8mb4" , this ::printStatements); mysql.applyDdl(source, "db1" , readFile( "ddl/mysql-decimal-issue.ddl" ), this ::printStatements); assertHistoryRecorded(); } run unit test as: mvn test -Dtest=MySqlSchemaTest#testForDecimal you will get the following exception: io.debezium.text.ParsingException: no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))\nBEGIN CREATE TEMPORARY TABLE temp1\nSELECT\n SUM(round( case \n when\n bod.return_product_weight is not null \n then\n bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100\n when\n bod.return_product_num is not null \n then\n bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10)' at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40) at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121) at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68) at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)

      Description

      I got following exception :

      io.debezium.text.ParsingException: no viable alternative at input 'CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))\nBEGIN CREATE TEMPORARY TABLE temp1\nSELECT\n    SUM(round(case\n                   when\n                        bod.return_product_weight is not null\n                   then\n                        bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100\n                   when\n                        bod.return_product_num is not null\n                   then\n                        bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10)'
      	at io.debezium.antlr.ParsingErrorListener.syntaxError(ParsingErrorListener.java:40)
      	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
      	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
      	at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
      	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.sqlStatements(MySqlParser.java:1121)
      	at io.debezium.ddl.parser.mysql.generated.MySqlParser.root(MySqlParser.java:849)
      	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:68)
      	at io.debezium.connector.mysql.antlr.MySqlAntlrDdlParser.parseTree(MySqlAntlrDdlParser.java:41)
      
      

      When I dig into the exception , I found the ANTLR parser did not resolve following SQL:

      
      CREATE DEFINER=`root`@`%` PROCEDURE `_Navicat_Temp_Stored_Proc`(st datetime,et datetime,saleman varchar(50))
      BEGIN CREATE TEMPORARY TABLE temp1
      SELECT
          SUM(round(case
                         when
                              bod.return_product_weight is not null
                         then
                              bod.actual_price-floor(floor(bod.return_product_weight/50)/10*bod.unit_price*100)/100
                         when
                              bod.return_product_num is not null
                         then
                              bod.actual_price-(CAST(bod.return_product_num AS DECIMAL(10))*unit_price)
                         else
                              bod.actual_price END,2)
             ) '实际金额小计(不含退货)'
      FROM business_order bo
      LEFT JOIN business_order_detail bod on bo.id=bod.order_id
      
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                michael_hit001 michael wong
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: