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

[17 Sep 2006 19:30] Dmitry Lenev
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;
[17 Sep 2006 20:08] MySQL Verification Team
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>
[17 Sep 2006 20:10] MySQL Verification Team
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>
[18 Jul 2007 13:09] 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/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.
[18 Jul 2007 13:10] Konstantin Osipov
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.
[25 Jul 2007 8:47] Bugs System
Pushed into 5.0.48
[25 Jul 2007 8:48] Bugs System
Pushed into 5.1.21-beta
[27 Jul 2007 5:19] Paul DuBois
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.
[7 Aug 2007 16:27] Jon Stephens
Fix for 5.1 moved to 5.1.19 changelog per email from Joerg