Fix for bugs#12472/#15137 'CREATE TABLE ... SELECT ... which explicitly

or implicitly uses stored function gives "Table not locked" error'

CREATE TABLE ... SELECT ... statement which was explicitly or implicitly
(through view) using stored function gave "Table not locked" error.

The actual bug resides in the current locking scheme of CREATE TABLE SELECT
code, which first opens and locks tables of the SELECT statement itself,
and then, having SELECT tables locked, creates the .FRM, opens the .FRM and
acquires lock on it. This scheme opens a possibility for a deadlock, which
was present and ignored since version 3.23 or earlier. This scheme also
conflicts with the invariant of the prelocking algorithm -- no table can
be open and locked while there are tables locked in prelocked mode.

The patch makes an exception for this invariant when doing CREATE TABLE ...
SELECT, thus extending the possibility of a deadlock to the prelocked mode.
We can't supply a better fix in 5.0.


mysql-test/r/sp.result:
  Added tests for bugs#12472/#15137 'CREATE TABLE ... SELECT ... which
  explicitly or implicitly uses stored function gives "Table not locked" error'
mysql-test/t/sp.test:
  Added tests for bugs#12472/#15137 'CREATE TABLE ... SELECT ... which
  explicitly or implicitly uses stored function gives "Table not locked" error'
sql/mysql_priv.h:
  Added flag which can be passed to open_table() routine in order to ignore
  set of locked tables and prelocked mode.
  We don't need declaration of create_table_from_items() any longer as it was
  moved into sql_insert.cc and made static.
sql/sql_base.cc:
  open_table():
    Added flag which allows open table ignoring set of locked tables and
    prelocked mode.
sql/sql_insert.cc:
  Moved create_table_from_items() from sql_table.cc to sql_insert.cc as it was
  not used outside of sql_insert.cc and contains code which is specific for
  CREATE TABLE ... SELECT.
  Also now when we are executing CREATE TABLE ... SELECT ... statement which
  SELECT part requires execution in prelocked mode we ignore set of locked
  tables in order to get access to the table we just have created.
  We probably don't want to do this if we are under real LOCK TABLES since
  it will widen window for deadlock too much.
sql/sql_table.cc:
  Moved create_table_from_items() routine into sql_insert.cc, since it was not
  used anywhere outside of this file and contains logic which is specific for
  CREATE TABLE ... SELECT statement.

2 files changed, 48 insertions, 0 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 3e139f8cce5..9dbaf228c95 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4837,4 +4837,29 @@ c	2
 b	3
 a	1
 delete from t1|
+drop function if exists bug12472|
+create function bug12472() returns int return (select count(*) from t1)|
+create table t3 as select bug12472() as i|
+show create table t3|
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `i` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t3|
+i
+0
+drop table t3|
+create view v1 as select bug12472() as j|
+create table t3 as select * from v1|
+show create table t3|
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `j` bigint(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t3|
+j
+0
+drop table t3|
+drop view v1|
+drop function bug12472|
 drop table t1,t2;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 9e1afa53149..515893ed982 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5684,6 +5684,29 @@ delete from t1|
 
 
 #
+# BUG#12472/BUG#15137 'CREATE TABLE ... SELECT ... which explicitly or
+# implicitly uses stored function gives "Table not locked" error'.
+#
+--disable_warnings
+drop function if exists bug12472|
+--enable_warnings
+create function bug12472() returns int return (select count(*) from t1)|
+# Check case when function is used directly
+create table t3 as select bug12472() as i|
+show create table t3|
+select * from t3|
+drop table t3|
+# Check case when function is used indirectly through view
+create view v1 as select bug12472() as j|
+create table t3 as select * from v1|
+show create table t3|
+select * from t3|
+drop table t3|
+drop view v1|
+drop function bug12472|
+
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings