MYSQL function fails - Failed SQL: (1064)

This is my Liquibase changeset:

<changeSet author="user" id="1712203200334-1">    
    <sqlFile path="config/liquibase/IsValidCoordinatesFormat.sql"/>    
</changeSet>

This is the config/liquibase/IsValidCoordinatesFormat.sql file:

CREATE FUNCTION IsValidCoordinatesFormat(coordString VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE isValid BOOLEAN;
  DECLARE regex VARCHAR(255);
  SET isValid = FALSE;

  SET regex = '^(-?\\d+(\\.\\d+)?);(-?\\d+(\\.\\d+)?)$';

  IF coordString REGEXP regex THEN
    SET isValid = TRUE;
  END IF;

  RETURN isValid;
END;

And this is the error i'm getting

Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12 [Failed SQL: (1064) CREATE FUNCTION IsValidCoordinatesFormat(coordString VARCHAR(255)) RETURNS BOOLEAN
2024-04-04 08:00:53 DETERMINISTIC
2024-04-04 08:00:53 BEGIN
2024-04-04 08:00:53   DECLARE isValid BOOLEAN;
2024-04-04 08:00:53   DECLARE regex VARCHAR(255);
2024-04-04 08:00:53   SET isValid = FALSE;
2024-04-04 08:00:53 
2024-04-04 08:00:53   SET regex = '^(-?\\d+(\\.\\d+)?);(-?\\d+(\\.\\d+)?)$';
2024-04-04 08:00:53 
2024-04-04 08:00:53   IF coordString REGEXP regex THEN
2024-04-04 08:00:53     SET isValid = TRUE;
2024-04-04 08:00:53   END IF]

The SQL syntax looks correct for me. What's causing the problem?

Answers

The error you're encountering seems to be related to the delimiter used in your SQL script. By default, MySQL expects a different delimiter for stored procedures, functions, and triggers.

When defining a stored procedure, function, or trigger in MySQL, you typically need to change the delimiter temporarily to something other than ; to ensure that statements within the body of the procedure/function/trigger are not prematurely terminated.

Here's how you can modify your script to address this issue:

DELIMITER $$

CREATE FUNCTION IsValidCoordinatesFormat(coordString VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE isValid BOOLEAN;
  DECLARE regex VARCHAR(255);
  SET isValid = FALSE;

  SET regex = '^(-?\\d+(\\.\\d+)?);(-?\\d+(\\.\\d+)?)$';

  IF coordString REGEXP regex THEN
    SET isValid = TRUE;
  END IF;

  RETURN isValid;
END$$

DELIMITER ;

In this script:

• We change the delimiter using DELIMITER $$ before the CREATE FUNCTION statement.
• The function body ends with END$$.
• After the function definition, we reset the delimiter back to ; using DELIMITER ;.

By changing the delimiter, MySQL will treat the entire function definition as a single statement and correctly interpret it.