MySQL Bugs: #10705: Formating 'datetime' field return a wrong field type

Bug #10705	Formating 'datetime' field return a wrong field type
Submitted:	18 May 2005 10:05	Modified:	12 Sep 2005 19:11
Reporter:	Paolo Saudin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.13 & 5.0.10	OS:	Windows (windows)
Assigned to:	Valeriy Kravchuk	CPU Architecture:	Any

Description:
When I retrieve data from a table with a datetime column "SELECT
master.Fulldate ..." (Fulldate is the datetime column), I normally get a
recordset whose first column is of adDBTimeStamp type (ado 135). As soon as I
need aggregate functions as "SELECT SUBSTRING(master.Fulldate,1,13) as Fulldate
... GROUP BY Fulldate" to get hourly averages, the recordset field type change
to adBinary (128) and is no longer treated as a valid date - the
rs.field(0).value is ??????. Tried all the latest MyODBC versions without success

Same as Bug #4437.

Regards,
Paolo

How to repeat:
CREATE TABLE IF NOT EXISTS `_master` (
  `Fulldate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Fulldate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SELECT SUBSTRING(master.Fulldate,1,13) as Fulldate
... GROUP BY Fulldate

if you use "SELECT SUBSTRING(master.Fulldate,1,13) as Fulldate
... GROUP BY Fulldate", function substring() converts "master.Fulldate" to string type. It's right. What type do you expect ?

I expect type of date or datetime. I tried using the CAST function [ SELECT (CAST(SUBSTRING(master.Fulldate,1,13) AS DATE) as Fulldate ] but the result doesn't change, I still get string type. I need to pass a recordset to a charting component, is there a way to get either a date or datetime field type? Thanks!

I tried to repeat it on my test cases (VB and C#). My test cases returned type "date":

Data: 2005-05-23
Type: date

I attached test cases to "Files".

Test cases

Attachment: 10705.zip (application/x-zip-compressed, text), 6.92 KiB.

All is ok and the field type is correct until I use a group by clause. 
I attached test cases too using VB6.0 SP5, ADO 2.8 and the latest MyODBC
With the query Select CAST(SUBSTRING(master.Fulldate,1,10) AS Datetime) as Fulldate from master group by Fulldate the field type is no longer correct
Regards.

myodbc field types

Attachment: Test_mysql_type.zip (application/x-zip-compressed, text), 2.21 KiB.

I was not able to reproduce the bug. I used your test case from bug #10705. I attached screen copy of my results.

screen copy

Attachment: screencopy.GIF (image/gif, text), 65.87 KiB.

for version 5.0.10

query A:
select cast(substring(FullDate, 1, 10) as datetime) as fulldate from _master group by fulldate
issues warning about ambigous column reference. return result: datetime - OK.

query B:
select cast(substring(FullDate, 1, 10) as datetime) as fulldate1 from _master group by fulldate1
no warning, returns datetime - OK

query C:
select cast(substring(FullDate, 1, 10) as datetime) as fulldate1 from _master group by fulldate
no warning, returns datetime - OK

if in queries A, B, C group by clause is changed to order by, then no warnings, returns date time - OK

4.1.13 version
query A: no warning, returns string - bad
query B: no warning, returns datetime - OK
query C: no warning, returns datetime - OK

if in queries A, B, C group by clause is changed to order by, then no warnings, returns date time - OK

Have a look at the bug #11965 and try the test program in VB6.0. I updated to the latest (5.0.10) version but I still get adVarChar instead of adDBTimeStamp.

Thanks,
Paolo

Let me check this with simple C and on Linux.

Have you tried to repeat this behaviour with newer 4.1.14 and 5.0.12-beta versions?

I upgraded to the 5.0.12 version and now the fields type are all ok! I attached the picture showing the result.
Many Thanks!
Paolo

Fileds type are now correct

Attachment: Test-5.0.12.GIF (image/gif, text), 19.20 KiB.

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:

Thank you for the feedback and bug report.