MySQL Bugs: #22427: create table if not exists + stored function results in inconsistent behavior

Bug #22427	create table if not exists + stored function results in inconsistent behavior
Submitted:	17 Sep 2006 19:30	Modified:	27 Jul 2007 5:19
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.25-bk/5.1BK	OS:	Linux (Linux Suse 10.1)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any
Tags:	rt_q1_2007, stored function

Description:
Attempt to use CREATE TABLE IF NOT EXISTS ... SELECT statement that uses stored function which refernces table to be created (or updated) emits unexpected error message. Such statements should either be prohibited and emit clear error message
or allowed and work correctly (this is quite possible as CREATE IF NOT EXISTS ... SELECT statement is interpreted as INSERT ... SELECT if subject table exists).
See also similar bug#15522.

How to repeat:
create table t1 (i int);
insert into t1 values (1);
create function f1 () returns int return (select max(i) from t1);
select f1();
# The following emits error
# ERROR 1213: Deadlock found when trying to get lock
create table if not exists t1 select f1() as i;

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

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

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> create function f1 () returns int return (select max(i) from t1);
Query OK, 0 rows affected (0.02 sec)

mysql> select f1();
+------+
| f1() |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> create table if not exists t1 select f1() as i;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

Thank you for the bug report.

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

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

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> create function f1 () returns int return (select max(i) from t1);
Query OK, 0 rows affected (0.03 sec)

mysql> select f1();
+------+
| f1() |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> # The following emits error
mysql> # ERROR 1213: Deadlock found when trying to get lock
mysql> create table if not exists t1 select f1() as i;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
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/31084

ChangeSet@1.2532, 2007-07-18 17:09:03+04:00, kostja@bodhi.(none) +2 -0
  Add a test case for Bug#22427 create table if not exists + stored 
  function results in inconsistent behavior.
  
  The bug itself was fixed by the patch for bug 20662.

Queued the test case in 5.0-runtime.
The bug itself was fixed by a patch for Bug#20662.
Not marking as a duplicate as it is really a different bug, that just happened to be fixed by the approach taken for Bug#20662.

Pushed into 5.0.48

Pushed into 5.1.21-beta

Noted in 5.0.48, 5.1.21 changelogs.

A statement of the form CREATE TABLE IF NOT EXISTS t1 SELECT f1() AS 
i failed with a deadlock error if the stored function f1() referred
to a table with the same name as the to-be-created table. Now it
correctly produces a message that the table already exists.

Fix for 5.1 moved to 5.1.19 changelog per email from Joerg