| 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: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.7 beta | OS: | Windows (windows xp) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[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.
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;