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();
[EMAIL PROTECTED] mysql-debug-5.0.16-linux-i686-glibc23]$ lmysql p.sql
get_signal(0,1,1)
0
Nick Arnett [EMAIL PROTECTED] 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
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
