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

[7 Sep 2005 13:13] Anders Karlsson
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'
[7 Sep 2005 13:33] Valerii Kravchuk
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
[7 Sep 2005 13:51] Anders Karlsson
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.
[24 Oct 2005 19:38] Oleksandr Byelkin
OK to push after fixing sql_class.h as it mentioned in review.
[25 Oct 2005 9:02] 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/31436
[25 Oct 2005 9:08] Alexander Nozdrin
Approved by Sanja Byelkin by email.
Fixed in 5.0, currently tagged 5.0.15.
[27 Oct 2005 9:28] Jon Stephens
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.