Bug #21083 SELECT empty string into stored procedure local variable gives NULL
Submitted: 16 Jul 2006 17:45 Modified: 16 Jul 2006 18:25
Reporter: Svetoslav Naidenov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 OS:
Assigned to: CPU Architecture:Any

[16 Jul 2006 17:45] Svetoslav Naidenov
Description:
Selecting empty varchar or varbinary field into stored procedure local variable
results NULL instead of ''

Seems similary to Closed Bug #8692

How to repeat:
create table t1(
 id int primary key,
 f varchar(16) not null,
 l varchar(16) not null
);

insert into t1 values (1,'harry',''),(2,'sally', 'not empty');

delimiter //
create procedure sp_t1(in x int)
begin
  declare t_f, t_l varchar(16);

  select f,l into t_f, t_l
  from t1 where id = x;

  select t_f, t_l;
end
//

delimiter ;

mysql> call sp_t1(1);
+-------+------+
| t_f   | t_l  |
+-------+------+
| harry | NULL |
+-------+------+
1 row in set (0.00 sec)
[16 Jul 2006 18:25] MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbr
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbr
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

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

mysql> create table t1(
    ->  id int primary key,
    ->  f varchar(16) not null,
    ->  l varchar(16) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> insert into t1 values (1,'harry',''),(2,'sally', 'not empty');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> delimiter //
mysql> create procedure sp_t1(in x int)
    -> begin
    ->   declare t_f, t_l varchar(16);
    -> 
    ->   select f,l into t_f, t_l
    ->   from t1 where id = x;
    -> 
    ->   select t_f, t_l;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> delimiter ;
mysql> call sp_t1(1);
+-------+------+
| t_f   | t_l  |
+-------+------+
| harry |      | 
+-------+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>