MySQL Forums
Forum List  »  Spanish

bug en ADDTIME?
Posted by: sebastian gomez
Date: January 23, 2015 10:42AM

una estructura simple de 3 tablas, 1 es relación muchos-a-muchos de las otras dos:

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');

--------------

el siguiente query es el problema (en 5.0 funciona bien, en 5.1 y 5.6.22 funciona mal)

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;

-----

lo gracioso es que el problema se arregla si envuelvo en TIME() el "a.hora_cita"
ó también, si hago ORDER BY hora_cita

pero por supuesto, no se tendría que hacer esto

Options: ReplyQuote


Subject
Views
Written By
Posted
bug en ADDTIME?
1889
January 23, 2015 10:42AM


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.