MySQL Bugs: #20230: routine_definition is not null

Bug #20230	routine_definition is not null
Submitted:	2 Jun 2006 15:23	Modified:	2 Aug 2006 18:40
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug/5.0BK	OS:	Linux (SUSE 10.0)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
In information_schema, in the routines table, the routine_definition column is NOT NULL.
It should be nullable.
If a user does not have privileges, routine_definition contains ''.
It should contain NULL.

How to repeat:
As user root, say:

mysql> create procedure p6 () set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> grant execute on procedure p6 to john@localhost;
Query OK, 0 rows affected (0.00 sec)

As user john@localhost, say:

mysql> select count(*) from information_schema.routines where specific_name='p6' and routine_definition is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.routines where specific_name='p6' and routine_definition = '';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysql -ujohn
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition is null;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition = '';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> 
-----------------------------------------------------------------------------------
miguel@hegel:~/dbs/5.0> bin/mysql -ujohn
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition is null;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition = '';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.01 sec)

mysql>

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/8199

Approved by email.

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/8513

Fixed in 5.0.24 and 5.1.12

Noted in 5.0.24, 5.1.12 changelogs.

In the INFORMATION_SCHEMA.ROUTINES table the ROUTINE_NAME column now
is defined NOT NULL rather than NULL. Also, NULL rather than the empty
string is returned as the column value if the user does not have
sufficient privileges to see the routine definition.

Previous comment is incorrect.  Should be:

In the INFORMATION_SCHEMA.ROUTINES table the ROUTINE_DEFINITION
column now is defined as NULL rather than NOT NULL. Also, NULL rather
than the empty string is returned as the column value if the user
does not have sufficient privileges to see the routine definition.