Bug #82350 Cannot create a stored function if a database is not selected
Submitted: 26 Jul 2016 22:35 Modified: 17 May 2018 16:23
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: create function, No database selected, regression

[26 Jul 2016 22:35] Davi Arnaut
Description:
Cannot create a stored function if a database is not selected and the function
being created invokes another function (without using a fully qualified name).

This works in MySQL 5.6

How to repeat:
mysql> SELECT DATABASE();
DATABASE()
NULL
mysql> CREATE DATABASE IF NOT EXISTS test;
mysql> CREATE FUNCTION `test`.f1() RETURNS INT RETURN 0;
mysql> CREATE FUNCTION `test`.f2() RETURNS INT RETURN f1();
ERROR 1046 (3D000): No database selected
mysql>
[27 Jul 2016 1:05] MySQL Verification Team
Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.33 Source distribution 2016-JUL-09

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 >  SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql 5.6 > CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql 5.6 > CREATE FUNCTION `test`.f1() RETURNS INT RETURN 0;
Query OK, 0 rows affected (0.08 sec)

mysql 5.6 >  CREATE FUNCTION `test`.f2() RETURNS INT RETURN f1();
Query OK, 0 rows affected (0.08 sec)

mysql 5.6 >

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.15 Source distribution 2016-JUL-09

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 >  SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql 5.7 > CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql 5.7 > CREATE FUNCTION `test`.f1() RETURNS INT RETURN 0;
Query OK, 0 rows affected (0.16 sec)

mysql 5.7 >  CREATE FUNCTION `test`.f2() RETURNS INT RETURN f1();
ERROR 1046 (3D000): No database selected
mysql 5.7 >
[17 May 2018 16:23] Paul DuBois
Posted by developer:
 
Fixed in 5.7.23, 8.0.12.

CREATE FUNCTION produced an error if there was no default database
and the function body called another function.