MySQL Forums
Forum List  »  General

Re: bug (5.6.22) in ADDTIME?
Posted by: Peter Brawley
Date: January 23, 2015 12:56PM

AddTime() is not the cause of your anomaly. Your query mistake is.

In a Group By query, non-aggregated columns omitted from the Group By clause return arbitrary values when the column does not maintain a strictly 1:1 relationship with Group By columns. When we add in the required Group By columns, your anomaly goes away:

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; 
+--------------+-----------+----------+-----------+-------------+-----------+
| codasig_cita | hora_cita | duracion | duracion2 | add_correct | add_wrong |
+--------------+-----------+----------+-----------+-------------+-----------+
|       741550 | 07:00:00  | 00:00:00 | 00:00:00  | 07:00:00    | 07:30:00  |
|       742135 | 07:30:00  | 00:15:00 | 00:15:00  | 07:45:00    | 08:15:00  |
|       742413 | 08:00:00  | 00:15:00 | 00:15:00  | 08:15:00    | 08:00:00  |
|       742460 | 07:45:00  | 00:15:00 | 00:15:00  | 08:00:00    | 08:00:00  |
+--------------+-----------+----------+-----------+-------------+-----------+

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, a.hora_cita, pe.duracion, add_correct;
+--------------+-----------+----------+-----------+-------------+-----------+
| codasig_cita | hora_cita | duracion | duracion2 | add_correct | add_wrong |
+--------------+-----------+----------+-----------+-------------+-----------+
|       741550 | 07:00:00  | 00:00:00 | 00:00:00  | 07:00:00    | 07:00:00  |
|       742135 | 07:30:00  | 00:15:00 | 00:15:00  | 07:45:00    | 07:45:00  |
|       742413 | 08:00:00  | 00:15:00 | 00:15:00  | 08:15:00    | 08:15:00  |
|       742460 | 07:45:00  | 00:15:00 | 00:15:00  | 08:00:00    | 08:00:00  |
+--------------+-----------+----------+-----------+-------------+-----------+

Options: ReplyQuote


Subject
Written By
Posted
January 23, 2015 10:46AM
Re: bug (5.6.22) in ADDTIME?
January 23, 2015 12:56PM
January 23, 2015 05:48PM
January 26, 2015 12:51PM
January 26, 2015 03:02PM
January 23, 2015 05:55PM
January 26, 2015 03:17PM
January 26, 2015 03:54PM
January 26, 2015 06:35PM


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.