MySQL Bugs: #15137: CREATE TABLE ... SECLECT .. from a view with stored function doesn't work

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

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

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>

Please take into an account that it is possible to create a TEMPORARY table from x_view.

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

Approved over email with 2 comments.

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.