MySQL Bugs: #14150: create table ... select function

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:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

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.

verified with 5.0bk, the following works though:

create table t2 like t1;
insert into t2 select ftest();

Igor, this is a duplicate of Bug#12472. Reassigning to Dmitri.

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!