Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34144

SHOW CREATE FUNCTION generates syntax error

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • 10.6.17
    • N/A
    • Stored routines, Upgrades
    • None
    • AlmaLinux 8

    Description

      Hello,

      A node that was upgraded from MariaDB 5.5 to 10.3 to 10.6.17 produces an error on displaying a stored function:

      MariaDB [idm]> SHOW CREATE FUNCTION `calculate_real_timestamp`;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset int(11), timezone int(11)) RETURNS decimal(20,4)
          DETERMINISTIC
      BEG...' at line 1
      

      The function exists according to the information_schema:

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset int(11), timezone int(11)) RETURNS decimal(20,4)
          DETERMINISTIC
      BEG...' at line 1
      MariaDB [idm]> select * FROM information_schema.routines where routine_name='calculate_real_timestamp'\G
      *************************** 1. row ***************************
                 SPECIFIC_NAME: calculate_real_timestamp
               ROUTINE_CATALOG: def
                ROUTINE_SCHEMA: app
                  ROUTINE_NAME: calculate_real_timestamp
                  ROUTINE_TYPE: FUNCTION
                     DATA_TYPE: decimal
      CHARACTER_MAXIMUM_LENGTH: NULL
        CHARACTER_OCTET_LENGTH: NULL
             NUMERIC_PRECISION: 20
                 NUMERIC_SCALE: 4
            DATETIME_PRECISION: NULL
            CHARACTER_SET_NAME: NULL
                COLLATION_NAME: NULL
                DTD_IDENTIFIER: decimal(20,4)
                  ROUTINE_BODY: SQL
            ROUTINE_DEFINITION: BEGIN
      RETURN (time / 1000000) + ((ABS(offset) > 43200) * offset) + ((ABS(offset) < 1800) * ((timezone * 60) + 0)) + ((ABS(offset) > 43200) * ((timezone * 60) + 0));
      END
                 EXTERNAL_NAME: NULL
             EXTERNAL_LANGUAGE: NULL
               PARAMETER_STYLE: SQL
              IS_DETERMINISTIC: YES
               SQL_DATA_ACCESS: CONTAINS SQL
                      SQL_PATH: NULL
                 SECURITY_TYPE: DEFINER
                       CREATED: 2022-03-28 22:22:55
                  LAST_ALTERED: 2022-03-28 22:22:55
                      SQL_MODE: 
               ROUTINE_COMMENT: 
                       DEFINER: root@localhost
          CHARACTER_SET_CLIENT: utf8
          COLLATION_CONNECTION: utf8_general_ci
            DATABASE_COLLATION: latin1_swedish_ci
      1 row in set (0,016 sec)
      

      I guess that it errors on the usage of the word 'offset'. Perhaps that became a reserved word in recent versions?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              michaeldg Michaël de groot
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.