MySQL Bugs: #6129: Stored procedure won't display @@sql

Bug #6129	Stored procedure won't display @@sql_mode value
Submitted:	16 Oct 2004 14:13	Modified:	13 Apr 2005 14:29
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Inside a stored procedure, "SELECT @@sql_mode;" won't display the current value of 
@@sql_mode, and won't display the value as it was at CREATE PROCEDURE time. 
It might depend on the length of the @@sql_mode value. 
 

How to repeat:
mysql> set sql_mode='ansi'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p30 () select @@sql_mode; 
Query OK, 0 rows affected (0.36 sec) 
 
mysql> call p30(); 
+--------------------------------------------------------------------------------+ 
| @@sql_mode                                                                     | 
+--------------------------------------------------------------------------------+ 
| 
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI 
| 
+--------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p31 () select @@sql_mode; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> call p31(); 
+------------+ 
| @@sql_mode | 
+------------+ 
|            | 
+------------+ 
1 row in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec)

This was fixed when the sql_mode field in the mysql.proc table was brought
up-to-date with the actual sql_mode definition.
However, an old installation might need to update the table according to
the mysql_fix_privilege_tables script (or reinstall the system database).

A test case will be added though.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23966