MySQL Bugs: #13037: undefined variable in IF cause erroneous error-message

Bug #13037	undefined variable in IF cause erroneous error-message
Submitted:	7 Sep 2005 13:13	Modified:	27 Oct 2005 9:28
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.12, 5.0.13-BK	OS:	Linux (Linux)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
Using an undefined variables in an IF-statement sometimes cause the error-message produced when calling the procedure to report "unknown column ... in 'order clause'". This also affects a SET clause that use an undefined variable. But this doesn't happen all the time. This only happens until a "correct" error message is produced, which happens for example when a SELECT is done on an undefined column. After that, recreating the first erroneous procedure again cause a more correct "unknown column ... in 'field list'". By loggin in and out, the 'order clause' error is reoccuring.
Which error is correct might be discussed, but getting two different ones in different circumstances is obviously incorrect.

How to repeat:
$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 381 to server version: 5.0.12-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> drop procedure p1//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p1()
    -> begin
    -> if foo then
    -> select 1;
    -> end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
ERROR 1054 (42S22): Unknown column 'foo' in 'order clause'

mysql> drop procedure p1//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p1()
    -> begin
    -> set @foo = bar;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
ERROR 1054 (42S22): Unknown column 'bar' in 'order clause'
mysql> drop procedure p1//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p1()
    -> begin
    -> select foo;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
ERROR 1054 (42S22): Unknown column 'foo' in 'field list'
mysql> drop procedure p1//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p1()
    -> begin
    -> if foo then
    -> select 1;
    -> end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
ERROR 1054 (42S22): Unknown column 'foo' in 'field list'

I was able to repeat the behaviour described on 5.0.13-BK build too:

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delimiter //
mysql> create procedure p1()
    -> begin
    ->  if foo then
    ->    select 1;
    ->  end if;
    -> end//
Query OK, 0 rows affected (0.07 sec)

mysql> call p1()//
ERROR 1054 (42S22): Unknown column 'foo' in 'order clause'
mysql> create procedure p1_1()
    -> begin
    ->  if @foo then
    ->    select 1;
    ->  end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1_1()//
Query OK, 0 rows affected (0.00 sec)

mysql> set @foo=1//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1_1()//
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select foo//
ERROR 1054 (42S22): Unknown column 'foo' in 'field list'
mysql> select version()//
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0.00 sec)

But, please, note both the "right way" to write such a procedure (p1_1), and the message you get when trying to reference foo in the command line, not in SP. So, I am not sure SPs adds something special in this case.

But, please

Why is p1_1 more "right" than p1? They serve completely different purposes. Using  only foo without the @-sign references procedure local variables or parameters, whereas @foo is used to access a session global variable? One or the other is not right or wrong, they serve different purposes.

OK to push after fixing sql_class.h as it mentioned in review.

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/31436

Approved by Sanja Byelkin by email.
Fixed in 5.0, currently tagged 5.0.15.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Documented bugfix in 5.0.15 changelog; closed.