MySQL Bugs: #9286: SESSION/GLOBAL should be disallowed for user variables

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

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;

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.

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

ok to push

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

Fixed in 4.1.11

5.0 needs to be fixed separately (the code is too different there)

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

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

Fixed in 5.0.6

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.