Bug #31767 DROP FUNCTION name resolution
Submitted: 22 Oct 2007 23:40 Modified: 6 Mar 2010 20:14
Reporter: Marc ALFF Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[22 Oct 2007 23:40] Marc ALFF
Description:
Name resolution for the DROP FUNCTION statement is broken:

1)

DROP FUNCTION db.foo should drop ONLY Stored functions,
and not drop UDF.

2)

DROP FUNCTION foo should use the same name resolution as
SELECT foo().

In particular, when both a UDF ans Stored Function in the current database exist,
the name "foo" in a select statement and "foo" in a DROP FUNCTION statement
should point to the *same* object

How to repeat:
--source include/have_udf.inc

--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";

select metaphon("Hello");
drop function if exists test.metaphon;

# This is a bug, the UDF should not be dropped
--error ER_SP_DOES_NOT_EXIST
select metaphon("Hello");

CREATE FUNCTION test.metaphon(a TEXT) RETURNS TEXT return "This is a SF";

select metaphon("Hello");

--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";

# Uses the UDF
select metaphon("Hello");

# Uses the SF
select test.metaphon("Hello");

# Bug here, drop metaphon drops the SF, not the UDF
drop function if exists metaphon;

# Should call the SF
select metaphon("Hello");

----------

Current results:

CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
select metaphon("Hello");
metaphon("Hello")
HL
drop function if exists test.metaphon;
select metaphon("Hello");
ERROR 42000: FUNCTION test.metaphon does not exist
CREATE FUNCTION test.metaphon(a TEXT) RETURNS TEXT return "This is a SF";
select metaphon("Hello");
metaphon("Hello")
This is a SF
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
select metaphon("Hello");
metaphon("Hello")
HL
select test.metaphon("Hello");
test.metaphon("Hello")
This is a SF
drop function if exists metaphon;
select metaphon("Hello");
metaphon("Hello")
HL

Suggested fix:
In sql_parse.cc,
case SQLCOM_DROP_FUNCTION:
drop UDF or Stored functions in the correct order,
and behave differently if "foo" or "db.foo" (non qualified / fully qualified) names are used.
[13 Mar 2008 0:03] 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/commits/43889

ChangeSet@1.2604, 2008-03-12 18:03:23-06:00, malff@lambda.hsd1.co.comcast.net. +9 -0
  Bug#31767 (DROP FUNCTION name resolution)
  
  Before this fix,
  DROP FUNCTION <db>.<name> could drop a user defined function named <name>.
  
  The resolution of a <name> to either a UDF or a stored function was
  different in the following statements:
  - DROP FUNCTION <name>
  - SELECT <name>(...)
  
  With this fix, the implementation of DROP FUNCTION has been changed,
  so that:
  - DROP FUNCTION <db>.<name> only drop the stored function <db>.<name>
  - DROP FUNCTION <name> resolve the <name> using rules consistent
  with the SELECT <name>() statement.
[1 Apr 2008 1:46] 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/commits/44716

ChangeSet@1.2585, 2008-03-31 19:45:52-06:00, malff@lambda.hsd1.co.comcast.net. +10 -0
  Bug#31767 (DROP FUNCTION name resolution)
  
  Before this fix,
  DROP FUNCTION <db>.<name> could drop a user defined function named <name>.
  
  The resolution of a <name> to either a UDF or a stored function was
  different in the following statements:
  - DROP FUNCTION <name>
  - SELECT <name>(...)
  
  With this fix, the implementation of DROP FUNCTION has been changed,
  so that:
  - DROP FUNCTION <db>.<name> only drop the stored function <db>.<name>
  - DROP FUNCTION <name> resolve the <name> using rules consistent
  with the SELECT <name>() statement.
[28 May 2008 10:02] Bugs System
Pushed into 6.0.6-alpha
[30 Jul 2008 18:20] Paul DuBois
Noted in 6.0.6 changelog.

For DROP FUNCTION db_name.func_name (that is, when the function name
is qualified with the database name), the statement should apply only
to a stored function named func_name in the given database. However,
if a UDF with the same name existed, the statement dropped the UDF
instead.
[23 Feb 2010 18:46] 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/commits/101242

3106 Marc Alff	2010-02-23
      Bug#31767 DROP FUNCTION name resolution
      
      Backport to 5.5.99
[23 Feb 2010 18:52] Marc ALFF
Backport for 5.5.99 queued in mysql-next-mr-bugfixing
[25 Feb 2010 19:46] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100225194420-p60r4u90tszj8q2x) (version source revid:marc.alff@sun.com-20100223184751-np8t6xb8lgvvw1oa) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 19:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100225194305-h49uyjrlfl3mwo60) (version source revid:wlad@sun.com-20100223223036-f9tvi5gqrtj91r5o) (pib:16)
[25 Feb 2010 19:50] Paul DuBois
Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 11:02] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:alik@sun.com-20100225195857-farb6yvy8x06bylj) (merge vers: 5.5.99-m3) (pib:16)
[6 Mar 2010 20:14] Paul DuBois
Noted in 5.5.3 changelog.