Bug #25067 Can't use year and value as column names in queries inside a stored procedures

• Submitted: 14 Dec 2006 11:43
• Modified: 18 Dec 2006 21:27
• Reporter: Rogerio Araújo
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1.12
• OS: Windows (windows xp)
• CPU Architecture: Any

[14 Dec 2006 11:43] Rogerio Araújo

Description:
If i run this statement under my stored procedure:

    select id into quarterid
    from quarter
    where year = yearvalue and value = quartervalue;

nothing is returned and quarterid remains null, but if i change some columns names:

    select id into quarterid
    from quarter
    where yearx = yearvalue and valuex = quartervalue;

my desired values are returned.

How to repeat:
1 - Create a table named quarter with the columns: id int, year varchar(4) and value varchar(2).

2 - Create a stored procedure and put the command above inside this stored procedure:

    declare quarterid int unsigned;

    select id into quarterid
    from quarter
    where year = '2006' and value = '02';

3 - Insert a row in quarter table with data below:

insert into quarter (year, value) values('2006', '02');

4 - Execute the stored procedure and check the value stored into quarterid variable after query execution.

[14 Dec 2006 11:47] Rogerio Araújo

Fixed synopsis.

[18 Dec 2006 21:29] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.1.15-BK on Linux:

openxs@suse:~/dbs/5.1> 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.1.15-beta Source distribution

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

mysql> create table quarter (id int, year varchar(4), value varchar(2));
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter //
mysql> create procedure proc1()
    -> begin
    -> declare quarterid int unsigned;
    -> select id into quarterid
    -> from quarter
    -> where year='2006' and value='02';
    -> select quarterid;
    -> end;
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> insert into quarter values(1, '2006', '02');
Query OK, 1 row affected (0.01 sec)

mysql> call proc1();
+-----------+
| quarterid |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So, if you do have non-NULL value in the id column of the row, it "works". There is not problem with 'year' and 'value" names. But if I'll do as you describe:

mysql> delete from quarter;
Query OK, 1 row affected (0.00 sec)

mysql> insert into quarter (year, value) values('2006', '02');
Query OK, 1 row affected (0.00 sec)

mysql> call proc1();
+-----------+
| quarterid |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I will get NULL, but this is not a bug. Your id column had not got any value, so it is NULL.