MySQL Bugs: #26394: IF error message "ERROR 1054 (42S22): Unknown column 'X' in 'field list'"

Bug #26394	IF error message "ERROR 1054 (42S22): Unknown column 'X' in 'field list'"
Submitted:	15 Feb 2007 8:20	Modified:	15 Feb 2007 9:40
Reporter:	Roland Bouman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.36-NK, all	OS:	Linux (Linux)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	42S22, error 1054, IF, IF...THEN, stored procedure, Unknown column

Description:
Semantics of Stored procedure code is not checked at CREATE time. At runtime, undeclared variables are detected, and an error message is generated for each reference to an undeclared variable. However, SP's seem to believe any reference denotes a column, even though the syntactic context excludes that. This leads to a very confusing error message in case the procedure.

How to repeat:
A simple demonstration:

delimiter $$
create procedure p_test()
if
     thisIsNotAColumn = 0
then
     select 1;
end if;
$$

call p_test()
$$
ERROR 1054 (42S22): Unknown column 'thisIsNotAColumn' in 'field list'

So, thisIsNotAColumn is not a column, but the error message suggests otherwise. Here, it is of course easy to spot the error. Now consider this example: 

delimiter ;

create table thisReallyIsATable(
    thisIsNotAColum int
);

delimiter $$

drop procedure if exists p_test
$$
create procedure p_test()
begin
    declare v_thisIsNotAColumn int;

    select thisIsNotAColumn
    into   v_thisIsNotAColumn
    from   thisReallyIsATable
    ;
    if
        thisIsNotAColumn = 0
    then
         select 1;
    end if;
end;
$$

call p_test()
$$
ERROR 1054 (42S22): Unknown column 'thisIsNotAColumn' in 'field list'

When debugging this, the instinct will always be to think that it is the SELECT INTO that is causing the problem, and people are confused when they find to be nothing wrong with the table. 

Suggested fix:
Two possibilities, they don't exclude each other:

1) The error message should instead be

ERROR #### (#####): Reference to undeclared variable 'thisIsNotAColumn'

2) Any runtime erros should report a line and column (that is, character position) number to indicate the offending piece of code.

Thank you for a bug report. Verified just as described with latest 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot 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 1
Server version: 5.0.36 Source distribution

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

mysql> create table thisReallyIsATable(
    ->     thisIsNotAColum int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //
mysql> create procedure p_test()
    -> begin
    ->     declare v_thisIsNotAColumn int;
    ->
    ->     select thisIsNotAColumn
    ->     into   v_thisIsNotAColumn
    ->     from   thisReallyIsATable
    ->     ;
    ->     if
    ->         thisIsNotAColumn = 0
    ->     then
    ->          select 1;
    ->     end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> call p_test();
ERROR 1054 (42S22): Unknown column 'thisIsNotAColumn' in 'field list'

Hi Marc, 
This is very close to the problem reported in Bug#5967 - internally it's a duplicate, but since it's another manifestation, I'm not closing it as such.
When correct name resolution of SP variables is implemented, the error message will be automatically fixed as the SP variable name resolution context will be the last in the stack and will be used to issue an error when nothing is found in it.
Right now the tables name resolution context is the last in the name resolution stack, hence the error.
Bug#5967 is "To be fixed later".