> from unknown sites. If you want readers to debug
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.
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)
> incoherent. As written, the '=' operator would be
If you can - please confirm if this bug persists in server version other than 5.0.xx.
g.