MySQL Bugs: #21589: Incosistency In error/warning with functions between 5.0.22 and 5.0.24

Bug #21589	Incosistency In error/warning with functions between 5.0.22 and 5.0.24
Submitted:	11 Aug 2006 15:35	Modified:	15 Aug 2006 18:44
Reporter:	Daniel Fiske	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24 Win32	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
There are inconsistencies between mysql 5.0.22 and .24 with regard to return values on functions.

How to repeat:
1. Create a function of the form

CREATE FUNCTION `fn_BUG`(
) RETURNS int(11)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN

  DECLARE nRET INT;
  SET nRet = -1;

  SELECT
    some_column into nRet
  FROM
    tbl_some_table
  WHERE
    some_other_column = 'a value that does not exist'
  LIMIT 1;

  RETURN nRET;
END $$

Where the SELECT section DOES NOT RETURN ANYTHING.

On: 5.0.22

mysql> SELECT fn_BUG(1);
+-----------------------------------------+
| fn_Bug() |
+-----------------------------------------+
|                                      -1 |
+-----------------------------------------+
1 row in set, 1 warning (0.06 sec)

On: 5.0.24

mysql> SELECT fn_BUG(1);
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

The latter causes a major problem in code as there is NO return value. In the .NET libraries this throws an exception from ExecuteScalar.

Suggested fix:
None!

Thank you for the report.

Could you please try below test: I can not repeat it on Linux using last sources and want to be sure test is correct.

create table if not exists bug21589(id int not null auto_increment primary key,foo varchar(255));

truncate table bug21589;

insert into bug21589 (foo) values ('foo'), ('bar'), ('baz');

delimiter $$

CREATE FUNCTION `fn_BUG`() RETURNS int(11)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN

  DECLARE nRET INT;
  SET nRet = -1;

  SELECT
    id into nRet
  FROM
    bug21589
  WHERE
    foo = 'a value that does not exist'
  LIMIT 1;

  RETURN nRET;
END $$

delimiter ;

I can not repeat it on Windows 2003 using provided test and MySQL 5.0.24. If cause of I can not repeat the bug is test, please, provide correct test and reopen bug.

Will test further and and let you know and add further comment.

Ran the following

drop database bug21589;

create database bug21589;
use bug21589;

create table if not exists bug21589(id int not null auto_increment primary key,foo varchar(255));

truncate table bug21589;

insert into bug21589 (foo) values ('foo'), ('bar'), ('baz');

delimiter $$

CREATE FUNCTION `fn_BUG`() RETURNS int(11)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN

  DECLARE nRET INT;
  SET nRet = -1;

  SELECT
    id into nRet
  FROM
    bug21589
  WHERE
    foo = 'a value that does not exist'
  LIMIT 1;

  RETURN nRET;
END $$

delimiter ;

select fn_BUG();

show variables;

Output of bug

Attachment: bug21589 output.txt (text/plain), 26.61 KiB.

See attached file for output of these queries

Verified as on Linux using last BK sources with a bit modified test:

SET SQL_MODE='STRICT_TRANS_TABLES';

DROP DATABASE bug21589;

CREATE DATABASE bug21589;
USE bug21589;

CREATE TABLE IF NOT EXISTS bug21589(id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,foo VARCHAR(255));

TRUNCATE TABLE bug21589;

INSERT INTO bug21589 (foo) VALUES ('foo'), ('bar'), ('baz') ENGINE = INNODB;

delimiter $$

CREATE FUNCTION `fn_BUG`() RETURNS int(11)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN

  DECLARE nRET INT;
  SET nRet = -1;

  SELECT
    id into nRet
  FROM
    bug21589
  WHERE
    foo = 'a value that does not exist'
  LIMIT 1;

  RETURN nRET;
END $$

delimiter ;

SELECT fn_BUG();

We have similar bug #20028

Status changed, because this bug is duplicate of bug #20028

This bug (#21589) isn't strictly speaking a duplicate of bug #20028.

The original problem in bug #20028 concerned an error being generated when one SP calls another.

This bug concerns an issue with a single SP generating an error when called on its own.

While bug #20028 was being investigated, the problem in this bug also started to affect the test case for bug #20028 - leading to two problems being demonstrated under bug #20028.

The single SP problem in this bug, and the double SP issue originally reported in bug #20028, are both fixed in MySQL 5.0.33.

Hope this information helps anyone looking into error 1329 when using stored procedures!