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

[16 Oct 2004 14:13] Peter Gulutzan
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)
[13 Apr 2005 14:29] Per-Erik Martin
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.
[13 Apr 2005 14:29] Bugs System
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