MySQL Bugs: #44010: Different result from information_schema.is

Bug #44010	Different result from information_schema.is_routines between 5.1 and 6.0
Submitted:	1 Apr 2009 8:50	Modified:	1 Apr 2009 9:27
Reporter:	Magnus Blåudd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Different result from query against information_schema.routines in 6.0 vs 5.1. 5.1 does not return any rows, while 6.0 does.

How to repeat:
From the testcase funcs_1.is_routines:

<snip>
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;

# Show that the column values of
#   ROUTINE_CATALOG, EXTERNAL_NAME, EXTERNAL_LANGUAGE, SQL_PATH are always NULL
# and
#   ROUTINE_BODY, PARAMETER_STYLE are 'SQL'
# and
#   SPECIFIC_NAME = ROUTINE_NAME.
SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
       routine_body,external_name,external_language,parameter_style,sql_path
FROM information_schema.routines
WHERE routine_catalog   IS NOT NULL OR external_name   IS NOT NULL
   OR external_language IS NOT NULL OR sql_path        IS NOT NULL
   OR routine_body      <> 'SQL'    OR parameter_style <> 'SQL'
   OR specific_name     <> routine_name;

^^^ This query gives a result in 6.0 but not in 5.1

Thank you for the report.

Verified as described.

Reading result in 6.0:

SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
routine_body,external_name,external_language,parameter_style,sql_path
FROM information_schema.routines
WHERE routine_catalog   IS NOT NULL
OR external_name   IS NOT NULL
OR external_language IS NOT NULL
OR sql_path        IS NOT NULL
OR routine_body      <> 'SQL'
OR parameter_style <> 'SQL'
OR specific_name     <> routine_name
;
specific_name   add_suppression
routine_catalog def
routine_schema  mtr
routine_name    add_suppression
routine_type    PROCEDURE
routine_body    SQL
external_name   NULL
external_language       NULL
parameter_style SQL
sql_path        NULL

While in 5.1:

specific_name   add_suppression
routine_catalog NULL
routine_schema  mtr
routine_name    add_suppression
routine_type    PROCEDURE
routine_body    SQL
external_name   NULL
external_language       NULL
parameter_style SQL
sql_path        NULL

So only difference is routine_catalog is 'def' in 6.0 while it is NULL in 5.1. So what is the bug about? Should we implement routine_catalog in 5.1 or?

Thank you for the feedback.

Currently documentation contains information about it:

http://dev.mysql.com/doc/refman/6.0/en/routines-table.html
INFORMATION_SCHEMA  Name 	mysql.proc Name 	Remarks
ROUTINE_CATALOG 	  	def

and

INFORMATION_SCHEMA  Name 	mysql.proc Name 	Remarks
ROUTINE_CATALOG 	  	NULL

So I close the report as "Not a Bug", because behavior is documented.