MySQL Bugs: #28318: CREATE FUNCTION (UDF) requires a schema

Bug #28318	CREATE FUNCTION (UDF) requires a schema
Submitted:	9 May 2007 1:49	Modified:	24 Oct 2007 19:06
Reporter:	Brian Aker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
Try to create a FUNCTION when you are not attached to a particular schema:

mysql> CREATE FUNCTION hello RETURNS STRING SONAME "libskeleton_udf.so";
ERROR 1046 (3D000): No database selected

How to repeat:
sh# mysql

mysql> CREATE FUNCTION hello RETURNS STRING SONAME "libskeleton_udf.so";
ERROR 1046 (3D000): No database selected

Suggested fix:
Creating a function should not be tied to having the schema set.

used to work in 4.1, doesn't anymore in 5.0 and above, 
probably due to CREATE FUNCTION now also being used for
stored functions which do need to be tied to a database?

The problem is with sp_name: rule
The same rule is used for stored routines and UDFs, whereas routines always belong  to a schema, and UDFs do not.

See related Bug#29050

Bug #29816 Syntactically wrong query fails with misleading error message
was marked a duplicate of this bug.
Please also add the test case from Bug#29816 when this bug is fixed.

A regression. Please fix in 5.0.

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

ChangeSet@1.2524, 2007-09-13 13:13:14-03:00, davi@moksha.local +8 -0
  Bug#28318 CREATE FUNCTION (UDF) requires a schema
  Bug#29816 Syntactically wrong query fails with misleading error message
  
  The core problem is that an SQL-invoked function name can be a <schema
  qualified routine name> that contains no <schema name>, but the mysql
  parser insists that all stored procedures (function, procedures and
  triggers) must have a <schema name>, which is not true for functions.
  This problem is especially visible when trying to create a function
  or when a query contains a syntax error after a function call (in the
  same query), both will fail with a "No database selected" message if
  the session is not attached to a particular schema, but the first
  one should succeed and the second fail with a "syntax error" message.
  
  Part of the fix is to revamp the sp name handling so that a schema
  name may be omitted for functions -- this means that the internal
  function name representation may not have a dot, which represents
  that the function doesn't have a schema name. The other part is
  to place schema checks after the type (function, trigger or procedure)
  of the routine is known.

Patch approved, with very minor changes (see email)

Thanks for all the tests.

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

ChangeSet@1.2543, 2007-10-15 19:15:38-06:00, malff@lambda.hsd1.co.comcast.net. +6 -0
  Bug#28318 (CREATE FUNCTION (UDF) requires a schema) -- part II
  
  The root cause of the issue was that the CREATE FUNCTION grammar,
  for User Defined Functions, was using the sp_name rule.
  The sp_name rule is intended for fully qualified stored procedure names,
  like either ident.ident, or just ident but with a default database
  implicitly selected.
  
  A UDF does not have a fully qualified name, only a name (ident), and should
  not use the sp_name grammar fragment during parsing.
  
  The fix is to re-organize the CREATE FUNCTION grammar, to better separate:
  - creating UDF (no definer, can have AGGREGATE, simple ident)
  - creating Stored Functions (definer, no AGGREGATE, fully qualified name)
  
  With the test case provided, another issue was exposed which is also fixed:
  the DROP FUNCTION statement was using sp_name and also failing when no database
  is implicitly selected, when droping UDF functions.
  The fix is also to change the grammar so that DROP FUNCTION works with
  both the ident.ident syntax (to drop a stored function), or just the ident
  syntax (to drop either a UDF or a Stored Function, in the current database)

Pushed into 5.1.23-beta

Pushed into 5.0.52

Noted in 5.0.52, 5.1.23 changelogs.

The parser confused user-defined function (UDF) and stored function
creation for CREATE FUNCTION and required that there be a default
database when creating UDFs, although there is no such requirement.

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha