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:
None 
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
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);
[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?