MySQL Bugs: #12472: CREATE TABLE t AS SELECT spfunc gives "Table not locked with LOCK TABLES" error

Bug #12472	CREATE TABLE t AS SELECT spfunc gives "Table not locked with LOCK TABLES" error
Submitted:	9 Aug 2005 20:59	Modified:	22 May 2006 20:09
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Any (any)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Attempt to run a statement 
CREATE TABLE t AS SELECT spfunc() 
where spfunc uses a table produces "Table ... was not locked with LOCK TABLES".  

How to repeat:
Run:
create table t1 as select 1 a;
delimiter //
create function f2() returns int deterministic reads sql data 
begin  
  return (select max(a) from t1); 
end; //

delimiter //

create table t2 as select f2();

And get the error:
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Suggested fix:
I think such statements should be supported. If they aren't, we need to document what class of statements is not supported (e.g. no SP function calls in DDL statements??).

I also just found this problem. I think this should be solved before MySQL 5 becomes a Production Release.

Here's my example:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t1 (
    ->   id int(11) NOT NULL auto_increment,
    ->   `name` varchar(20) NOT NULL default '',
    ->   decval decimal(6,2) NOT NULL default '0.00',
    ->   PRIMARY KEY  (id),
    ->   KEY ind_decval (decval)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (1, 'abc', 4.00);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (2, 'def', 9.70);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (3, 'ghi', 6.40);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t1 (id, name, decval) VALUES (4, 'slf', 4.20);
Query OK, 1 row affected (0.05 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> CREATE TABLE t2 (
    ->   id int(11) NOT NULL auto_increment,
    ->   id_t1 int(11) NOT NULL default '0',
    ->   decval decimal(6,2) NOT NULL default '0.00',
    ->   PRIMARY KEY  (id),
    ->   KEY id_t1 (id_t1),
    ->   KEY ind_decval (decval)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (1, 2, 8.50);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (2, 1, 8.50);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (4, 2, 6.20);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (5, 3, 4.00);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (6, 2, 4.20);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (8, 3, 5.60);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (9, 4, 8.10);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (10, 2, 0.70);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (12, 1, 4.50);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (29, 3, 5.60);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (30, 4, 8.10);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (31, 2, 0.70);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO t2 (id, id_t1, decval) VALUES (33, 1, 4.50);
Query OK, 1 row affected (0.05 sec)

mysql> ALTER TABLE `t2`
    ->   ADD CONSTRAINT t2_ibfk_1 FOREIGN KEY (id_t1) REFERENCES t1 (id);
Query OK, 13 rows affected (0.08 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS getValue$$
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE FUNCTION  getValue(_id INT) RETURNS decimal(6,2)
    -> BEGIN
    ->   DECLARE _retval DECIMAL(6,2);
    ->
    ->   SELECT decval FROM t1 WHERE id = _id INTO _retval;
    ->
    ->   RETURN _retval;
    ->
    -> END$$
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> DELIMITER ;
mysql> drop table if exists t3;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show warnings;
+-------+------+--------------------+
| Level | Code | Message            |
+-------+------+--------------------+
| Note  | 1051 | Unknown table 't3' |
+-------+------+--------------------+
1 row in set (0.06 sec)

mysql> create table t3 select id, getValue(id_t1) as decval_t1, decval as decval
_t2 from t2;
ERROR 1100 (HY000): Table 't3' was not locked with LOCK TABLES
mysql>

I have the same basic problem. I want to create a table from a VIEW that contains calculated columns that use functions. If I remove the calculated columns from the view the problem goes away. I can view all records in the view with a select statement, so I would expect to be able to create a table using the same select statement.

Bug #14150 is marked as duplicate of this bug.

The original bug description "where spfunc uses a table" is not completely correct. The bug occurs also on a deterministic function that doesn't make use of any tables if it resides in another database:

localhost-test [root]> use test;
Database changed
localhost-test [root]> create function f() returns int deterministic return 1;
Query OK, 0 rows affected (0.52 sec)

localhost-(none) [root]> create database test2;
Query OK, 1 row affected (0.00 sec)

localhost-(none) [root]> use test2
Database changed
localhost-test2 [root]> create table t1 (i INT);
Query OK, 0 rows affected (0.00 sec)

localhost-test2 [root]> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0

localhost-test2 [root]> CREATE TABLE t2 SELECT test.f() FROM t1;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
localhost-test2 [root]> CREATE TABLE test2.t2 SELECT test.f() FROM test2.t1;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

It's the same behaviour from a users point of view (that's why I added it here), but it might come from a different source internally (so please tell me, if I should open a separate report for this), it could for example be related to my bug #18444.

This bug is fixed by the same patch that fixes Bug#15137

Fixed in 5.0.22 and 5.1.11

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

Noted in 5.0.22, 5.1.11 changelogs.