Bug #65744 | Nested functions not running | ||
---|---|---|---|
Submitted: | 27 Jun 2012 4:09 | Modified: | 6 Jul 2012 6:56 |
Reporter: | STEVEN NJUKI | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 5.5.25 | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[27 Jun 2012 4:09]
STEVEN NJUKI
[27 Jun 2012 5:25]
Valeriy Kravchuk
Please, send complete test case to copy/paste, with definitions for all stored functions and tables used, and some data to demonstrate the problem. Also specify the exact server version used.
[27 Jun 2012 18:37]
STEVEN NJUKI
Description of table for selection : DROP TABLE IF EXISTS my_table ; CREATE TABLE my_table (ticks DATETIME, bid DECIMAL(6,5), ask DECIMAL(6,5) ) ENGINE MYISAM; The parent function TicksID: DELIMITER // DROP FUNCTION IF EXISTS TicksID; CREATE FUNCTION TicksID(RowTicks DATETIME, ShortSecondsInterval INT, LongSecondsInterval INT) RETURNS DECIMAL(21,13) READS SQL DATA BEGIN DECLARE _result DECIMAL(21,13) DEFAULT 0.0; DECLARE _peak_lag INT; DECLARE _peak_time DATETIME; DECLARE _peak_desc VARCHAR(2000); DECLARE _long_low_bid DECIMAL(6,5); DECLARE _long_high_bid DECIMAL(6,5); DECLARE _short_low_bid DECIMAL(6,5); DECLARE _short_high_bid DECIMAL(6,5); DECLARE _short_high_time DATETIME; DECLARE _short_low_time DATETIME; DECLARE _long_high_time DATETIME; DECLARE _long_low_time DATETIME; DECLARE _low_lag INT; DECLARE _high_lag INT; SET _long_low_bid = BidLow(RowTicks,LongSecondsInterval); SET _long_high_bid = BidHigh(RowTicks,LongSecondsInterval); SET _short_low_bid = BidLow(RowTicks,ShortSecondsInterval); SET _short_high_bid = BidHigh(RowTicks,ShortSecondsInterval); IF COALESCE(HighTicks(RowTicks,ShortSecondsInterval),"1998-12-31") = NULL THEN SET _short_high_time = "1998-12-31"; END IF; IF COALESCE(LowTicks(RowTicks,ShortSecondsInterval),"1998-12-31") = NULL THEN SET _short_low_time = "1998-12-31"; END IF; IF COALESCE(HighTicks(RowTicks,LongSecondsInterval),"1998-12-31") = NULL THEN SET _long_high_time = "1998-12-31"; END IF; IF COALESCE(LowTicks(RowTicks,LongSecondsInterval),"1998-12-31") = NULL THEN SET _long_low_time = "1998-12-31"; END IF; SET _low_lag = TIME_TO_SEC(TIMEDIFF(_short_low_time,_long_low_time)); SET _high_lag = TIME_TO_SEC(TIMEDIFF(_short_high_time,_long_high_time)); SET _peak_lag = LEAST ( TIME_TO_SEC(TIMEDIFF(RowTicks,_short_high_time)), TIME_TO_SEC(TIMEDIFF(RowTicks,_short_low_time)) ); SET _result = 0.0; IF _peak_lag = TIME_TO_SEC(TIMEDIFF(RowTicks,_short_low_time)) THEN SET _result = -1*(_long_high_bid-_short_low_bid)/(_low_lag+_high_lag); END IF; IF _peak_lag = TIME_TO_SEC(TIMEDIFF(RowTicks,_short_high_time)) THEN SET _result = (_short_high_bid-_long_low_bid)/(_low_lag+_high_lag); END IF; RETURN _result; END // DELIMITER ; The nested function HighTicks: DELIMITER // DROP FUNCTION IF EXISTS HighTicks; CREATE FUNCTION HighTicks(RowTicks DATETIME, SecondsInterval INT) RETURNS DATETIME READS SQL DATA BEGIN DECLARE _result DATETIME; SELECT a2.ticks INTO _result FROM (SELECT a1.ticks, MAX(a1.bid) FROM (SELECT * FROM my_table WHERE ticks<RowTicks AND ticks>=TIMESTAMPADD(SECOND,-1*SecondsInterval,RowTicks) ORDER BY bid DESC LIMIT 1) AS a1) AS a2; RETURN GREATEST(COALESCE(_result,"1998-12-31"),"1998-12-31"); END // DELIMITER ;
[28 Jun 2012 7:09]
STEVEN NJUKI
Sorry just to correct my earlier source. The table definition that was bringing this error is DROP TABLE IF EXISTS my_table ; CREATE TABLE my_Table (ticks DATETIME, bid DECIMAL(6,5), ask DECIMAL(6,5), PRIMARY KEY (ticks)) ENGINE MYISAM; An alternative parent function that can also nest the HighTicks function is here: DELIMITER // DROP FUNCTION IF EXISTS BidLow; CREATE FUNCTION BidLow(RowTicks DATETIME, SecondsInterval INT) RETURNS DECIMAL(6,5) READS SQL DATA BEGIN DECLARE _result DECIMAL(6,5) DEFAULT 0.0; SELECT MIN(a1.bid) INTO _result FROM (SELECT * FROM eurusd WHERE ticks<RowTicks AND ticks>=TIMESTAMPADD(SECOND,-1*SecondsInterval,RowTicks) ORDER BY bid ASC LIMIT 1) AS a1; RETURN _result; END // DELIMITER ;
[29 Jun 2012 10:40]
Valeriy Kravchuk
One table is still missing: mysql> SELECT BidLow("0000-00-00",60); ERROR 1146 (42S02): Table 'test.eurusd' doesn't exist Please, provide the entire test case as some .sql file to just run.
[29 Jun 2012 12:36]
STEVEN NJUKI
Sorry the function BidLow should have been [code] DELIMITER // DROP FUNCTION IF EXISTS BidLow; CREATE FUNCTION BidLow(RowTicks DATETIME, SecondsInterval INT) RETURNS DECIMAL(6,5) READS SQL DATA BEGIN DECLARE _result DECIMAL(6,5) DEFAULT 0.0; SELECT MIN(a1.bid) INTO _result FROM (SELECT * FROM my_table WHERE ticks<RowTicks AND ticks>=TIMESTAMPADD(SECOND,-1*SecondsInterval,RowTicks) ORDER BY bid ASC LIMIT 1) AS a1; RETURN _result; END // DELIMITER ; [/code]
[6 Jul 2012 6:16]
Valeriy Kravchuk
This is what I get with recent updated definitions of the table and all the functions: mysql> SELECT BidLow("0000-00-00",60); +-------------------------+ | BidLow("0000-00-00",60) | +-------------------------+ | NULL | +-------------------------+ 1 row in set (0.19 sec) mysql> SELECT HighTicks("0000-00-00",120); +-----------------------------+ | HighTicks("0000-00-00",120) | +-----------------------------+ | 1998-12-31 00:00:00 | +-----------------------------+ 1 row in set (0.22 sec) mysql> SELECT BidLow("1998-12-31 00:00:00",60); +----------------------------------+ | BidLow("1998-12-31 00:00:00",60) | +----------------------------------+ | NULL | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT BidLow(HighTicks("0000-00-00",120),60); ERROR 1048 (23000): Column 'ticks' cannot be null mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.25 | +-----------+ 1 row in set (0.00 sec)
[6 Jul 2012 6:56]
STEVEN NJUKI
My problem is with this error that you have got as well... [code] mysql> SELECT BidLow(HighTicks("0000-00-00",120),60); ERROR 1048 (23000): Column 'ticks' cannot be null [/code] If BidLow() and HighTicks() each work properly with the same values, why does nesting them produce an error?