MySQL/MariaDB stored procedure inserts even with syntax error

Question

createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail?

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
) 
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  ) 
  VALUES
    (
      email,
      hashedPassword,
      (SELECT 
        id 
      FROM
        Role 
      WHERE `Code` = 'CUSTOMER'),
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      createdAt <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    ) ;
END $$

DELIMITER ;

score 2 · Accepted Answer · edited Jun 15 '20 at 09:05

Nope, it's not a bug. It's a situation that would otherwise have been an ambiguity, that got resolved in a different way in the absence of the declared variable. MySQL Server does exactly the same thing.

Watch this:

mysql> select @@version_comment, @@version;
+------------------------------+-----------+
| @@version_comment            | @@version |
+------------------------------+-----------+
| MySQL Community Server (GPL) | 5.5.30    |
+------------------------------+-----------+
1 row in set (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE ambiguity (c1 DATETIME NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)
mysql> INSERT INTO ambiguity (c1) VALUES (c1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM ambiguity;
+---------------------+
| c1                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql>

All you did was reference a column name from the row you were inserting, which, "at the time" you referenced it (even though there is theoretically no such concept as a flow of time or order of operations during an insert operation) the column "already contained" (had been initialized to) its default value.

It's pretty unusual that you'd do that intentionally, but it's a theoretically legitimate operation, at least in MySQL.

Best practice is to not use the same names for stored program ("local") variables as you're using for column names, because of exactly this situation, and others like it... because it's ambiguous.

“A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.”

— http://dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html

...and, of course, as a column name, when possible, when there's no such variable.

MySQL/MariaDB stored procedure inserts even with syntax error

1 Answers1

Linked