Description:
We have a Stored Procedure that calculates warranty times for repairs. The field "eingangsdatum" represents a date that is fetched from table "eingangsliste". At the beginning of the procedure, the variable eingangsdatum is declared and defaults to '1970-01-01':
DECLARE eingangsdatum DATE DEFAULT '1970-01-01';
[...]
SELECT COALESCE(`eingangsdatum`,CURDATE())
INTO eingangsdatum
FROM `eingangsliste`
WHERE `vorgangsnr` = vorgang;
"vorgang" is the parameter that contains the Primary Key of the table eingangsliste. Until recently, MySQL was able to fetch the correct date and store it into variable eingangsdatum. With 5.5.28 (and properly before), it fails, resulting in keeping the default date for eingangsdatum.
How to repeat:
Test with the following SQL dump and call the routine with:
CALL p_garantie(209896);
SELECT @GARANTIE;
Expected result:
0|2010-10-17|209896
Actual result is:
0|1970-01-01|209896
-- --------------------------------------------------------
-- Host: 127.0.0.1
-- Server version: 5.5.28-log - MySQL Community Server (GPL)
-- Server OS: Win32
-- HeidiSQL version: 7.0.0.4206
-- Date/time: 2012-10-30 16:40:05
-- --------------------------------------------------------
/*!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' */;
-- Dumping database structure for test
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
-- Dumping structure for table test.eingangsliste
DROP TABLE IF EXISTS `eingangsliste`;
CREATE TABLE IF NOT EXISTS `eingangsliste` (
`vorgangsnr` int(10) unsigned NOT NULL AUTO_INCREMENT,
`eingangsdatum` date DEFAULT NULL,
`datum_annahme` varchar(20) DEFAULT NULL,
`warennummer` char(20) DEFAULT NULL,
`benutzerid` int(10) unsigned DEFAULT NULL,
`zubehör` char(250) DEFAULT NULL,
`k_lieferscheinnummer` int(10) unsigned DEFAULT NULL,
`rmanr` int(10) unsigned DEFAULT '0' ,
`vermerke_versand` varchar(1024) DEFAULT NULL ,
`k_bestelldaten` varchar(1024) DEFAULT NULL ,
`buchungsdatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`vorgangsnr`),
KEY `rmanr` (`rmanr`),
KEY `eingangsdatum` (`eingangsdatum`)
) ENGINE=MyISAM AUTO_INCREMENT=211235 DEFAULT CHARSET=utf8;
-- Dumping data for table test.eingangsliste: 2 rows
/*!40000 ALTER TABLE `eingangsliste` DISABLE KEYS */;
INSERT INTO `eingangsliste` (`vorgangsnr`, `eingangsdatum`, `datum_annahme`, `warennummer`, `benutzerid`, `zubehör`, `k_lieferscheinnummer`, `rmanr`, `vermerke_versand`, `k_bestelldaten`, `buchungsdatum`) VALUES
(194031, '2012-07-11', '11.07.2012', '59C', 64717, '', 0, 0, NULL, NULL, '2012-07-10 15:56:58'),
(209896, '2012-10-17', '17.10.2012', '5C', 64717, NULL, 0, 0, NULL, NULL, '2012-10-16 15:04:04');
/*!40000 ALTER TABLE `eingangsliste` ENABLE KEYS */;
-- Dumping structure for procedure test.p_garantie
DROP PROCEDURE IF EXISTS `p_garantie`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_garantie`(IN `vorgang` INT)
COMMENT 'Stored Procedure zur Errechnung der Garantiearten'
BEGIN
DECLARE eingangsdatum DATE DEFAULT '1970-01-01';
DECLARE eigengarantie DATE DEFAULT '1970-01-01';
DECLARE herstellergarantie DATE DEFAULT '1970-01-01';
DECLARE wartungsvertrag DATE DEFAULT '1970-01-01';
DECLARE garantie INT DEFAULT 0;
DECLARE garantieart CHAR(1) DEFAULT '';
DECLARE sernr CHAR(20) DEFAULT '';
DECLARE gerid INT DEFAULT 0;
DECLARE kungruppe CHAR(80) DEFAULT 0;
SELECT COALESCE(`eingangsdatum`,CURDATE())
INTO eingangsdatum
FROM `eingangsliste`
WHERE `vorgangsnr` = vorgang ;
SET @GARANTIE = CONCAT_WS('|',garantie,eingangsdatum,vorgang);
END//
DELIMITER ;
/*!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 */;
Description: We have a Stored Procedure that calculates warranty times for repairs. The field "eingangsdatum" represents a date that is fetched from table "eingangsliste". At the beginning of the procedure, the variable eingangsdatum is declared and defaults to '1970-01-01': DECLARE eingangsdatum DATE DEFAULT '1970-01-01'; [...] SELECT COALESCE(`eingangsdatum`,CURDATE()) INTO eingangsdatum FROM `eingangsliste` WHERE `vorgangsnr` = vorgang; "vorgang" is the parameter that contains the Primary Key of the table eingangsliste. Until recently, MySQL was able to fetch the correct date and store it into variable eingangsdatum. With 5.5.28 (and properly before), it fails, resulting in keeping the default date for eingangsdatum. How to repeat: Test with the following SQL dump and call the routine with: CALL p_garantie(209896); SELECT @GARANTIE; Expected result: 0|2010-10-17|209896 Actual result is: 0|1970-01-01|209896 -- -------------------------------------------------------- -- Host: 127.0.0.1 -- Server version: 5.5.28-log - MySQL Community Server (GPL) -- Server OS: Win32 -- HeidiSQL version: 7.0.0.4206 -- Date/time: 2012-10-30 16:40:05 -- -------------------------------------------------------- /*!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' */; -- Dumping database structure for test CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `test`; -- Dumping structure for table test.eingangsliste DROP TABLE IF EXISTS `eingangsliste`; CREATE TABLE IF NOT EXISTS `eingangsliste` ( `vorgangsnr` int(10) unsigned NOT NULL AUTO_INCREMENT, `eingangsdatum` date DEFAULT NULL, `datum_annahme` varchar(20) DEFAULT NULL, `warennummer` char(20) DEFAULT NULL, `benutzerid` int(10) unsigned DEFAULT NULL, `zubehör` char(250) DEFAULT NULL, `k_lieferscheinnummer` int(10) unsigned DEFAULT NULL, `rmanr` int(10) unsigned DEFAULT '0' , `vermerke_versand` varchar(1024) DEFAULT NULL , `k_bestelldaten` varchar(1024) DEFAULT NULL , `buchungsdatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`vorgangsnr`), KEY `rmanr` (`rmanr`), KEY `eingangsdatum` (`eingangsdatum`) ) ENGINE=MyISAM AUTO_INCREMENT=211235 DEFAULT CHARSET=utf8; -- Dumping data for table test.eingangsliste: 2 rows /*!40000 ALTER TABLE `eingangsliste` DISABLE KEYS */; INSERT INTO `eingangsliste` (`vorgangsnr`, `eingangsdatum`, `datum_annahme`, `warennummer`, `benutzerid`, `zubehör`, `k_lieferscheinnummer`, `rmanr`, `vermerke_versand`, `k_bestelldaten`, `buchungsdatum`) VALUES (194031, '2012-07-11', '11.07.2012', '59C', 64717, '', 0, 0, NULL, NULL, '2012-07-10 15:56:58'), (209896, '2012-10-17', '17.10.2012', '5C', 64717, NULL, 0, 0, NULL, NULL, '2012-10-16 15:04:04'); /*!40000 ALTER TABLE `eingangsliste` ENABLE KEYS */; -- Dumping structure for procedure test.p_garantie DROP PROCEDURE IF EXISTS `p_garantie`; DELIMITER // CREATE DEFINER=`root`@`localhost` PROCEDURE `p_garantie`(IN `vorgang` INT) COMMENT 'Stored Procedure zur Errechnung der Garantiearten' BEGIN DECLARE eingangsdatum DATE DEFAULT '1970-01-01'; DECLARE eigengarantie DATE DEFAULT '1970-01-01'; DECLARE herstellergarantie DATE DEFAULT '1970-01-01'; DECLARE wartungsvertrag DATE DEFAULT '1970-01-01'; DECLARE garantie INT DEFAULT 0; DECLARE garantieart CHAR(1) DEFAULT ''; DECLARE sernr CHAR(20) DEFAULT ''; DECLARE gerid INT DEFAULT 0; DECLARE kungruppe CHAR(80) DEFAULT 0; SELECT COALESCE(`eingangsdatum`,CURDATE()) INTO eingangsdatum FROM `eingangsliste` WHERE `vorgangsnr` = vorgang ; SET @GARANTIE = CONCAT_WS('|',garantie,eingangsdatum,vorgang); END// DELIMITER ; /*!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 */;