Bug #20028 Function with select return no data
Submitted: 23 May 2006 21:12 Modified: 13 Nov 2006 18:47
Reporter: Daniel Guimarães Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.21-community-nt OS:Windows (Windows XP Professional/Linux)
Assigned to: Marc ALFF CPU Architecture:Any

[23 May 2006 21:12] Daniel Guimarães
Description:
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)

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! */
[23 May 2006 22:22] Hartmut Holzgraefe
works fine on linux with both 5.0.21 and latest 5.0 source

could you add the output of SHOW VARIABLES to this issue
so that we can compare these to the settings i was using 
in my test?
[24 May 2006 12:37] Daniel Guimarães
Show variables result set

Attachment: show_variables.txt (text/plain), 5.68 KiB.

[24 May 2006 12:38] Daniel Guimarães
Result correct on Linux with MySQL 5.0.21.
[24 May 2006 16:11] Peter Laursen
On my (windows XP SP2) system both functions returns 0 (Zero) 
... with both MySQL 5.0.21 and 5.1.9
[24 May 2006 16:53] MySQL Verification Team
Thank you for the bug report. I got the same result on Linux and
Windows. Could you please print here both results you got.

Thanks in advance.
[24 May 2006 17:35] Daniel Guimarães
-- Result from SELECT func1()

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

-- Result from SELECT func2()

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

-- Result from SELECT version()

5.0.21-community-nt
[24 May 2006 17:47] MySQL Verification Team
Then please see your sql_mode:

mysql> SELECT Func1();
+---------+
| Func1() |
+---------+
| 0       |
+---------+
1 row in set, 1 warning (0.02 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>
[29 May 2006 17:25] Daniel Guimarães
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)
[15 Aug 2006 12:20] Sveta Smirnova
We have duplicate bug #20315
[15 Aug 2006 18:43] Sveta Smirnova
Verified as described on MySQL 5.0.23 using a bit modified test case:

SET SQL_MODE='STRICT_TRANS_TABLES';

DROP DATABASE bug20028;

CREATE DATABASE bug20028;
USE bug20028;

DROP TABLE IF EXISTS Test;
DROP FUNCTION IF EXISTS Func1;
DROP FUNCTION IF EXISTS Func2;

CREATE TABLE Test(Id INTEGER);

delimiter ||

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;

||

delimiter ;

SELECT Func1(); /* Return zero */

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

Current BK sources on Linux return different result:

mysql> SELECT Func1(); /* Return zero */
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
mysql>
mysql> SELECT Func2(); /* Return no data! */
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
mysql>

See also bug #21589.
[23 Aug 2006 6:57] Daniel Fiske
SERIOUSLY! HOW CAN THIS BE NON-CRITICAL?

You have 2 different returns for different versions of MySQL? One generates a warning the other an ERROR.....surely it fits into the category

S1 (Critical): Represents a complete loss of service, a significant functionality is missing, a system that hangs indefinitely; and there is no available workaround.

....returning an error instead of a warning...BREAKS a whole lot of stuff and as of yet there is no suggested workaround.

COME ON!
[15 Sep 2006 9:45] Sergei Golubchik
just to clarify - "severify" field is specified by bug reporter, not by MySQL developers. It shows how criticial is the bug to the reporter.
[28 Sep 2006 21:30] Daniel Guimarães
The problem persists in the version 5.0.24a

SELECT VERSION();

Result: 5.0.24a-community-nt

SELECT Func1(); /* Return no data! */
SELECT Func2(); /* Return no data! */
[28 Sep 2006 21:34] Daniel Guimarães
Results from SHOW VARIABLES in 5.0.24a version

Attachment: show_variables_5.0.24a.txt (text/plain), 5.97 KiB.

[28 Sep 2006 22:52] MySQL Verification Team
Sveta verified it on Linux too. Updating OS.
[6 Oct 2006 2:26] 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/commits/13211

ChangeSet@1.2271, 2006-10-05 20:26:07-07:00, malff@weblab.(none) +8 -0
  Bug#20028 (Function with select return no data)
  
  Before this change, THD::abort_on_warning was set in sp_head::execute,
  causing Stored Procedures, Stored Functions and Triggers to treat warnings
  as errors when executing in a strict SQL_MODE.
  
  After this change, THD::abort_on_warning is set in process_triggers in
  sql/sql_trigger.cc, which only affects triggers. Only INSERT and UPDATE
  triggers can elevate the abort_on_warning flag, which does not affect
  DELETE triggers.
  
  This fix is a revision of the change done for 6951, it produces the
  same results for the INSERT and UPDATE triggers in strict mode, while not
  causing side effects in DELETE triggers, Stored Procedures or Stored
  Functions, which -- according to the definition of a STRICT mode -- should
  not produre errors when no insert or update is performed.
  
  New tests cases have been added.
  
  An existing test case, sp-vars, was, according to the definition of the
  TRADITIONAL mode, incorrect in expecting errors from stored functions when
  no insert or update is present. The expected result for this test has been
  adjusted.
  
  Note to the reviewer: changes in sp-vars needs to be confirmed by an expert,
  my knowledge is too limited in this area.
[17 Oct 2006 17:58] Marc ALFF
The change causing diffs in the sp-vars test was wrong.
thd->abort_on_warning needs to be 0 inside a stored procedure,
including for triggers (per internal discussions)
[17 Oct 2006 18:11] 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/commits/13824

ChangeSet@1.2295, 2006-10-17 12:10:51-07:00, malff@weblab.(none) +8 -0
  Bug#20028 (Function with select return no data)
  
  This patch reverts a change introduced by Bug 6951, which incorrectly
  set thd->abort_on_warning for stored procedures.
  
  As per internal discussions about the SQL_MODE=TRADITIONAL,
  the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE
  trigger.
  
  Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE
  have been included or revised, to reflect the intended behavior.
[19 Oct 2006 17:40] 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/commits/14004

ChangeSet@1.2288, 2006-10-19 11:39:51-07:00, malff@weblab.(none) +8 -0
  Bug#20028 (Function with select return no data)
  
  This patch reverts a change introduced by Bug 6951, which incorrectly
  set thd->abort_on_warning for stored procedures.
  
  As per internal discussions about the SQL_MODE=TRADITIONAL,
  the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE
  trigger.
  
  Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE
  have been included or revised, to reflect the intended behavior.
  
  (reposting approved patch, to work around source control issues, no review needed)
[19 Oct 2006 20:20] Marc ALFF
Pushed to 5.0-runtime
[13 Nov 2006 18:47] Paul DuBois
Noted in 5.0.30 (not 5.0.29), 5.1.13 changelogs.

With SQL_MODE=TRADITIONAL, MySQL incorrectly aborted on warnings 
within stored routines and triggers.
[2 Dec 2006 14:29] dasjo fdasjk
This is the workaround that I used...

CREATE DEFINER=`root`@`localhost` FUNCTION `lookupSyn`(s varchar(45)) RETURNS varchar(45) CHARSET latin1
    READS SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE r VARCHAR(45);

  SELECT txt into r FROM (
    SELECT t2.txt as txt
     FROM dw_sales.synTxt t1 LEFT JOIN dw_sales.synTxt t2 ON t1.synId = t2.id
     WHERE t1.txt LIKE upper(s)
     UNION
     SELECT s as txt
     LIMIT 1
   )as t0;

  return r;

-- http://bugs.mysql.com/bug.php?id=20028
END
[18 Dec 2006 3:46] Vladimir Shebordaev
See also BUG #5929
[25 Feb 2007 19:23] Richard Fearn
For anyone coming across this for the first time, the original problem in this bug (#20028) is that when Func2 is called (which in turn calls Func1), an error is generated, but by itself, Func1 just generates a warning.

In MySQL 5.0.23, a change was made as part of a fix for bug #6951. This change caused Func1 - when called by itself - to *also* generate an error instead of a warning - hence why the behaviour changes (see the comment from 28 Sep 2006 23:30).

This new problem was reported on its own in bug #21589.

5.0.33 is the first version of the Community Server where both issues are fixed - and no error is generated when calling either Func1 or Func2.
[25 Feb 2007 20:32] Richard Fearn
The message in the 5.0.30 and 5.0.33 changelogs mentions TRADITIONAL mode:

"With SQL_MODE=TRADITIONAL, MySQL incorrectly aborted on warnings within stored routines and triggers."

However, as shown in the output from the test cases above, the original problem in this bug report (and the problem reported in bug #21589) occurs in strict mode.
[6 Oct 2009 6:31] Yuvraj N
CREATE FUNCTION Func1() RETURNS INTEGER
BEGIN
  DECLARE Temp INTEGER;
  SELECT Id INTO Temp FROM Test LIMIT 1;
  RETURN IFNULL(Temp, 0);
END;

Work and solve one error comming "Result returning more than on row/redord" But if I have multiple records in a table I tried

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

Still it will return the First record from my table say as myUniquefield=1 record. Which is wrong result comming i want the exact record for the myUniquefield=10. How do i achieve this?
[6 Oct 2009 13:03] Yuvraj N
There is not problem in function as i have written in above comment the Problem is at my enf i have passed the filed name in function parameter with the same as it is in the database e.g myUniqueFiled=myUniqueFiled (which is pass argument)