MySQL Bugs: #36677: TRADITIONAL sql_mode not honored in stored procedures

Bug #36677	TRADITIONAL sql_mode not honored in stored procedures
Submitted:	12 May 2008 22:09	Modified:	29 May 2009 19:59
Reporter:	Tim Soderstrom	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.24-rc, 5.0.51a, 5.0.60	OS:	MacOS (Leopard)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	sql_mode stored procedures

Description:
I am trying to force a sql_mode for a particular stored procedure. In particular, I am setting the sql_mode to TRADITIONAL for data integrity purposes. I have tried a few methods of setting the SQL mode in a stored procedure, including defining it prior to importing the procedure, as well as running a SET SESSION sql_mode... Neither option seems to be honored by MySQL. I know the later does not work according to bug 13015, but the later should.

I think that both the SET SESSION inside the procedure as well as the SQL_MODE attached to the procedure should work. Certainly the SQL_MODE attached to the procedure should. Neither do. The peculiar part is that running a SHOW VARIABLES LIKE 'sql_mode'; returns the correct results no matter which method is used, even though MySQL opts not to honor either method.

How to repeat:
-- Importing Test Table

DROP TABLE IF EXISTS PhotoDawgLenses;

CREATE TABLE PhotoDawgLenses(
	lensID smallint unsigned NOT NULL auto_increment,
	lensName varchar(64) NOT NULL,
	minZoom mediumint unsigned NOT NULL,
	maxZoom mediumint unsigned NOT NULL,
	minAperture decimal(6,3) unsigned NOT NULL,
	maxAperture decimal(6,3) unsigned NOT NULL,
	hasED enum('Yes', 'No') NOT NULL DEFAULT 'No',
	hasVR enum('Yes', 'No') NOT NULL DEFAULT 'No',
	PRIMARY KEY (lensID),
	UNIQUE KEY (lensName)
) ENGINE='InnoDB';

DROP PROCEDURE IF EXISTS AddLensTestProc;

-- Setting sql_mode prior to importing procedure
SET SESSION sql_mode='TRADITIONAL';

DELIMITER //

CREATE PROCEDURE AddLensTestProc (
	IN inLensName varchar(64), 
	IN inMinZoom mediumint unsigned, 
	IN inMaxZoom mediumint unsigned,
	IN inMinAperture decimal(6,3) unsigned,
	IN inMaxAperture decimal(6,3) unsigned,
	IN inHasED enum('Yes', 'No'),
	IN inHasVR enum('Yes', 'No')
)
LANGUAGE SQL READS SQL DATA
main:BEGIN
	SHOW variables like 'sql_mode%';
	
	IF inMinZoom > inMaxZoom THEN
		LEAVE main;
	END IF;
	IF inMinAperture > inMaxAperture THEN
		LEAVE main;
	END IF;
	IF inHasED != 'Yes' THEN
		SET inHasED = 'No';
	END IF;
	IF inHasVR != 'Yes' THEN
		SET inhasVR = 'No';
	END IF;
	INSERT INTO PhotoDawgLenses (lensName, minZoom, maxZoom, minAperture, maxAperture, hasED, hasVR) VALUES
		(inLensName, inMinZoom, inMaxZoom, inMinAperture, inMaxAperture, inHasED, inHasVR);
END //

DELIMITER ;

-- Resetting sql mode back ot default (as if this was a new connection)
SET SESSION sql_mode='';

-- Here is the reuslt from the information_schema showing the the SQL_MODE is properly defined
SELECT ROUTINE_NAME, SQL_MODE FROM information_schema.ROUTINES WHERE ROUTINE_NAME='AddLensTestProc'\G
-- The follwoing should now work - inMinZoom is less than 0 and 'Bad' is not a value ENUM for inHasVR";
CALL AddLensTestProc('proctest9', -1, 1, 1, 1, 'Yes', 'Bad');
SELECT * FROM PhotoDawgLenses;
TRUNCATE PhotoDawgLenses;
-- However, even though the SQL Mode is set, MySQL is NOT flagging an error, although it should";
-- Setting the sql_mode manually works, but this should not be needed since the procedure already has a sql_mode defined";
SET SESSION sql_mode="TRADITIONAL";
CALL AddLensTestProc('proctest9', -1, 1, 1, 1, 'Yes', 'Bad');

Suggested fix:
MySQL should be properly honoring the SQL_MODE attached to the stored procedure.

Also verified in 5.0.51a:

# mysql moocowproductions_dev < testcase.sql 
*************************** 1. row ***************************
ROUTINE_NAME: AddLensTestProc
    SQL_MODE: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
Variable_name	Value
sql_mode	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
lensID	lensName	minZoom	maxZoom	minAperture	maxAperture	hasED	hasVR
1	proctest9	0	1	1.000	1.000	Yes	No
ERROR 1264 (22003) at line 68: Out of range value adjusted for column 'inMinZoom' at row 1

Here is the test-script (included in the how to reproduce section as well)

Attachment: testcase.sql (, text), 2.23 KiB.

Thank you for a problem report. Verified just as described with 5.0.60 also. 

This is in clear contradition with the manual, http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html, that says:

"MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the current server SQL mode."

The bug is apparently that the SQL mode of a stored procedure doesn't apply to 
assignment of IN parameters of the procedure. 
There is no reason for that, however, it would be an incompatible change.
A test case:

mysql> set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p1 (a int) select a;
Query OK, 0 rows affected (0.02 sec)

mysql> call  p1(1/0);
+------+
| a    |
+------+
| NULL | 
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 | 
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.01 sec)

mysql> call  p1(1/0);
+------+
| a    |
+------+
| NULL | 
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I do not believe that we should consider this a bug.

If I understand correctly, the point is that MySQL,
for an IN parameter, evaluates the expression and
makes an assignment before changing the sql_mode.

But that is by design. Looking at the standard-SQL
specification (SQL/Foundation, <routine invocation>)
I gather that one should evaluate and assign before
changing the routine execution context, before starting
to execute the routine. And, although sql_mode is of
course not standard, I believe it fits in routine
execution context better than anywhere else. Therefore
the routine's sql_mode does not have effect until the
routine starts to execute.

There is no contradiction with the manual. It says
"always executes the routine with this setting in force",
and that is the case. The only possible difficulty is
that a user might not realize the routine execution does
not begin until after the IN evaluation + assignment is
over. Therefore I am setting this to "Documenting".

Workarounds:
- assign IN parameters to declared stored procedure variables
- wrap invocation of a procedure into another CALL.
- run consistently in the intended SQL mode.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to manual:

"The switch from the SQL mode of the invoker to that of the routine
occurs after evaluation of arguments and assignment of the resulting
values to routine parameters. If you define a routine in strict SQL
mode but invoke it in non-strict mode, assignment of arguments to
routine parameters does not take place in strict mode. If you require
that expressions passed to a routine be assigned in strict SQL mode,
you should invoke the routine with strict mode in effect."

Note: The workaround of assigning parameters to local routine variables
does not work. By then it's too late. For example, truncation of 9999
assigned to a TINYINT will already have occurred at parameter
assignment.