Unpredictable results from stored procedure
Hi all!
Mysql version: 5.0.70, 5.0.95
I have defined stored function:
DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `cena_bylinia`(`indata` DATE, `inczas` TIME, `innumer` TEXT character set latin2, `incz_trwania` DECIMAL(10,2), `inLINIA` TEXT character set latin2, `tmpIdx` BIGINT(20) unsigned) RETURNS decimal(24,8)
READS SQL DATA
BEGIN
declare lstart decimal(10,4) default null;
declare lwolnysek decimal(24,8) default null;
declare loplata decimal(24,8) default null;
declare lzasek decimal(24,8) default null;
declare lzaokr decimal(24,8) default null;
declare llweekday text character set latin2 default convert(weekday(indata),char);
declare llhour text character set latin2 default left(convert(inczas,char),2);
declare lllength integer unsigned default char_length(innumer);
declare continue handler for not found set lstart=null ;
call _debug(concat_WS('#',tmpIdx,indata,inczas,innumer,incz_trwania,inlinia,llweekday,llhour,lllength));
select `start`, `wolnysek`, `oplata`, `zasek`, `zaokr`
into lstart, lwolnysek, loplata, lzasek, lzaokr
from `tar_work2`
where
(inLINIA like `linie`)
and (innumer like `prefix`)
and (llweekday regexp `xdzien`)
and (llhour regexp `xgodzina`)
and (indata between `waznaod` and `waznado`)
and (lllength between `mincyfr` and `maxcyfr`)
order by `waga` desc, `prefix`
limit 1
;
call _debug(concat_WS('|',tmpIdx,lstart,lwolnysek,loplata,lzasek,lzaokr));
return round(lstart+ if(incz_trwania<=lwolnysek,0.0,loplata*ceiling((incz_trwania - lwolnysek)/lzasek)),lzaokr);
END//
DELIMITER ;
And I've use this function 3 times for each row in select:
mysql> select cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x1,
-> cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x2,
-> cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x3,
-> tmp1.tar_asterisk_id from tmp1
-> order by tar_asterisk_id asc
-> ;
+------------+------------+------------+-----------------+
| x1 | x2 | x3 | tar_asterisk_id |
+------------+------------+------------+-----------------+
| 0.29000000 | 0.29000000 | 0.29000000 | 296 |
| 0.29000000 | 0.29000000 | 0.29000000 | 297 |
| 0.29000000 | 0.29000000 | 0.29000000 | 300 |
| 0.29000000 | 0.29000000 | 0.29000000 | 306 |
| 0.29000000 | NULL | NULL | 321 |
| NULL | NULL | NULL | 324 |
| 0.29000000 | NULL | NULL | 332 |
| NULL | NULL | NULL | 333 |
| NULL | NULL | NULL | 339 |
| NULL | NULL | NULL | 340 |
| NULL | NULL | NULL | 355 |
| 2.32000000 | NULL | NULL | 369 |
| 0.29000000 | NULL | NULL | 410 |
| NULL | NULL | NULL | 417 |
| 0.87000000 | NULL | NULL | 463 |
| 0.29000000 | NULL | NULL | 475 |
| NULL | NULL | NULL | 502 |
+------------+------------+------------+-----------------+
17 rows in set (0.01 sec)
In each row results returned from function should be the same... The tables have data that all function calls in above selection should return non null results.
I try to redefine this function as procedure, generally with the same effect
I need some workaround, can anybody help me?
thanks
g.
[edit]
The _debug function is not necessary of course, It's for debugging porpouses only...
Edited 1 time(s). Last edit at 07/16/2013 04:11AM by Grzegorz Laszczak.