I am (a newbie) having trouble creating a stored function to return a
date from a table ('reporting_days') a given number of days after a
specified start date. This is the start of a more complex function
that will allow me to add various date values to a start date subject
to business rules held in the reporting_days table, which is why I'm
using a decimal as input then rounding to an int.

Here is the SQL I've tried:

delimiter //
CREATE FUNCTION  Add_Working_Days(Units_to_Add Decimal(10, 2),
Start_Date Date)
    RETURNS date

BEGIN
    DECLARE whole_units int;

	SET whole_units = ROUND(Units_to_Add, 0);

    RETURN SELECT Max(T1.Reporting_Day) FROM (
        SELECT * FROM reporting_days WHERE (Day_Type ='') AND
(reporting_day >= Start_Date) Limit whole_units) as T1;

END//

delimiter ;

I've also tried assigning the return value to a parameter and (using
'SET varReturn = SELECT . . .) and still get a syntax error with the
SELECT part of the function. When I run the following against the
database I get the expected result:

SELECT Max(T1.Reporting_Day)
FROM (SELECT * FROM reporting_days
WHERE (Day_Type ='') AND (reporting_day >= '2010-01-01') Limit 20) as
T1

So I suspect there is something wrong with the way I'm trying to
access the result of the query SQL rather than with the query SQL
itself.

Can anybody point out where I'm going wrong, and also, if there is a
better way to return a scalar value from a query?

Thanks in advance



Martin

On 2010-12-16 06:46, MartinH wrote:
[...]
> delimiter //
> CREATE FUNCTION  Add_Working_Days(Units_to_Add Decimal(10, 2),
> Start_Date Date)
>     RETURNS date
> 
> BEGIN
>     DECLARE whole_units int;
> 
> 	SET whole_units = ROUND(Units_to_Add, 0);
> 
>     RETURN SELECT Max(T1.Reporting_Day) FROM (
>         SELECT * FROM reporting_days WHERE (Day_Type ='') AND
> (reporting_day >= Start_Date) Limit whole_units) as T1;
> 

I think you will get the wrong result here. You return the latest day
from n random days after start_date. You need to order the result set
for this to work.

/Lennart

On Dec 15, 8:13=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-12-16 06:46, MartinH wrote:
> [...]
>
> > delimiter //
> > CREATE FUNCTION =A0Add_Working_Days(Units_to_Add Decimal(10, 2),
> > Start_Date Date)
> > =A0 =A0 RETURNS date
>
> > BEGIN
> > =A0 =A0 DECLARE whole_units int;
>
> > =A0 =A0SET whole_units =3D ROUND(Units_to_Add, 0);
>
> > =A0 =A0 RETURN SELECT Max(T1.Reporting_Day) FROM (
> > =A0 =A0 =A0 =A0 SELECT * FROM reporting_days WHERE (Day_Type =3D'') AND
> > (reporting_day >=3D Start_Date) Limit whole_units) as T1;
>
> I think you will get the wrong result here. You return the latest day
> from n random days after start_date. You need to order the result set
> for this to work.
>
> /Lennart

Agreed, I will need to order the results from the subquery, but would
this be causing the syntax error?

On 2010-12-16 07:21, MartinH wrote:
[...]
>> I think you will get the wrong result here. You return the latest day
>> from n random days after start_date. You need to order the result set
>> for this to work.
>>
>> /Lennart
> 
> Agreed, I will need to order the results from the subquery, but would
> this be causing the syntax error?

Probably not, I was just pointing out that you are likely to get wrong
results from your function. If you post a create table statement for
working days, and some insert statements with sample data, I may be able
to help out.


/Lennart

MartinH <mgheal@gmail.com> wrote:

> Here is the SQL I've tried:
>
> CREATE FUNCTION  Add_Working_Days(Units_to_Add Decimal(10, 2),
> Start_Date Date)
>     RETURNS date
>
> BEGIN
>     DECLARE whole_units int;
>
> 	SET whole_units = ROUND(Units_to_Add, 0);
>
>     RETURN SELECT Max(T1.Reporting_Day) FROM (
>         SELECT * FROM reporting_days WHERE (Day_Type ='') AND
> (reporting_day >= Start_Date) Limit whole_units) as T1;
>
> END
>
> I've also tried assigning the return value to a parameter and (using
> 'SET varReturn = SELECT . . .) and still get a syntax error with the
> SELECT part of the function.

....

> Can anybody point out where I'm going wrong, and also, if there is a
> better way to return a scalar value from a query?

The first way does not work because a function cannot return a result
set. A result set (even with only one row) is not a scalar.

Your second thought is much better: you have to put the result into a
variable and then RETURN that. You just use the wrong syntax.

http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html


XL

On Dec 15, 11:55=A0pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> MartinH <mgh...@gmail.com> wrote:
> > Here is the SQL I've tried:
>
> > CREATE FUNCTION =A0Add_Working_Days(Units_to_Add Decimal(10, 2),
> > Start_Date Date)
> > =A0 =A0 RETURNS date
>
> > BEGIN
> > =A0 =A0 DECLARE whole_units int;
>
> > =A0 =A0SET whole_units =3D ROUND(Units_to_Add, 0);
>
> > =A0 =A0 RETURN SELECT Max(T1.Reporting_Day) FROM (
> > =A0 =A0 =A0 =A0 SELECT * FROM reporting_days WHERE (Day_Type =3D'') AND
> > (reporting_day >=3D Start_Date) Limit whole_units) as T1;
>
> > END
>
> > I've also tried assigning the return value to a parameter and (using
> > 'SET varReturn =3D SELECT . . .) and still get a syntax error with the
> > SELECT part of the function.
>
> ...
>
> > Can anybody point out where I'm going wrong, and also, if there is a
> > better way to return a scalar value from a query?
>
> The first way does not work because a function cannot return a result
> set. A result set (even with only one row) is not a scalar.
>
> Your second thought is much better: you have to put the result into a
> variable and then RETURN that. You just use the wrong syntax.
>
> http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html
>
> XL

Thanks for the guidance - I've managed to narrow down the syntax error
to the use of the whole_units parameter as the value for the LIMIT on
the sub query results. When I hard code a numeric value it works, but
not with the parameter.

I have found in the mySQL documentation that:

The LIMIT clause can be used to constrain the number of rows returned
by the SELECT statement. LIMIT takes one or two numeric arguments,
which must both be nonnegative integer constants (except when using
prepared statements).

As I have already specified the whole_units parameter as an INT I'm
not sure what to try next?

Any suggestions would be appreciated.


Martin

El 16/12/2010 11:43, MartinH escribi�/wrote:
> On Dec 15, 11:55 pm, Axel Schwenke<axel.schwe...@gmx.de>  wrote:
>> MartinH<mgh...@gmail.com>  wrote:
>>> Here is the SQL I've tried:
>>
>>> CREATE FUNCTION  Add_Working_Days(Units_to_Add Decimal(10, 2),
>>> Start_Date Date)
>>>      RETURNS date
>>
>>> BEGIN
>>>      DECLARE whole_units int;
>>
>>>     SET whole_units = ROUND(Units_to_Add, 0);
>>
>>>      RETURN SELECT Max(T1.Reporting_Day) FROM (
>>>          SELECT * FROM reporting_days WHERE (Day_Type ='') AND
>>> (reporting_day>= Start_Date) Limit whole_units) as T1;
>>
>>> END
>>
>>> I've also tried assigning the return value to a parameter and (using
>>> 'SET varReturn = SELECT . . .) and still get a syntax error with the
>>> SELECT part of the function.
>>
>> ...
>>
>>> Can anybody point out where I'm going wrong, and also, if there is a
>>> better way to return a scalar value from a query?
>>
>> The first way does not work because a function cannot return a result
>> set. A result set (even with only one row) is not a scalar.
>>
>> Your second thought is much better: you have to put the result into a
>> variable and then RETURN that. You just use the wrong syntax.
>>
>> http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html
>>
>> XL
>
> Thanks for the guidance - I've managed to narrow down the syntax error
> to the use of the whole_units parameter as the value for the LIMIT on
> the sub query results. When I hard code a numeric value it works, but
> not with the parameter.
>
> I have found in the mySQL documentation that:
>
> The LIMIT clause can be used to constrain the number of rows returned
> by the SELECT statement. LIMIT takes one or two numeric arguments,
> which must both be nonnegative integer constants (except when using
> prepared statements).

A result set with one column and one row is still a result set.


> As I have already specified the whole_units parameter as an INT I'm
> not sure what to try next?

Just follow Axel's suggestion. You need to declare a local variable:

	DECLARE whatever DATE;

.... feed it:

	SET whatever = (
		SELECT Max(T1.Reporting_Day) FROM
		(
			SELECT *
			FROM reporting_days
			WHERE (Day_Type ='')
				AND reporting_day>= Start_Date
			Limit whole_units
		) as T1
	);

	... or:

	SELECT Max(T1.Reporting_Day) INTO whatever FROM
	(
		SELECT *
		FROM reporting_days
		WHERE (Day_Type ='')
			AND reporting_day>= Start_Date
		Limit whole_units
	) as T1;

  ... and return the variable:

	RETURN whatever;



-- 
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On 2010-12-16 11:43, MartinH wrote:
[...]
> 
> As I have already specified the whole_units parameter as an INT I'm
> not sure what to try next?
> 
> Any suggestions would be appreciated.
> 

One way is to use a cursor inside the function. Example:

delimiter //
CREATE FUNCTION  Add_Working_Days(Units_to_Add Decimal(10, 2),
Start_Date Date)
    RETURNS date

BEGIN
    DECLARE whole_units int;
    DECLARE res DATE;
    DECLARE cur1 CURSOR FOR SELECT * FROM reporting_days
                        WHERE reporting_day >= Start_Date
                        ORDER BY reporting_day;

    SET whole_units = ROUND(Units_to_Add, 0);

    OPEN cur1;
    WHILE whole_units >= 0 DO
        FETCH cur1 into res;
        SET whole_units = whole_units - 1;
    END WHILE;

    RETURN res;

END//

delimiter ;


/Lennart

On Dec 16, 1:42=A0am, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-12-16 11:43, MartinH wrote:
> [...]
>
>
>
> > As I have already specified the whole_units parameter as an INT I'm
> > not sure what to try next?
>
> > Any suggestions would be appreciated.
>
> One way is to use a cursor inside the function. Example:
>
> delimiter //
> CREATE FUNCTION =A0Add_Working_Days(Units_to_Add Decimal(10, 2),
> Start_Date Date)
> =A0 =A0 RETURNS date
>
> BEGIN
> =A0 =A0 DECLARE whole_units int;
> =A0 =A0 DECLARE res DATE;
> =A0 =A0 DECLARE cur1 CURSOR FOR SELECT * FROM reporting_days
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 WHERE reporting_day >=3D =
Start_Date
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ORDER BY reporting_day;
>
> =A0 =A0 SET whole_units =3D ROUND(Units_to_Add, 0);
>
> =A0 =A0 OPEN cur1;
> =A0 =A0 WHILE whole_units >=3D 0 DO
> =A0 =A0 =A0 =A0 FETCH cur1 into res;
> =A0 =A0 =A0 =A0 SET whole_units =3D whole_units - 1;
> =A0 =A0 END WHILE;
>
> =A0 =A0 RETURN res;
>
> END//
>
> delimiter ;
>
> /Lennart

Hi Lennart,

I have used your code which creates the function, but then when I try
and use it like this:

SELECT Add_Working_Days_Cursor(10.23, '2010-12-01')

I get an "#1328 - Incorrect number of FETCH variables" error. Any
suggestions?

Thanks



Martin

On 2010-12-16 21:50, MartinH wrote:
[...]
> 
> I have used your code which creates the function, but then when I try
> and use it like this:
> 
> SELECT Add_Working_Days_Cursor(10.23, '2010-12-01')
> 
> I get an "#1328 - Incorrect number of FETCH variables" error. Any
> suggestions?

It works for me (but then again, I have neither your table definition
nor some sample data to try with)

mysql> SELECT Add_Working_Days(1.23, '2010-01-01');
+--------------------------------------+
| Add_Working_Days(1.23, '2010-01-01') |
+--------------------------------------+
| 2010-01-02                           |
+--------------------------------------+
1 row in set (0.01 sec)

My code was just an example so you will have to add some error handling,
for example a continue handler for not found condition. NULL will
probably be appropriate to return in that case.


/Lennart

On Dec 16, 8:40=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-12-16 21:50, MartinH wrote:
> [...]
>
>
>
> > I have used your code which creates the function, but then when I try
> > and use it like this:
>
> > SELECT Add_Working_Days_Cursor(10.23, '2010-12-01')
>
> > I get an "#1328 - Incorrect number of FETCH variables" error. Any
> > suggestions?
>
> It works for me (but then again, I have neither your table definition
> nor some sample data to try with)
>
> mysql> SELECT Add_Working_Days(1.23, '2010-01-01');
> +--------------------------------------+
> | Add_Working_Days(1.23, '2010-01-01') |
> +--------------------------------------+
> | 2010-01-02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> +--------------------------------------+
> 1 row in set (0.01 sec)
>
> My code was just an example so you will have to add some error handling,
> for example a continue handler for not found condition. NULL will
> probably be appropriate to return in that case.
>
> /Lennart

Here is the table definition:

CREATE TABLE IF NOT EXISTS `reporting_days` (
  `Reporting_Day` date NOT NULL,
  `Day_Type` char(1) NOT NULL,
  PRIMARY KEY (`Reporting_Day`),
  KEY `Day_Type` (`Day_Type`)
)

and some sample record inserts:

INSERT INTO `reporting_days` (`Reporting_Day`, `Day_Type`) VALUES
('2008-01-01', ''),
('2008-01-02', ''),
('2008-01-03', ''),
('2008-01-04', ''),
('2008-01-05', 'W'),
('2008-01-06', 'W'),
('2008-01-07', '')

On 2010-12-19 10:57, MartinH wrote:
> On Dec 16, 8:40 pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
> wrote:
>> On 2010-12-16 21:50, MartinH wrote:
>> [...]
>>
>>
>>
>> > I have used your code which creates the function, but then when I try
>> > and use it like this:
>>
>> > SELECT Add_Working_Days_Cursor(10.23, '2010-12-01')
>>
>> > I get an "#1328 - Incorrect number of FETCH variables" error. Any
>> > suggestions?
>>
>> It works for me (but then again, I have neither your table definition
>> nor some sample data to try with)
>>
>> mysql> SELECT Add_Working_Days(1.23, '2010-01-01');
>> +--------------------------------------+
>> | Add_Working_Days(1.23, '2010-01-01') |
>> +--------------------------------------+
>> | 2010-01-02                           |
>> +--------------------------------------+
>> 1 row in set (0.01 sec)
>>
>> My code was just an example so you will have to add some error handling,
>> for example a continue handler for not found condition. NULL will
>> probably be appropriate to return in that case.
>>
>> /Lennart
> 
> Here is the table definition:
> 
> CREATE TABLE IF NOT EXISTS `reporting_days` (
>   `Reporting_Day` date NOT NULL,
>   `Day_Type` char(1) NOT NULL,
>   PRIMARY KEY (`Reporting_Day`),
>   KEY `Day_Type` (`Day_Type`)
> )
> 
> and some sample record inserts:
> 
> INSERT INTO `reporting_days` (`Reporting_Day`, `Day_Type`) VALUES
> ('2008-01-01', ''),
> ('2008-01-02', ''),
> ('2008-01-03', ''),
> ('2008-01-04', ''),
> ('2008-01-05', 'W'),
> ('2008-01-06', 'W'),
> ('2008-01-07', '')


The problem is likely:

select *

in the cursor def. Given your table_def I would aim for something like:

drop function Add_Working_Days;
delimiter //
CREATE FUNCTION  Add_Working_Days(Units_to_Add Decimal(10, 2),
Start_Date Date)
    RETURNS date

BEGIN
    DECLARE whole_units int;
    DECLARE res DATE;
    DECLARE cur1 CURSOR FOR SELECT reporting_day FROM reporting_days
                        WHERE reporting_day >= Start_Date
                          AND Day_Type = ''
                        ORDER BY reporting_day;

    DECLARE CONTINUE HANDLER FOR NOT FOUND return null;

    SET whole_units = ROUND(Units_to_Add, 0);

    OPEN cur1;
    WHILE whole_units >= 0 DO
        FETCH cur1 into res;
        SET whole_units = whole_units - 1;
    END WHILE;

    RETURN res;

END//

delimiter ;

mysql> SELECT Add_Working_Days(1.23, '2008-01-01');
+--------------------------------------+
| Add_Working_Days(1.23, '2008-01-01') |
+--------------------------------------+
| 2008-01-02                           |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT Add_Working_Days(4.23, '2008-01-01');
+--------------------------------------+
| Add_Working_Days(4.23, '2008-01-01') |
+--------------------------------------+
| 2008-01-07                           |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT Add_Working_Days(5.23, '2008-01-01');
+--------------------------------------+
| Add_Working_Days(5.23, '2008-01-01') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
1 row in set (0.00 sec)


One thing that strikes me as odd (but perhaps there is a good reason for
it), is that the table is named working_days and contains an attribute
stating whether it is a working day or not.


/Lennart

On 2010-12-19 12:31, Lennart Jonsson wrote:
[...]

Without using a cursor:

delimiter //
CREATE FUNCTION  Add_Working_Days2(Units_to_Add Decimal(10, 2),
                                   Start_Date Date)
    RETURNS date
BEGIN
    declare res date;

    select reporting_day into res
    from (
        select x.reporting_day,
            (select count(y.reporting_day)
             from reporting_days as y
             where day_type = ''
               and y.reporting_day < x.reporting_day
               and y.reporting_day >= Start_Date
            ) as cnt
        from reporting_days as x
        where day_type = ''
          and reporting_day >= Start_Date
    ) as z
    where cnt = floor(Units_to_Add);

    RETURN res;

END//
delimiter ;


A bit ironic that mysql does not accept:

return select reporting_day
       from ...

or

return select max(reporting_day)
       from ...

On 12/19/2010 6:31 AM, Lennart Jonsson wrote:
> On 2010-12-19 10:57, MartinH wrote:
>> On Dec 16, 8:40 pm, Lennart Jonsson<erik.lennart.jons...@gmail.com>
>> wrote:
>>> On 2010-12-16 21:50, MartinH wrote:
>>> [...]
>>>
>>>
>>>
>>>> I have used your code which creates the function, but then when I try
>>>> and use it like this:
>>>
>>>> SELECT Add_Working_Days_Cursor(10.23, '2010-12-01')
>>>
>>>> I get an "#1328 - Incorrect number of FETCH variables" error. Any
>>>> suggestions?
>>>
>>> It works for me (but then again, I have neither your table definition
>>> nor some sample data to try with)
>>>
>>> mysql>  SELECT Add_Working_Days(1.23, '2010-01-01');
>>> +--------------------------------------+
>>> | Add_Working_Days(1.23, '2010-01-01') |
>>> +--------------------------------------+
>>> | 2010-01-02                           |
>>> +--------------------------------------+
>>> 1 row in set (0.01 sec)
>>>
>>> My code was just an example so you will have to add some error handling,
>>> for example a continue handler for not found condition. NULL will
>>> probably be appropriate to return in that case.
>>>
>>> /Lennart
>>
>> Here is the table definition:
>>
>> CREATE TABLE IF NOT EXISTS `reporting_days` (
>>    `Reporting_Day` date NOT NULL,
>>    `Day_Type` char(1) NOT NULL,
>>    PRIMARY KEY (`Reporting_Day`),
>>    KEY `Day_Type` (`Day_Type`)
>> )
>>
>> and some sample record inserts:
>>
>> INSERT INTO `reporting_days` (`Reporting_Day`, `Day_Type`) VALUES
>> ('2008-01-01', ''),
>> ('2008-01-02', ''),
>> ('2008-01-03', ''),
>> ('2008-01-04', ''),
>> ('2008-01-05', 'W'),
>> ('2008-01-06', 'W'),
>> ('2008-01-07', '')
>
>
> The problem is likely:
>
> select *
>
> in the cursor def. Given your table_def I would aim for something like:
>

Exactly.  Another good reason to never use SELECT *.  You should always 
specify the columns being selected.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:

> CREATE FUNCTION  Add_Working_Days2( ...

(working example, following my initial advise)

> A bit ironic that mysql does not accept:
>
> return select reporting_day
>        from ...

What is ironic about a well documented restriction?

"Statements that return a result set can be used within a stored
 procedcure but not within a stored function"

from http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

and also

"The following additional statements or operations are not permitted
 within stored functions ... Statements that return a result set"

from http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html


XL

On 2010-12-19 17:58, Axel Schwenke wrote:
[...]
> 
> What is ironic about a well documented restriction?
> 

It's ironic because the engine will silently cast between types in other
situations (whether that makes sense or not).


/Lennart

Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
> On 2010-12-19 17:58, Axel Schwenke wrote:
> [...]
>>
>> What is ironic about a well documented restriction?
>
> It's ironic because the engine will silently cast between types in other
> situations (whether that makes sense or not).

Only that this is not a type casting situation. There is no way to
cast a result set to a scalar. Just like there is no way to cast an
array (if such a type would exist in SQL) to a scalar. Of course
there are edge cases like an array with only one element or a result
set with only one row and in that row only one field - and in such
edge cases there would be a straightforward way to do the cast.

But it's not there. Instead there's another straightforward solution
to select into a variable and return that. And that makes a lot of
sense. In fact it is often wished for an opaque "result set" type and
the possibility to return that from a stored routine. And then nobody
would ever want that to be implicitly casted to a scalar.


XL

On 2010-12-19 20:29, Axel Schwenke wrote:
[...]
> Only that this is not a type casting situation. There is no way to
> cast a result set to a scalar. Just like there is no way to cast an
> array (if such a type would exist in SQL)

Arrays where introduced in SQL99. I don't have a copy of that (so I
can't say for sure where), but you can find it under 4.10 Collection
types in SQL2003 (Foundation part). Personally I have never found use
for it though.

> to a scalar. Of course
> there are edge cases like an array with only one element or a result
> set with only one row and in that row only one field - and in such
> edge cases there would be a straightforward way to do the cast.
>

select max(reporting_day) from ...

would qualify quite happily.


> But it's not there. Instead there's another straightforward solution
> to select into a variable and return that. And that makes a lot of
> sense. In fact it is often wished for an opaque "result set" type and
> the possibility to return that from a stored routine. And then nobody
> would ever want that to be implicitly casted to a scalar.
> 

Point taken, and I don't think it is a big deal to declare an extra
variable. Let's say I'm a bit more concerned regarding the other
situations ...

/Lennart

On Dec 20, 1:06=A0pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2010-12-19 20:29, Axel Schwenke wrote:
> [...]
>
> > Only that this is not a type casting situation. There is no way to
> > cast a result set to a scalar. Just like there is no way to cast an
> > array (if such a type would exist in SQL)
>
> Arrays where introduced in SQL99. I don't have a copy of that (so I
> can't say for sure where), but you can find it under 4.10 Collection
> types in SQL2003 (Foundation part). Personally I have never found use
> for it though.
>
> > to a scalar. Of course
> > there are edge cases like an array with only one element or a result
> > set with only one row and in that row only one field - and in such
> > edge cases there would be a straightforward way to do the cast.
>
> select max(reporting_day) from ...
>
> would qualify quite happily.
>
> > But it's not there. Instead there's another straightforward solution
> > to select into a variable and return that. And that makes a lot of
> > sense. In fact it is often wished for an opaque "result set" type and
> > the possibility to return that from a stored routine. And then nobody
> > would ever want that to be implicitly casted to a scalar.
>
> Point taken, and I don't think it is a big deal to declare an extra
> variable. Let's say I'm a bit more concerned regarding the other
> situations ...
>
> /Lennart

Thanks all for your help - I have learned a lot. Not least that the
cursor approach is much quicker than the non-cursor solution (the
cursor takes about 0.003 seconds while the non-cursor solution takes
1.3 - 1.4 seconds for the same input values) so I'll use the cursor
version for this and for similar situations I come across in future.


Martin

On 2010-12-20 05:42, MartinH wrote:
[...]
> Thanks all for your help - I have learned a lot. Not least that the
> cursor approach is much quicker than the non-cursor solution (the
> cursor takes about 0.003 seconds while the non-cursor solution takes
> 1.3 - 1.4 seconds for the same input values) so I'll use the cursor
> version for this and for similar situations I come across in future.
> 

My advise is to always seek for declarative solutions before considering
a cursor, i.e. don't rush ahead and create a cursor for any problem you
encounter ;-)

/Lennart

MartinH <mgheal@gmail.com> wrote:
>
> Thanks all for your help - I have learned a lot. Not least that the
> cursor approach is much quicker than the non-cursor solution (the
> cursor takes about 0.003 seconds while the non-cursor solution takes
> 1.3 - 1.4 seconds for the same input values)

I don't think this is real. In fact I have strong reason to believe
that a non-cursor version will be faster (but not significantly).
To avoid measuring only cache warming effects, you should execute
each version a few times. And you should (for each version) start
from the same position: (re)start the MySQL server before running
the test.

Of course the comparison makes only sense if you run the same query.
Else you will just see the effect of different execution plans.


XL

On 2010-12-20 10:29, Axel Schwenke wrote:
> MartinH <mgheal@gmail.com> wrote:
>>
>> Thanks all for your help - I have learned a lot. Not least that the
>> cursor approach is much quicker than the non-cursor solution (the
>> cursor takes about 0.003 seconds while the non-cursor solution takes
>> 1.3 - 1.4 seconds for the same input values)
> 
> I don't think this is real. In fact I have strong reason to believe
> that a non-cursor version will be faster (but not significantly).

Axel, can you elaborate on why you expect the non-cursor version to be
faster, is it due to context switching or some sort of similar effect?

I don't fancy cursors myself but I would expect the cursor variant to do
a lot less work than my non cursor based version:

select reporting_day into res
    from (
        select x.reporting_day,
            (select count(y.reporting_day)
             from reporting_days as y
             where day_type = ''
               and y.reporting_day < x.reporting_day
               and y.reporting_day >= Start_Date
            ) as cnt
        from reporting_days as x
        where day_type = ''
          and reporting_day >= Start_Date
    ) as z
    where cnt = floor(Units_to_Add);

Given a table with say 1000 rows and start_date somewhere in the middle
I'ld expect the query to *indirectly* touch 500+499+...+1 rows.
Indirectly because we will utilize the index, but we will still have to
do a leaf page scan of the index.

For the cursor:

SELECT reporting_day FROM reporting_days
                        WHERE reporting_day >= Start_Date
                          AND Day_Type = ''
                        ORDER BY reporting_day;

I would expect that start_date is found through the index, and then read
a number of leaf pages in the index.


/Lennart

Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
> On 2010-12-20 10:29, Axel Schwenke wrote:
>>
>> I don't think this is real. In fact I have strong reason to believe
>> that a non-cursor version will be faster (but not significantly).
>
> Axel, can you elaborate on why you expect the non-cursor version to be
> faster, is it due to context switching or some sort of similar effect?

I think we compare apples and pears here. The two versions below use
pretty different queries, so any difference in performance is *not*
a matter of using a cursor or not.

It seems you use the cursor to just read the first FLOOR(Units_to_Add)
matching rows. But then MySQL already has a syntax feature for that:
a LIMIT clause. And of course MySQL can use LIMIT to optimize execution
of a query (i.e. read rows in specified order and stop reading when the
LIMIT is reached)

http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html


A non-cursor version of that query could look like so:

SELECT MAX(inner.day)
  FROM ( SELECT reporting_day AS day
           FROM reporting_days
          WHERE reporting_day >= Start_Date
            AND day_type = ''
       ORDER BY reporting_day
          LIMIT FLOOR(Units_to_Add)
  ) AS inner;

Due to syntax limitations one had to use PREPARE and EXECUTE to put
the `Units_to_Add` value into that query (aka "dynamic SQL").

In order to execute the inner query fast, an index on
(reporting_day, day_type) is needed. The `reporting_day` column allows
to use an index scan to read in sort order and including the `day_type`
column avoids diving to the row to compare day_type = ''


XL

On 2010-12-28 19:32, Axel Schwenke wrote:
> Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
>> On 2010-12-20 10:29, Axel Schwenke wrote:
>>>
>>> I don't think this is real. In fact I have strong reason to believe
>>> that a non-cursor version will be faster (but not significantly).
>>
>> Axel, can you elaborate on why you expect the non-cursor version to be
>> faster, is it due to context switching or some sort of similar effect?
> 
> I think we compare apples and pears here. The two versions below use
> pretty different queries, so any difference in performance is *not*
> a matter of using a cursor or not.
>

See below

> It seems you use the cursor to just read the first FLOOR(Units_to_Add)
> matching rows. But then MySQL already has a syntax feature for that:
> a LIMIT clause. And of course MySQL can use LIMIT to optimize execution
> of a query (i.e. read rows in specified order and stop reading when the
> LIMIT is reached)
> 
> http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html
> 
> 
> A non-cursor version of that query could look like so:
> 
> SELECT MAX(inner.day)
>   FROM ( SELECT reporting_day AS day
>            FROM reporting_days
>           WHERE reporting_day >= Start_Date
>             AND day_type = ''
>        ORDER BY reporting_day
>           LIMIT FLOOR(Units_to_Add)
>   ) AS inner;
> 
> Due to syntax limitations one had to use PREPARE and EXECUTE to put
> the `Units_to_Add` value into that query (aka "dynamic SQL").
>

Can you provide a working example inside a function? I keep getting:

   ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or
trigger

Example:

delimiter //
CREATE FUNCTION  Add_Working_Days3(Units_to_Add Decimal(10, 2),
                                   Start_Date Date)
    RETURNS date
BEGIN
    declare res date;

    set @qry = 'SELECT MAX(inner.day) into res
               FROM ( SELECT reporting_day AS day
                      FROM reporting_days
                      WHERE reporting_day >= Start_Date
                        AND day_type = ''''
                      ORDER BY reporting_day
                      LIMIT FLOOR(' || Units_to_Add || ')
               ) as INNER)';

    PREPARE stmt from @qry;
    EXECUTE stmt;

END//
delimiter ;

I introduced the @qry variable since there seems to be limitations in
what one can do with the query string inside the PREPARE stmt

The reason I did not use LIMIT in my declarative approach was that LIMIT
is not supported other than as a constant, but I'm unable to PREPARE a
query as well.


/Lennart

On 2010-12-28 22:06, Lennart Jonsson wrote:
[...]
> 
> delimiter //
> CREATE FUNCTION  Add_Working_Days3(Units_to_Add Decimal(10, 2),
>                                    Start_Date Date)
>     RETURNS date
> BEGIN
>     declare res date;
> 
>     set @qry = 'SELECT MAX(inner.day) into res
>                FROM ( SELECT reporting_day AS day
>                       FROM reporting_days
>                       WHERE reporting_day >= Start_Date
>                         AND day_type = ''''
>                       ORDER BY reporting_day
>                       LIMIT FLOOR(' || Units_to_Add || ')
>                ) as INNER)';
> 
>     PREPARE stmt from @qry;
>     EXECUTE stmt;
> 
> END//
> delimiter ;
> 

not sure how the last ')' ended up in my post, it should be:

    set @qry = 'SELECT MAX(inner.day) into res
               FROM ( SELECT reporting_day AS day
                      FROM reporting_days
                      WHERE reporting_day >= Start_Date
                        AND day_type = ''''
                      ORDER BY reporting_day
                      LIMIT FLOOR(' || Units_to_Add || ')
               ) as INNER';

Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:
> On 2010-12-28 19:32, Axel Schwenke wrote:

>> A non-cursor version of that query could look like so:
>>
>> SELECT MAX(inner.day)
>>   FROM ( SELECT reporting_day AS day
>>            FROM reporting_days
>>           WHERE reporting_day >= Start_Date
>>             AND day_type = ''
>>        ORDER BY reporting_day
>>           LIMIT FLOOR(Units_to_Add)
>>   ) AS inner;
>>
>> Due to syntax limitations one had to use PREPARE and EXECUTE to put
>> the `Units_to_Add` value into that query (aka "dynamic SQL").
>
> Can you provide a working example inside a function?

No. Dynamic SQL is not allowed in a stored function. But it can be used
in a stored procedure. And of course it had to be written along

$qry = "... LIMIT " || FLOOR(Units_to_Add) || ...


XL