Bug #10055 Stored proccedures: using stored procedure (function) cause empty result set
Submitted: 21 Apr 2005 9:17 Modified: 10 Aug 2005 14:53
Reporter: Teemu Kuulasmaa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.7 beta OS:Microsoft Windows (windows xp)
Assigned to: Dmitry Lenev CPU Architecture:Any

[21 Apr 2005 9:17] Teemu Kuulasmaa
Description:
If I try to use my own functions (stored procedure) in a query, I get empty result set! 

How to repeat:
The following query works like it should (this is obvious):
SELECT t.COLUMN_NAME
  FROM
    information_schema.COLUMNS AS t
  WHERE t.TABLE_NAME like 'user'

But the following query doesn't work. It gives empty result set!:
SELECT t.COLUMN_NAME, TEST(t.COLUMN_NAME)
  FROM
    information_schema.COLUMNS AS t
  WHERE t.TABLE_NAME like 'user'

The TEST function has been defined as follows:
CREATE FUNCTION TEST (var CHAR)
  RETURNS CHAR(255)
BEGIN
  RETURN LOVER(var);
END;
[21 Apr 2005 14:47] Jorge del Conde
mysql> SELECT t.COLUMN_NAME, TEST(t.COLUMN_NAME)
    ->   FROM
    ->     information_schema.COLUMNS AS t
    ->   WHERE t.TABLE_NAME like 'User'
    -> //
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
    -> //
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1100 | Table 'user' was not locked with LOCK TABLES |
+---------+------+----------------------------------------------+
1 row in set (0.01 sec)
[16 Jun 2005 6:07] Teemu Kuulasmaa
I tested behavior of server version 5.0.7 beta. The new version crashes if I execute the query:

SELECT t.COLUMN_NAME, TEST(t.COLUMN_NAME)
  FROM
    information_schema.COLUMNS AS t
  WHERE t.TABLE_NAME like 'user'

The TEST function was defined as follows:

CREATE FUNCTION TEST (var CHAR)
  RETURNS CHAR(255)
BEGIN
  RETURN LOWER(var);
END;

So behavior has been changed. Instead of empty resultset the query causes server crash.

I hope this bug will be solved quickly.
[16 Jun 2005 6:11] Teemu Kuulasmaa
Once again... It seems that server will crash only if query refers to information_schema. The following query works like it should:

SELECT t.User, TEST(t.User)
  FROM
    mysql.user AS t
  WHERE t.User like 'root'
[28 Jul 2005 9:05] 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/27671
[8 Aug 2005 13:44] 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/27995
[9 Aug 2005 8:55] 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/28058
[9 Aug 2005 9:04] Dmitry Lenev
Fixed in 5.0.12
[10 Aug 2005 14:53] 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:

Fix documented in 5.0.12 changelog.