Bug #15137 CREATE TABLE ... SECLECT .. from a view with stored function doesn't work
Submitted: 22 Nov 2005 15:01 Modified: 22 May 2006 20:10
Reporter: Gleb Paharenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.16 OS:Linux (Linux)
Assigned to: Dmitry Lenev CPU Architecture:Any

[22 Nov 2005 15:01] Gleb Paharenko
Description:
First reported at:
  http://lists.mysql.com/mysql/191980

When I try to create a table from a view which has referense to a stored routine (function)
MySQL throws a strange error:
  ERROR 1100 (HY000) at line 21: Table 'x_mview' was not locked with LOCK TABLES

While it works perfectly if I'm creating a temporary table

How to repeat:
mysql < v.sql

cat v.sql

drop function if exists get_signal;
delimiter $$
CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv
MEDIUMINT(8)) RETURNS TINYINT(1)
DETERMINISTIC
RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma -
(2 * stdv),
IF (cnt >= ma + (2 * stdv), 1, -1),
0);
$$
delimiter ;

drop table if exists tvtable;
create table tvtable(a int);
insert into tvtable set a = 1;
create or replace view x_view AS
--select a from tvtable;
select get_signal(a,1,1) from tvtable;
DROP TABLE IF EXISTS x_mview;
--CREATE TEMPORARY TABLE x_mview SELECT * FROM x_view;
CREATE  TABLE x_mview SELECT * FROM x_view;

SELECT * from x_mview;

It produces an error:
ERROR 1100 (HY000) at line 21: Table 'x_mview' was not locked with LOCK TABLE

If I change the last create statement to 
CREATE TEMPORARY table it works fine:
[gleb@blend mysql-debug-5.0.16-linux-i686-glibc23]$ lmysql <v.sql
get_signal(a,1,1)
0

the modified v.sql (only comments has changed)

drop function if exists get_signal;
delimiter $$
CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv
MEDIUMINT(8)) RETURNS TINYINT(1)
DETERMINISTIC
RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma -
(2 * stdv),
IF (cnt >= ma + (2 * stdv), 1, -1),
0);
$$
delimiter ;

drop table if exists tvtable;
create table tvtable(a int);
insert into tvtable set a = 1;
create or replace view x_view AS
--select a from tvtable;
select get_signal(a,1,1) from tvtable;
DROP TABLE IF EXISTS x_mview;
CREATE TEMPORARY TABLE x_mview SELECT * FROM x_view;
--CREATE  TABLE x_mview SELECT * FROM x_view;

SELECT * from x_mview;

Suggested fix:
Fix that this will work with all tables, not only with temporary
[22 Nov 2005 16:29] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this using 5.0.16:

mysql> delimiter $$
mysql> CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv
    -> MEDIUMINT(8)) RETURNS TINYINT(1)
    -> DETERMINISTIC
    -> RETURN IF (ma > 9 AND stdv > 0 AND (cnt >= ma + (2 * stdv)) OR cnt <= ma -
    -> (2 * stdv),
    -> IF (cnt >= ma + (2 * stdv), 1, -1),
    -> 0);
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> drop table if exists tvtable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table tvtable(a int);
insert into tvtable set a = 1;
create or replace view x_view AS
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tvtable set a = 1;
Query OK, 1 row affected (0.00 sec)

mysql> create or replace view x_view AS
    -> --select a from tvtable;
    -> select get_signal(a,1,1) from tvtable;
DROP TABLE IF EXISTS x_mview;
--CREATE TEMPORARY TABLE x_mview SELECT * FROM x_view;
CREATE  TABLE x_mview SELECT * FROM x_view;
Query OK, 0 rows affected (0.02 sec)

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

mysql> --CREATE TEMPORARY TABLE x_mview SELECT * FROM x_view;
mysql> CREATE  TABLE x_mview SELECT * FROM x_view;

SELECT * from x_mview;
ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES
mysql>
[22 Nov 2005 16:33] Gleb Paharenko
Please take into an account that it is possible to create a TEMPORARY table from x_view.
[18 Feb 2006 12:02] Dan Kloke
using the --external-locking variable changes the error to:

ERROR 11 (HY000): Can't unlock file (Errcode: 11)

at least on Win2k.

my current workaround it to create the temptable, then create table .. select .. the final table from the temp table, then drop the temp table.

i think i am getting this error when creating table from queries involving slow views or even slow selects (that have lots of unindexed joins), but not necessarily as a direct result of using a stored function. since it only happens on slow selects, i havent got a good reproduction routine for it yet. however a simple create..select statement with a stored function doesnt necessarily fail:

mysql>DELIMITER :)

mysql>DROP FUNCTION IF EXISTS `test`.`age_seg` :)
mysql>CREATE FUNCTION `test`.`age_seg` (age int) RETURNS CHAR(6)
BEGIN
   RETURN IF(age<18,'und18',IF(age<=24,'18-24',IF(age<=34,'25-34',IF(age<=44,'35-44',IF(age<=54,'45-54',IF(age<=64,'55-64',IF(age<=74,'65-74','75plus')))))));
END :)

mysql>DELIMITER ;

mysql> create table test select age_seg(48);
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------------+
| ageseg(48) |
+------------+
| 45-54      |
+------------+
1 row in set (0.08 sec)

i found this bug thread while searching before submitting a feature request:

extend the syntax for CREATE [TEMPORARY] TABLE tbl_name SELECT select_stmt .. to:

CREATE  [TEMPORARY] TABLE [LOCK] tbl_name SELECT select_stmt ...

this of course means that the LOCK feature has to make up an alias.. but since that would be internal to the statement its maybe not necessary to provide one? maybe? and of course the table should be unlocked either explicitly ior implicitly afterwartds.

or hey, just lock/unlock the table being created by default. this would probably be a good idea anyway.. but what do i know.

if i come up with a routine to better demonstrate this issue i will post it.

thanks
[20 Apr 2006 20:50] Konstantin Osipov
Approved over email with 2 comments.
[18 May 2006 21:11] Dmitry Lenev
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.
[22 May 2006 20:09] Paul DuBois
Noted in 5.0.22, 5.1.11 changelogs.