Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;
share|improve this question
1  
That's looks like a bug within MariaDB. You should fire up mysql in a test VM, port some data and code into it. Then run it. If the INSERT still goes ahead, blame Larry Ellison and go search the MySQL bug lists for any Stored Procedure anomalies like this. –  RolandoMySQLDBA Jan 16 at 23:27

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
That is correct. I am having another similar problem which I asked about over here: dba.stackexchange.com/questions/56967/… –  John Doe Jan 17 at 1:34
    
"but it's a theoretically legitimate operation". No it's not. In SQL INSERT INTO ambiguity (c1) VALUES (c1) is an invalid statement because of the column reference in the values clause. The values clause is only allowed to contain value literals, expressions or scalar queries but not column references. Additionally the only possible value for that column would be NULL there (because it's undefined) and thus your example insert should at least fail with an not-null constraint violation. –  a_horse_with_no_name Jan 17 at 9:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.