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

Inconsistency between CREATE FUNCTION SYSDATE and DROP FUNCTION SYSDATE

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Parser

    Description

      This query:

      DROP FUNCTION IF EXISTS SYSDATE;
      

      works normally and returns no errors.

      This query:

      CREATE FUNCTION SYSDATE() RETURNS INT RETURN 10;
      

      returns an error:

      ERROR 1064 (42000): You have an error in your SQL syntax ... near 'SYSDATE() ...
      

      There is an inconsistency in here.
      Either both queries should return an error, or both work with no errors.

      The best solution is to make both queries work, as we generally allow to create stored functions that have the same names with built-in functions:

      DROP FUNCTION IF EXISTS CONCAT;
      CREATE FUNCTION CONCAT(a TEXT) RETURNS TEXT RETURN 'test';
      SELECT test.CONCAT('dummy');
      DROP FUNCTION CONCAT;
      

      Attachments

        Issue Links

          Activity

            The same problem is repeatable for NOW and CURTIME.

            bar Alexander Barkov added a comment - The same problem is repeatable for NOW and CURTIME.
            bar Alexander Barkov added a comment - - edited

            The problem happens because the tokenizer in sql_lex.cc does this:

            find_keyword(str, length, c == '(')
            

            In case when SYSDATE is followed by parentheses, it returned as SYSDATE.
            Otherwise, it's returned as IDENT.

            Most of the functions listed in lex.h in the sql_functions[] array are also affected:

            • BIT_AND
            • BIT_OR
            • BIT_XOR
            • CAST
            • COUNT
            • CUME_DIST
            • CURDATE
            • CURTIME
            • DATE_ADD
            • DATE_SUB
            • DENSE_RANK
            • EXTRACT
            • GROUP_CONCAT
            • MAX
            • MID
            • MIN
            • NOW
            • NTILE
            • POSITION
            • PERCENT_RANK
            • RANK
            • ROW_NUMBER
            • STD
            • STDDEV
            • STDDEV_POP
            • STDDEV_SAMP
            • SUBSTR
            • SUBSTRING
            • SUM
            • SYSDATE
            • TRIM
            • VARIANCE
            • VAR_POP
            • VAR_SAMP

            These functions from sql_functions[] are not affected (because they are listed in the "keyword_sp" rule):

            • ADDDATE
            • SESSION_USER
            • SUBDATE
            • SYSTEM_USER
            bar Alexander Barkov added a comment - - edited The problem happens because the tokenizer in sql_lex.cc does this: find_keyword(str, length, c == '(' ) In case when SYSDATE is followed by parentheses, it returned as SYSDATE. Otherwise, it's returned as IDENT. Most of the functions listed in lex.h in the sql_functions[] array are also affected: BIT_AND BIT_OR BIT_XOR CAST COUNT CUME_DIST CURDATE CURTIME DATE_ADD DATE_SUB DENSE_RANK EXTRACT GROUP_CONCAT MAX MID MIN NOW NTILE POSITION PERCENT_RANK RANK ROW_NUMBER STD STDDEV STDDEV_POP STDDEV_SAMP SUBSTR SUBSTRING SUM SYSDATE TRIM VARIANCE VAR_POP VAR_SAMP These functions from sql_functions[] are not affected (because they are listed in the "keyword_sp" rule): ADDDATE SESSION_USER SUBDATE SYSTEM_USER
            bar Alexander Barkov added a comment - - edited

            With sql_mode=ORACLE this script also fails:

            SET sql_mode=ORACLE;
            DELIMITER $$
            CREATE OR REPLACE FUNCTION SYSDATE() RETURN INT AS
            BEGIN
              RETURN 10;
            END;
            $$
            DELIMITER ;
            

            While this script (with no parentheses after the function name) works fine:

            SET sql_mode=ORACLE;
            DELIMITER $$
            CREATE OR REPLACE FUNCTION SYSDATE RETURN INT AS
            BEGIN
              RETURN 10;
            END;
            $$
            DELIMITER ;
            

            bar Alexander Barkov added a comment - - edited With sql_mode=ORACLE this script also fails: SET sql_mode=ORACLE; DELIMITER $$ CREATE OR REPLACE FUNCTION SYSDATE() RETURN INT AS BEGIN RETURN 10; END ; $$ DELIMITER ; While this script (with no parentheses after the function name) works fine: SET sql_mode=ORACLE; DELIMITER $$ CREATE OR REPLACE FUNCTION SYSDATE RETURN INT AS BEGIN RETURN 10; END ; $$ DELIMITER ;

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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