Bug #14150 create table ... select function_call() doesn't work in some cases
Submitted: 19 Oct 2005 20:18 Modified: 8 Feb 2006 11:32
Reporter: Markus Popp Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.13-rc-nt-max OS:Microsoft Windows (Windows XP)
Assigned to: Dmitry Lenev CPU Architecture:Any

[19 Oct 2005 20:18] Markus Popp
Description:
The command 'create table ... select function_call()' sends the message

ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

if the function refers to another table. However, if the function returns a deterministic value, the creation/insertion works.

How to repeat:
mysql> create table t1 (d date);
Query OK, 0 rows affected (0.03 sec)

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

mysql> delimiter //
mysql> create function ftest() returns date
    -> begin
    -> declare d date;
    -> select max(d) from t1 into d;
    -> return d;
    -> end//
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> create table t2 select ftest();
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

Suggested fix:
The returned value from the function should be inserted into the newly created table.
[19 Oct 2005 20:57] Hartmut Holzgraefe
verified with 5.0bk, the following works though:

create table t2 like t1;
insert into t2 select ftest();
[8 Feb 2006 11:16] Konstantin Osipov
Igor, this is a duplicate of Bug#12472. Reassigning to Dmitri.
[8 Feb 2006 11:32] Dmitry Lenev
Hi, Markus!

This bug is duplicate of bug #12472  "CREATE TABLE t AS SELECT spfunc gives "Table not locked with LOCK TABLES" error" so I have marked it as such.

Thanks for your interest in MySQL!