| 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 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?
Description: The function BidLow(InputA DATETIME, InputB INT) returns a decimal. When run with this: SELECT BidLow("0000-00-00",60); It executes properly and returns NULL. Another function that returns DATETIME HighTicks(InputA DATETIME, InputB INT) when run with this: SELECT HighTicks("0000-00-00",120); also executes properly and returns "0000-00-00". The problem am facing is if I nest the functions like this: SELECT BidLow(HighTicks("0000-00-00",120),60); I get the error 1048 (230000) column ... cannot be null. I need to execute these nested functions over a large number of rows and sometimes the result will be null. How to repeat: SELECT BidLow(HighTicks("0000-00-00",120),60);