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

Allow single dimension DECIMAL in CAST

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 1.2.1.Final
    • 1.1.2.Final
    • mysql-connector
    • None
    • 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

      Attachments

        Activity

          People

            Unassigned Unassigned
            michael_hit001 michael wong (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: