Bug #114001 #1327 - Undeclared variable: CONCAT

• Submitted: 15 Feb 2024 3:26
• Modified: 15 Feb 2024 10:54
• Reporter: Jianzhong Zhang
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: mysql Ver 15.1 Distrib 10.4.28-MariaDB,
• OS: MacOS (Sonoma 14.3.1)
• CPU Architecture: x86 (3.1 GHz 6-core Intel Core i5)
• Tags: MySQL, mysql bug, mysql treat CONCAT as a undeclared variable, triggers

[15 Feb 2024 3:26] Jianzhong Zhang

Description:
get error "#1327 - Undeclared variable: CONCAT" when to create a trigger:

DELIMITER // 
CREATE TRIGGER setInsert_default_volunteers
BEFORE INSERT ON cnasa_db.volunteers
FOR EACH ROW
BEGIN
  DECLARE tmpName VARCHAR(60);
  DECLARE tmpID   SMALLINT;
  DECLARE tmpTest BOOLEAN;

  SET tmpName = CONCAT(NEW.firstName, " ", NEW.lastName);

  IF NEW.wvrSignedName IS NULL OR NEW.wvrSignedName <=> tmpName THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT("volunteers table: Inserting a row without your signature or with a signed name different from your full name is not allowed.");
  END IF;

  SET NEW.wvrAgreementID = (SELECT MAX(id) FROM cnasa_db.waiverAgreements);

  IF NEW.passcode IS NULL THEN
    SET NEW.passcode = CONCAT(LOWER(NEW.firstName), LOWER(NEW.lastName), "@CNASAvolunteer");
  END IF;

  IF NEW.position IS NULL THEN
    SET NEW.position = "helper";
  ELSE
    SET tmpTest = (NEW.position <=> "director") AND (NEW.position <=> "referee") AND (NEW.position <=> "president") AND (NEW.position <=> "treasure") AND (NEW.position <=> "webMaintainer") AND (NEW.position <=> "helper");
    IF tmpTest THEN
      SET NEW.position = "helper";
    END IF;
  END IF;
END;
// DELIMITER ;

How to repeat:
execute the above sql code

Suggested fix:
may be parser problem/bug?

[15 Feb 2024 10:54] MySQL Verification Team

Hello Jianzhong Zhang,

Thank you for the report and feedback.
I see you are using non Oracle build, please report to the appropriate bugDB for further help/clarification. Discussed with my senior colleague Shane, it seems only a limited subset of <expr> are allowed in SIGNAL/RESIGNAL.  Could you please try removing CONCAT to see if it helps? i.e.

From:
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT("volunteers table: Inserting a row without your signature or with a signed name different from your full name is not allowed.");

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "volunteers table: Inserting a row without your signature or with a signed name different from your full name is not allowed.";

Alternatively, what he suggest is to try something like i.e you can you create a string in a @user variable and assign it.  E.g.

mysql> signal sqlstate '01000' SET MESSAGE_TEXT = 'A warning occurred';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set @s:=concat('a warning, ',now(),' for ',user());
Query OK, 0 rows affected (0.00 sec)

mysql> signal sqlstate '01000' SET MESSAGE_TEXT = @s;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1642 | a warning, 2024-02-15 12:48:44 for root@localhost |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

Sincerely,
Umesh