I am puzzled by a certain SQL statement I make on a data set I have.
The first SQL statement delivers the correct result, like this (with and
without SEC_TO_TIME):
mysql> select distinct
-> xVCD.VCDID,SEC_TO_TIME(sum(TIME_TO_SEC(sonoxexternal.Song.Length))) as Length
-> from xVCD,sonoxexternal.Song where xVCD.SongID=sonoxexternal.Song.SongID AND
-> xVCD.VCDID="VC39fef2bb7aa90"
-> group by xVCD.VCDID,xVCD.OrderID;
+-----------------+----------+
| VCDID | Length |
+-----------------+----------+
| VC39fef2bb7aa90 | 01:06:39 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> select distinct
-> xVCD.VCDID,sum(TIME_TO_SEC(sonoxexternal.Song.Length)) as Length
-> from xVCD,sonoxexternal.Song where xVCD.SongID=sonoxexternal.Song.SongID AND
-> xVCD.VCDID="VC39fef2bb7aa90"
-> group by xVCD.VCDID,xVCD.OrderID;
+-----------------+--------+
| VCDID | Length |
+-----------------+--------+
| VC39fef2bb7aa90 | 3999 |
+-----------------+--------+
1 row in set (0.00 sec)
----------------
However, on a different VCDID, I get this:
mysql> select distinct
-> xVCD.VCDID,sum(TIME_TO_SEC(sonoxexternal.Song.Length)) as Length
-> from xVCD,sonoxexternal.Song where xVCD.SongID=sonoxexternal.Song.SongID AND
-> xVCD.VCDID="VC3994263c44daf"
-> group by xVCD.VCDID,xVCD.OrderID;
+-----------------+--------+
| VCDID | Length |
+-----------------+--------+
| VC3994263c44daf | 375 |
| VC3994263c44daf | 1262 |
| VC3994263c44daf | 1799 |
+-----------------+--------+
3 rows in set (0.00 sec)
mysql> select distinct
-> xVCD.VCDID,SEC_TO_TIME(sum(TIME_TO_SEC(sonoxexternal.Song.Length))) as Length
-> from xVCD,sonoxexternal.Song where xVCD.SongID=sonoxexternal.Song.SongID AND
-> xVCD.VCDID="VC3994263c44daf"
-> group by xVCD.VCDID,xVCD.OrderID;
+-----------------+----------+
| VCDID | Length |
+-----------------+----------+
| VC3994263c44daf | 00:06:15 |
+-----------------+----------+
1 row in set (0.00 sec)
Which is totally wrong.
This happens on two out of some thousand entries in my queries... Is this
related to the TIME_TO_SEC bug that was in 3.23.22?
This is on version 3.23.28. (I will upgrade to 3.23.32 asap, but will
that fix this bug?)
--
patrik wallstrom | f o o d f i g h t
tel: +46-8-6188428 | s t o c k h o l m
gsm: +46-708405080 | - - - - - - - - -
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
