Bug #20315 Incorrect function return, error 1329 (02000)

• Submitted: 7 Jun 2006 11:04
• Modified: 15 Aug 2006 18:46
• Reporter: Daniel Guimarães
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.21-community-nt
• OS: Windows (Windows XP)
• CPU Architecture: Any

[7 Jun 2006 11:04] Daniel Guimarães

Description:
ATENTION! Preview post this bug: status changed to "Can't repeat" (???)

See the functions Func1 and Func2 of the example:

CREATE FUNCTION Func1() RETURNS INTEGER
BEGIN
  DECLARE Temp INTEGER;
  SELECT Id INTO Temp FROM Test LIMIT 1;
  RETURN IFNULL(Temp, 0);
END;

CREATE FUNCTION Func2() RETURNS INTEGER
BEGIN
  RETURN Func1();
END;

Func1() return zero (expected)
Func2() retorn no data (problem)

-- Result from SELECT version()

5.0.21-community-nt

Sequential output commands:

mysql> show variables like '%mode%';
+---------------+----------------------------------------------------------------+
| Variable_name | Value
 |
+---------------+----------------------------------------------------------------+
| sql_mode      |
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 |
+---------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select func1();
+---------+
| func1() |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select func2();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
mysql> show warnings;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
| Error | 1105 | Unknown error                                       |
| Error | 1105 | Unknown error                                       |
+-------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS Test;
DROP FUNCTION IF EXISTS Func1;
DROP FUNCTION IF EXISTS Func2;

CREATE TABLE Test(Id INTEGER);
  
CREATE FUNCTION Func1() RETURNS INTEGER
BEGIN
  DECLARE Temp INTEGER;
  SELECT Id INTO Temp FROM Test LIMIT 1;
  RETURN IFNULL(Temp, 0);
END;

CREATE FUNCTION Func2() RETURNS INTEGER
BEGIN
  RETURN Func1();
END;

Execute:

SELECT Func1(); /* Return zero */

SELECT Func2(); /* Return no data! */

-- Result from SELECT func1()

+---------+
|  func1()  |
+---------+
|           0 |
+---------+

-- Result from SELECT func2()

ERROR 1329 (02000) at line 1: No data - zero rows fetched, selected, or
processed

Suggested fix:
None

[7 Jun 2006 13:26] Valeriy Kravchuk

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

mysql> delimiter //
mysql> CREATE FUNCTION Func1() RETURNS INTEGER
    -> BEGIN
    ->   DECLARE Temp INTEGER;
    ->   SELECT Id INTO Temp FROM Test LIMIT 1;
    ->   RETURN IFNULL(Temp, 0);
    -> END;//
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION Func2() RETURNS INTEGER
    -> BEGIN
    ->   RETURN Func1();
    -> END;//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select func1();
ERROR 1146 (42S02): Table 'test.Test' doesn't exist
mysql> CREATE TABLE Test(Id INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> select func1();
+---------+
| func1() |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select func2();
+---------+
| func2() |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

[15 Aug 2006 18:46] Sveta Smirnova

It is same as bug #20028