MySQL Forums
Forum List  »  Stored Procedures

Re: Unpredictable results from stored procedure
Posted by: Grzegorz Laszczak
Date: July 23, 2013 01:57AM

Peter Brawley Wrote:
-------------------------------------------------------
Thanks for your answer

> For obvious security reaasons I don't download
> from unknown sites. If you want readers to debug
> your code & data for you, post here enough Create
> Table and Insert statements here to allow them to
> do so.

Below you find my tables, some data and functions to repeat my problem
Quote


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

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 ;


CREATE TABLE IF NOT EXISTS `tar_work2` (
`idx_work2` bigint(20) unsigned NOT NULL auto_increment,
`linie` varchar(50) NOT NULL default '',
`prefix` varchar(150) NOT NULL default '',
`waga` bigint(10) unsigned NOT NULL default '0',
`xdzien` varchar(18) NOT NULL default '',
`xgodzina` varchar(75) NOT NULL default '',
`start` decimal(24,8) NOT NULL default '0.00000000',
`wolnysek` decimal(24,8) NOT NULL default '0.00000000',
`oplata` decimal(24,8) NOT NULL default '0.00000000',
`zasek` decimal(24,8) NOT NULL default '0.00000000',
`zaokr` int(3) NOT NULL default '0',
`waznaod` date NOT NULL default '0000-00-00',
`waznado` date NOT NULL default '0000-00-00',
`mincyfr` bigint(12) NOT NULL default '0',
`maxcyfr` bigint(12) NOT NULL default '0',
PRIMARY KEY (`idx_work2`),
KEY `idxlinie` (`linie`(10)),
KEY `idxprefix` (`waga`,`prefix`(10))
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=latin2;

/*!40000 ALTER TABLE `tar_work2` DISABLE KEYS */;
INSERT IGNORE INTO `tar_work2` (`idx_work2`, `linie`, `prefix`, `waga`, `xdzien`, `xgodzina`, `start`, `wolnysek`, `oplata`, `zasek`, `zaokr`, `waznaod`, `waznado`, `mincyfr`, `maxcyfr`) VALUES
(7, '%ZGPSTN%', '012%', 3, '.', '(0[0-7]|2[1-3])', 0.00000000, 0.00000000, 0.29000000, 360.00000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(8, '%ZGPSTN%', '012%', 3, '.', '(0[89]|1.|20)', 0.00000000, 0.00000000, 0.29000000, 180.00000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(73, '%ZGPSTN%', '051%', 3, '[067]', '.*', 0.00000000, 0.00000000, 0.29000000, 26.38000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(74, '%ZGPSTN%', '051%', 3, '[1-5]', '(0[89]|1[0-8])', 0.00000000, 0.00000000, 0.29000000, 25.22000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(75, '%ZGPSTN%', '051%', 3, '[1-5]', '(0[0-7]|19|2[0-3])', 0.00000000, 0.00000000, 0.29000000, 26.38000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(97, '%ZGPSTN%', '060%', 3, '[067]', '.*', 0.00000000, 0.00000000, 0.29000000, 26.38000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(98, '%ZGPSTN%', '060%', 3, '[1-5]', '(0[89]|1[0-8])', 0.00000000, 0.00000000, 0.29000000, 25.22000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(99, '%ZGPSTN%', '060%', 3, '[1-5]', '(0[0-7]|19|2[0-3])', 0.00000000, 0.00000000, 0.29000000, 26.38000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(112, '%ZGPSTN%', '066%', 3, '[067]', '.*', 0.00000000, 0.00000000, 0.29000000, 26.38000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(113, '%ZGPSTN%', '066%', 3, '[1-5]', '(0[89]|1[0-8])', 0.00000000, 0.00000000, 0.29000000, 25.22000000, 2, '2013-01-01', '2099-12-31', 10, 101),
(114, '%ZGPSTN%', '066%', 3, '[1-5]', '(0[0-7]|19|2[0-3])', 0.00000000, 0.00000000, 0.29000000, 26.38000000, 2, '2013-01-01', '2099-12-31', 10, 101);
/*!40000 ALTER TABLE `tar_work2` ENABLE KEYS */;


CREATE TABLE IF NOT EXISTS `tmp1` (
`DATA` date default NULL,
`CZAS` time default NULL,
`ACC_CODE` varchar(20) NOT NULL default '',
`nr_kat_a` varchar(80) NOT NULL default '',
`inf_wybier` text NOT NULL,
`cz_trwania` decimal(10,2) NOT NULL default '0.00',
`ZALICZONE` int(1) NOT NULL default '0',
`LINIA` text NOT NULL,
`tar_asterisk_id` bigint(20) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

/*!40000 ALTER TABLE `tmp1` DISABLE KEYS */;
INSERT IGNORE INTO `tmp1` (`DATA`, `CZAS`, `ACC_CODE`, `nr_kat_a`, `inf_wybier`, `cz_trwania`, `ZALICZONE`, `LINIA`, `tar_asterisk_id`) VALUES
('2013-06-04', '06:53:10', '', '801', '0129999999', 282.00, 1, 'SIP/ZGPSTN', 296),
('2013-06-04', '06:58:19', '', '801', '0129999999', 35.00, 1, 'SIP/ZGPSTN', 297),
('2013-06-04', '07:16:13', '', '801', '0129999999', 226.00, 1, 'SIP/ZGPSTN', 300),
('2013-06-04', '07:48:53', '', '801', '0129999999', 97.00, 1, 'SIP/ZGPSTN', 306),
('2013-06-04', '08:53:12', '', '801', '0519999999', 9.00, 1, 'SIP/ZGPSTN', 321),
('2013-06-04', '08:53:57', '', '801', '0519999999', 59.00, 1, 'SIP/ZGPSTN', 324),
('2013-06-04', '09:33:51', '', '801', '0129999999', 114.00, 1, 'SIP/ZGPSTN', 332),
('2013-06-04', '10:01:52', '', '801', '0129999999', 57.00, 1, 'SIP/ZGPSTN', 333),
('2013-06-04', '10:07:09', '', '801', '0129999999', 61.00, 1, 'SIP/ZGPSTN', 339),
('2013-06-04', '10:08:57', '', '801', '0129999999', 30.00, 1, 'SIP/ZGPSTN', 340),
('2013-06-04', '10:37:41', '', '801', '0129999999', 62.00, 1, 'SIP/ZGPSTN', 355),
('2013-06-04', '11:08:20', '', '801', '0609999999', 200.00, 1, 'SIP/ZGPSTN', 369),
('2013-06-04', '12:40:39', '', '801', '0129999999', 76.00, 1, 'SIP/ZGPSTN', 410),
('2013-06-04', '12:49:59', '', '801', '0129999999', 84.00, 1, 'SIP/ZGPSTN', 417),
('2013-06-04', '13:46:13', '', '801', '0669999999', 73.00, 1, 'SIP/ZGPSTN', 463),
('2013-06-04', '14:02:46', '', '801', '0129999999', 79.00, 1, 'SIP/ZGPSTN', 475),
('2013-06-04', '15:07:53', '', '801', '0129999999', 16.00, 1, 'SIP/ZGPSTN', 502);
/*!40000 ALTER TABLE `tmp1` ENABLE KEYS */;


DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `_debug`(IN `inmessage` TEXT)
MODIFIES SQL DATA
COMMENT 'Zapisuje text do tablicy _debug_table'
BEGIN
insert into tar_asterisk._debug_table (message)
value (inmessage);
END//
DELIMITER ;


CREATE TABLE IF NOT EXISTS `_debug_table` (
`idx` bigint(20) unsigned NOT NULL auto_increment,
`tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`message` text NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='Tablica debugowa';

/*!40000 ALTER TABLE `_debug_table` DISABLE KEYS */;
/*!40000 ALTER TABLE `_debug_table` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

After issuing my select, please observe rasult set and data inside _debug_table.
Quote

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 ;

The SELECT inside function is ok. I've confirmed this with below JOIN (all records in tmp1 with records from tar_work2 and expresion which evaluates like my function)

Quote

select `start`, `wolnysek`, `oplata`, `zasek`, `zaokr`, idx_work2, tar_asterisk_id,
round(start+ if(cz_trwania<=wolnysek,0.0,oplata*ceiling((cz_trwania - wolnysek)/zasek)),zaokr) as RESULT,
cena_bylinia(`data`,czas,inf_wybier,cz_trwania,linia,tar_asterisk_id) as x1
from tmp1 left join `tar_work2`
on
(LINIA like `linie`)
and (inf_wybier like `prefix`)
and (weekday(data) regexp `xdzien`)
and (left(czas,2) regexp `xgodzina`)
and (data between `waznaod` and `waznado`)
and (length(inf_wybier) between `mincyfr` and `maxcyfr`) ;

> Your query's use of LIKE and REGEXP operators is
> incoherent. As written, the '=' operator would be
> better.

It's not possible in my project to replace LIKE and REGEXP with =

If you can - please confirm if this bug persists in server version other than 5.0.xx.

And, as I wrote before: maybe some workaround?

thanks
g.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Unpredictable results from stored procedure
1731
July 23, 2013 01:57AM


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.