Bug #8692 Cursor, fetch of empty string, wrong result
Submitted: 22 Feb 2005 16:07 Modified: 3 Aug 2005 16:00
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: Georg Richter

[22 Feb 2005 16:07] Matthias Leich
Description:
Stored Procedure with cursor:
If I fetch an empty string into a stored procedure variable or a @variable
the content is NULL instead of ''.
Example:
delimiter //;
drop procedure if exists p1//
drop table if exists t1//

create table t1 (col1 VARCHAR(255) not null)//
insert into t1 values('')//

create procedure p1() sql security invoker
begin
    declare v1 VARCHAR(255);
    declare xx cursor for select col1 from t1;
  open xx;
    fetch xx into v1;
    select v1;
end //

call p1()//
I get NULL instead of ''  .

Please have a look into the attached test case derived from Trudy's
fine test cases for WL#1107. I get this wrong behaviour for all
string data types (VARCHAR,CHAR,TEXT,BLOB).

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1869, 2005-02-22

How to repeat:
Please use my attached test file ml29.test , copy it to mysql-test/t
  touch r/ml29.result     # Produce a dummy file with 
                                  # expected results.
  ./mysql-test-run ml29
  <inspect> r/ml29.reject    # The protocol of the execution.
[22 Feb 2005 16:15] Matthias Leich
test case

Attachment: ml29.test (application/test, text), 2.84 KiB.

[30 May 2005 22:23] Peter Gulutzan
I get the same thing with the ENUM data type. For example:

create table fk6 (s1 enum('one','two'))//
insert into fk6 values ('')//
create function fk6 () returns enum('one','two') begin declare v enum('one','two'); declare c cursor for select  s1 from fk6; open c; fetch c into v; return v; end//
select fk6()//
... result is NULL
[22 Jul 2005 6:12] 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/27461
[27 Jul 2005 16:53] Georg Richter
fixed in 5.0.11
[3 Aug 2005 16:00] Jon Stephens
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.11 changelog.