Re: Can't materialize a view -- bug?

[Gleb Paharenko]

Hello.

In my opinion it is a bug. You may add your comments at:
  http://bugs.mysql.com/bug.php?id=15137

>If this is a bug, I'll be happy to file a report... but I'd really like a
>solution that will let me use the function.

I've noticed that you may create a temporary tables from views like your,
so a workaround is to create a temporary table first, and then a persistent 
table.

>I hit a problem using functions in stored procedures, too, and I'm wondering
>if these are related.

Please provide more information or a test case. Check that you're using
the latest MySQL version (5.0.16 now). Functions work in stored
procedures:

drop procedure if exists psignal;
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);
$$
CREATE procedure psignal()
DETERMINISTIC
BEGIN
SELECT get_signal(0,1,1);
END;
$$
delimiter ;
call psignal();

[gleb@blend mysql-debug-5.0.16-linux-i686-glibc23]$ lmysql <p.sql
get_signal(0,1,1)
0



Nick Arnett <narnett@stripped> wrote:
>I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit
>a strange error.
>
>Here's the scenario.
>
>CREATE OR REPLACE VIEW x_view AS
>SELECT [select statement, blah, blah, blah];
>
>DROP TABLE IF EXISTS x_mview;
>CREATE TABLE x_mview SELECT * FROM x_view;
>
>That was all working fine until I created a function that I use inside of
>the view. Now the last statement, the one that would materialize the view,
>returns a table locking error!
>
>ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES
>
>It's difficult to lock a table that doesn't exist yet...
>
>"SELECT * FROM x_view" works fine.
>
>The function is a simple bit of logic (it tests a count, moving average and
>standard deviation to see if the count is more or less than two standard
>deviations from the mean):
>
>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);
>
>If this is a bug, I'll be happy to file a report... but I'd really like a
>solution that will let me use the function.
>
>I hit a problem using functions in stored procedures, too, and I'm wondering
>if these are related.
>
>Nick


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com