[MDEV-10481] Inconsistency between CREATE FUNCTION SYSDATE and DROP FUNCTION SYSDATE Created: 2016-08-02  Updated: 2018-08-27

Status: Open
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: Compatibility

Issue Links:
PartOf
is part of MDEV-10137 Providing compatibility to other data... Open

 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;



 Comments   
Comment by Alexander Barkov [ 2016-08-02 ]

The same problem is repeatable for NOW and CURTIME.

Comment by Alexander Barkov [ 2016-08-03 ]

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
Comment by Alexander Barkov [ 2018-06-15 ]

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 ;

Generated at Thu Feb 08 07:42:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.