MySQL Forums
Forum List  »  Memory Storage Engine

Trouble aggregating data in memory table within stored proc
Posted by: Jonathan Overholt
Date: May 10, 2010 01:40PM

I have two stored routines, one which aggregates data points to timeslots within a day, and another that calls the first in a loop (not many iterations) to provide a daily summary. Each creates and populates the data to be returned in a TEMPORARY ENGINE=MEMORY table.

The first routine works great on its own, but when it's CALLed from within the second, I'm not getting the desired result. I can SELECT data from it, I can even SELECT COUNT(*), but if I SELECT SUM(...) it yields NULL.

First temporary table is defined thus:
CREATE TEMPORARY TABLE ttLabAvailabilityTimeslots (
`LabID` INT UNSIGNED NOT NULL,
`TimeslotStart` DATETIME NOT NULL,
`TimeslotEnd` DATETIME NOT NULL,
`TotalSlots` INT UNSIGNED NOT NULL)
ENGINE = MEMORY;

It returns with 10-50 rows. The second procedure's inner loop starts like this:

CALL spGetLabAvailabilityTimeslots (LabID, CurDate);
SELECT SUM(TotalSlots) FROM ttLabAvailabilityTimeslots;

The code works at the command line, but within the stored procedure it's returning NULL. Is this a known limitation in the 5.0 series? If so, is it fixed in 5.1? My distro doesn't support 5.1 in its current version, but I can rebuild the server to support this if I need to.

Edit: A little more prodding shows that it's not even the aggregation function that's throwing it off. SELECT * shows all of the data, but SELECT TotalSlots shows NULL for each row. It's the only column exhibiting this behavior. I can provide a full database creation script including these routines to anyone in a position to help, it's just a bit long to include inline here.

Thanks in advance.
--Jonathan Overholt



Edited 1 time(s). Last edit at 05/10/2010 03:13PM by Jonathan Overholt.

Options: ReplyQuote


Subject
Views
Written By
Posted
Trouble aggregating data in memory table within stored proc
6403
May 10, 2010 01:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.