MySQL/MariaDB stored procedure compiles with typos

Question

I just asked a similar question to this, but that turned out to be because MySQL column names are case insenstive and I was creating an ambiguity. I took the advice given and prefixed all my params with p. The problem is that if I type the param value with a misspelling the SP still compiles, but then fails at run time. Is there something I can do about this?

Here is an example:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
    pEmail VARCHAR (250),
    pHashedPassword BINARY(60),
    pFirstName VARCHAR (100),
    pLastName VARCHAR (100),
    pGender ENUM ('Male', 'Female'),
    pDateOfBirth DATE,
    pHeight DECIMAL (3, 2),
    pCurrentWeight DECIMAL (4, 1) 
    -- CALL User_Signup('[email protected]', 'password', 'Lee', 'Brooks', NULL, NULL, NULL, NULL);
    -- CALL User_Signup('[email protected]', 'password', 'Lee', 'Brooks', 'Male', CURRENT_DATE(), 1.85, 101.3);
) 
BEGIN
    DECLARE vRoleId INT;

    SELECT 
        id INTO vRoleId 
    FROM
        Role 
    WHERE `Code` = 'CUSTOMER' ;


    INSERT INTO USER (
        Email,
        HashedPassword,
        RoleId,
        FirstName,
        LastName,
        Gender,
        DateOfBirth,
        Height,
        CurrentWeight,
        CreatedAt
    ) 
    VALUES
        (
            pEmail,
            pHashedPassword,
            vRoleId,
            pFirstName,
            pLastName,
            pGender,
            pDateOfBirth,
            pHeight,
            pCurrentWeigh, << Note the typo, this still compiles
            UTC_TIMESTAMP()
        ) ;
END $$

DELIMITER ;

Answer 1

The explanation here is related to the same situation found in the other question... the expression here is at least potentially ambiguous, so it can't reasonably be validated at define time. It could refer to something that's not yet defined.

MySQL validates the syntax of stored procedures at define-time, but it doesn't validate anything that's potentially ambiguous or things that could become valid by the time the program is actually running.

In a stored procedure:

-- unambiguous

SET foo      -- definition rejected if 'foo' is not a valid local or system variable
    = bar;   -- definition rejected if 'bar' is not a valid local variable

-- potentially ambiguous, probably intended to be a column name if 'foo' isn't a variable
-- could be a reference to a not-yet-created or altered base or temp table

UPDATE buzzfizz SET foo = bar; -- checked for syntax only, server assumes you know best

The tables referenced in a stored procedure don't even have to exist when the procedure is declared, or could have a different structure when the procedure is run... after all, you can create tables (base tables and temporary tables) within stored procedures. If a procedure could only be declared against already-existing objects, this would be a significant lack of flexibility. You can also "mask" a base table by creating a temporary table with the same name, which would have messy implications here.

Stored functions and events abide by the same general rules, although things are a little different there since these programs do have differences from procedures... for example, you can't create a base table within a stored function, because stored functions aren't allowed to implicitly or explicitly cause a COMMIT.

Triggers are somewhat different, because they are parsed and cached as soon as they are declared, so if their definition doesn't match the table they're defined against (i.e., referencing a nonexistent column in the table via the NEW or OLD alias), the trigger is rejected... but references to other tables are not validated, since the other table might not yet exist and things that could be "column names" could indeed refer to columns or could refer to be other objects.

Views are somewhat different. They're validated at define time, and the referenced objects have to exist. That's why mysqldump generates SQL statements to create a dummy table with the same name and approximately the same column definitions as each view, only to later cause those tables to be dropped and replaced with the view itself. If not for this, views couldn't be restored from a backup if they referenced other views that did not yet exist. You can change the underlying objects out from under a view, which will cause an error, later when you try to access the view... but you can repair a view that you've broken in this way by putting the table back into its original condition. Views, of course, have no local variables and can't create or alter tables or manipulate transactions.