bug (5.6.22) in ADDTIME?
a simple 3-table structure, one of then is a MxM relation between the other two:
CREATE TABLE `asig_cita2` (
`codasig_cita` int(11) unsigned NOT NULL AUTO_INCREMENT,
`hora_cita` time NOT NULL DEFAULT '00:00:00',
PRIMARY KEY (`codasig_cita`)
) ENGINE=InnoDB AUTO_INCREMENT=745408 DEFAULT CHARSET=latin1;
insert into `asig_cita2`(`codasig_cita`,`hora_cita`) values (741550,'07:00:00'),(742135,'07:30:00'),(742413,'08:00:00'),(742460,'07:45:00');
CREATE TABLE `citas_proc_empl2` (
`codasig_cita` int(10) unsigned NOT NULL DEFAULT '0',
`codproc_empl` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`codasig_cita`,`codproc_empl`),
KEY `c2_ibfk_2` (`codproc_empl`),
CONSTRAINT `c2_ibfk_1` FOREIGN KEY (`codasig_cita`) REFERENCES `asig_cita2` (`codasig_cita`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `c2_ibfk_2` FOREIGN KEY (`codproc_empl`) REFERENCES `proc_empl2` (`codproc_empl`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into `citas_proc_empl2`(`codasig_cita`,`codproc_empl`) values (742135,251),(742413,251),(742460,251),(741550,6734);
CREATE TABLE `proc_empl2` (
`codproc_empl` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
`duracion` time NOT NULL DEFAULT '00:00:00',
PRIMARY KEY (`codproc_empl`)
) ENGINE=InnoDB AUTO_INCREMENT=7608 DEFAULT CHARSET=latin1;
insert into `proc_empl2`(`codproc_empl`,`duracion`) values (251,'00:15:00'),(253,'00:15:00'),(6684,'00:00:00'),(6689,'00:00:00'),(6690,'00:00:00'),(6694,'00:00:00'),(6695,'00:00:00'),(6696,'00:00:00'),(6721,'00:00:00'),(6734,'00:00:00');
--------------
the problem is this query: (works fine in 5.0 and wrong in en 5.1 and 5.6.22)
SELECT a.codasig_cita, a.hora_cita,
pe.duracion,
SEC_TO_TIME(SUM(TIME_TO_SEC(pe.duracion))) AS duracion2,
ADDTIME(a.hora_cita, pe.duracion) AS "add (correct)",
ADDTIME(a.hora_cita, SEC_TO_TIME(SUM(TIME_TO_SEC(pe.duracion))) ) AS "add (wrong)"
FROM asig_cita2 a, citas_proc_empl2 cpe, proc_empl2 pe
WHERE cpe.codproc_empl=pe.codproc_empl AND a.codasig_cita=cpe.codasig_cita
GROUP BY a.codasig_cita;
-------
the last column result is wrong, it should be like the previous one.
funny thing is, problem goes away if I wrapped the "a.hora_cita" in a TIME() function
OR
if I make "ORDER BY hora_cita"
but of course, you shouldn't be force to do that