MySQL Bugs: #8879: Wrong Time objects returned after SEC_TO

Bug #8879	Wrong Time objects returned after SEC_TO_TIME
Submitted:	2 Mar 2005 10:13	Modified:	8 Dec 2005 16:49
Reporter:	Gabor Toldi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (XP)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
Trying to get a Time object from a query via ODBC, and the result isnt exact what we want. When running a query in a mysql manager, the result is proper, but the same query cause a wrong (00:00:00, in all cases) object via the ODBC driver.
In version 3.23 the bug doesnt occoured.

How to repeat:
MySQL server ver. 4.023
MySQL ODBC driver ver. 3.51 11
libodbc++ library ver. 0.2.3.
MS VS 6.0, standard C++ project.
MS XP prof.

test table sql:
CREATE TABLE `testtable` (
  `cid` int(11) NOT NULL default '0',
  `extid` int(11) NOT NULL default '0',
  `extension` varchar(255) default NULL,
  `durationsec` int(11) default '0'
) TYPE=MyISAM;

#
# Dumping data for table 'testtable'
#
INSERT INTO `testtable` VALUES("1602780", "14424", "3190", "102");
INSERT INTO `testtable` VALUES("1602781", "14424", "3190", "58");
INSERT INTO `testtable` VALUES("1602782", "16065", "3405", "103");
INSERT INTO `testtable` VALUES("1602783", "14424", "3190", "54");
INSERT INTO `testtable` VALUES("1602784", "15369", "1293", "166");
INSERT INTO `testtable` VALUES("1602785", "15369", "1293", "104");
INSERT INTO `testtable` VALUES("1602786", "15369", "1293", "58");
INSERT INTO `testtable` VALUES("1602787", "15369", "1293", "68");
INSERT INTO `testtable` VALUES("1602788", "15369", "1293", "66");
INSERT INTO `testtable` VALUES("1602789", "13382", "2759", "484");
INSERT INTO `testtable` VALUES("1602790", "15850", "2262", "175");
INSERT INTO `testtable` VALUES("1602791", "15850", "2262", "105");
INSERT INTO `testtable` VALUES("1602792", "12817", "2299", "23");
INSERT INTO `testtable` VALUES("1602793", "13382", "2759", "32");
INSERT INTO `testtable` VALUES("1602794", "11651", "1480", "71");
INSERT INTO `testtable` VALUES("1602795", "13382", "2759", "36");
INSERT INTO `testtable` VALUES("1602796", "13382", "2759", "8");
INSERT INTO `testtable` VALUES("1602797", "13382", "2759", "26");
INSERT INTO `testtable` VALUES("1602798", "13382", "2759", "430");
INSERT INTO `testtable` VALUES("1602799", "12944", "2268", "55");
INSERT INTO `testtable` VALUES("1602800", "12817", "2299", "253");

C++ code:

#include <odbc++/connection.h>
#include <odbc++/resultset.h>
#include <odbc++/statement.h>
#include <odbc++/resultsetmetadata.h>
#include <odbc++/drivermanager.h>

using namespace odbc;

ResultSet* rslt;
Statement* stmt;
Connection* conn;
ResulSetMetaData* rsmd;
string res;
Time tim;
int index = 1;

conn = DriverManager::getConnection("dsn", "user", "pwd");
stmt = conn->createStatement();
string sql = "SELECT count(*), extension, sec_to_time(sum(durationsec)) as d, (sum(durationsec)) as dursec1 FROM testtable group by extid;";

rslt = stmt->executeQuery(sql);

while(rslt->next())
{
  res = rslt->getString(index);
  string colname = rslt->getColumnName(index);
  if (colname == "sumdur") tim = rslt->getTime(index);
  index++;
} 
  

The Result is:

res = '00:00:00'
tim = 0h 0m 0s

In every iteration.

Thanks for your excellent bug report

I'ld like to ask for repairs or any answers for the bug. Is this bug in the latest versions?

Regards, Gabor

I have also experienced this problem using the following syntax:

"SELECT SEC_TO_TIME(TIME_TO_SEC(Call_Detail_Records.EndTime) - TIME_TO_SEC(Call_Detail_Records.StartTime)) as Duration FROM Call_Detail_Records" - the result is the current date (i.e. 7/8/2005)

MySQL ODBC v3.51.11.00
MySQL Server v4.1.12a-nt

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

This bug has been fixed in the Connector/ODBC v.3.51.12

The same bug is in MySQL Connector/J 5.1. I didn't have problems when I was using MySQL Connector/J 3.1, but after upgrade to 5.1 I can not use function SEC_TO_TIME() function in SQL queries.

Hello Anna,

Do you have a test case for Connector/J? Can you please attach it to this bug record? I will re-verify this case against your code and re-open the bug along with changing its category from Connector/ODBC to Connector/J.
Thanks.