Bug #9286 SESSION/GLOBAL should be disallowed for user variables
Submitted: 18 Mar 2005 19:40 Modified: 22 May 2005 23:42
Reporter: Sergei Golubchik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:
Assigned to: Sergei Glukhov CPU Architecture:Any

[18 Mar 2005 19:40] Sergei Golubchik
Description:
From: Paul DuBois <paul@mysql.com>
To: dev-public@mysql.com
Subject: Should SESSION/GLOBAL be disallowed for user variables?
Date: Sat, 1 May 2004 12:10:31 -0500

>
> set session @honk=99;
>
>results in Query OK, 0 rows affected (0.00 sec).

What's actually happening there is that the user is setting a user
variable, because only one '@' is used.  The user was confused
about that, I think, but the fact that the statement was accepted
raises a question:

@honk is not a session variable, so shouldn't the statement have
been rejected?  I suppose one might argue that it's harmless to
accept it, but I think that is not really so.  It leads the
user to believe that at least one of the following statements should
work for displaying the variable:

 show session variables like 'honk';
 show session variables like '@honk';

But of course neither of them work, because @honk is not a session
variable.

How to repeat:
set session @honk=99;
[22 Mar 2005 20:58] Paul DuBois
I wonder if the "scope" of this bug report should be expanded to include
stored routine local variables.  There is a passage in the manual that says
this:

@node SET Statement, SELECT INTO Statement, DECLARE Local Variables, Variables in Stored Procedures
@subsubsection Variable @code{SET} Statement

@findex SET

@example
SET @var{var_name} = @var{expr} [, @var{var_name} = @var{expr}] ...
@end example

The @code{SET} statement in stored procedures is an extended version of
the general @code{SET} statement. Referenced variables may be ones declared
inside a routine, or global server variables.

The @code{SET} statement in stored procedures is implemented as part of the
pre-existing @code{SET} syntax. This allows an extended syntax of
@code{SET a=x, b=y, ...} where different variable types (locally declared
variables, server variables, and global and session server variables) can be
mixed. This also allows combinations of local variables and some options
that make sense only for global/system variables; in that case, the options
are recognized but ignored.

What "some options" means in the final sentence is
that you can use SESSION and GLOBAL when setting
routine local variables, and those options will be 
ignored.  But this is a similar kind of problem as 
allowing SESSION/GLOBAL when setting user
variables: It gives the user the impression that
the statement has a different meaning than it
really does.

This script illustrates the idea:

use test;
drop procedure if exists p;
delimiter $$
CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 0;
  SELECT 'set using SESSION';
  SET SESSION i = i + 1;
  SELECT 'i =', i;
  SELECT 'set using GLOBAL';
  SET GLOBAL i = i + 1;
  SELECT 'i =', i;
END$$
delimiter ;
call p ();
drop procedure if exists p;

One ugly complication with this issue is that SESSION and GLOBAL "carry
rightward" in SET statements.  For example:

SET x = 1, @x = 1, SESSION storage_engine = InnoDB, sql_mode = ANSI;

In this statement, the SESSION option also applies to variable settings
to its right that have no explicit SESSION or GLOBAL.  That's fine for
the statement just shown, because sql_mode is a system variable and the
concepts of SESSION and GLOBAL apply.  But what about a statement like
this:

SET x = 1, @x = 1, SESSION storage_engine = InnoDB, x = 2, @x = 2;

Here, SESSION applies implicitly to the second x and @x assignments,
where it doesn't make sense.  So if SESSION/GLOBAL are disallowed
for user variables and/or local variables, that needs also to be
detected in certain cases where those keywords do not actually appear
literally.
[29 Mar 2005 9:23] 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/23424
[31 Mar 2005 11:23] Alexander Barkov
ok to push
[6 Apr 2005 9:12] 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/23700
[6 Apr 2005 10:45] Sergei Glukhov
Fixed in 4.1.11
[6 Apr 2005 20:44] Sergei Golubchik
5.0 needs to be fixed separately (the code is too different there)
[8 Apr 2005 11:51] 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/23800
[18 May 2005 7:44] 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/25007
[18 May 2005 9:49] Sergei Glukhov
Fixed in 5.0.6
[22 May 2005 23:42] Paul DuBois
Noted in 4.1.11, 5.0.6 changelog entries.

I'm also turning off the "bug does not exist
in released versions" flag, because it does
exist.