MySQL Bugs: #4418: Queries on DATE and TIME fields, return (unexpected) empty values!

Bug #4418	Queries on DATE and TIME fields, return (unexpected) empty values!
Submitted:	6 Jul 2004 12:16	Modified:	26 Jul 2004 20:58
Reporter:	Gyuri van de Bilt	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.08	OS:	Microsoft Windows (Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Running MySQL 4.0.20, ODBC Driver 3.51.08 on Windows 2000, SP4
Error occured after changing from ODBC driver 3.51.06 to .08, so obviously a myODVC problem.

When running a query like this one:

SELECT DATE_FORMAT(DateStamp, '%Y%m') AS inmonth FROM tblRefLog GROUP BY DATE_FORMAT(DateStamp, '%Y%m') LIMIT 1

The following returns '???' instead of e.g. '200102' (also without the LIMIT)

[ASP]: Response.write varRS("inmonth")

Wrong output occurs on every table I use (tried different tables).

How to repeat:
See description.

Suggested fix:
N/A

Exact MySQL version: 4.20a

More problems seem to exist with DATE values in combination to GROUP BY (also without a DATE(function) in the GROUP BY part). I ran into more queries that now give invalid output for datevalues e.g.:

SELECT DATE_ADD(ShipmentDate, INTERVAL ShipPayDays DAY) AS datedue FROM tblShipment WHERE ShipStatus <= 900 GROUP BY ShipmentID LIMIT 1

Gives an error reading the 'datevar' value. 
When leaving out the 'GROUP BY ShipmentID' it returns a correct value.
Strangely it also returns a correct value when leaving out the WHERE clause (and thus leaving the group by intact)...

Table info:

CREATE TABLE `tblshipment` 
(
	`ShipmentID` INTEGER (11) NOT NULL  AUTO_INCREMENT , 
	`ShipOrderID` INTEGER (11), 
	`ShipmentDate` datetime, 
	`ShipAmount` INTEGER (11), 
	`ShipPrice` DOUBLE (22,2), 
	`ShippingCost` DOUBLE (22,2), 
	`ShipTotPrice` DOUBLE (22,2), 
	`ShipStatus` SMALLINT (5) UNSIGNED , 
	`ShipTimestamp` TIMESTAMP NOT NULL , 
	`ShipPaid` DOUBLE (22,2) DEFAULT 0.00, 
	`ShipAddCost` DOUBLE (22,2) DEFAULT 0.00, 
	`ShipPayDays` mediumint (9) DEFAULT 0, 
	`ShipPayDate` DATE, 
	`ShipUncollect` DOUBLE (22,2) DEFAULT 0.00, 
	`ShipCollecting` tinyint (4) DEFAULT 0, 
	`ShipCollectDate` DATE, 
	`ShipRepVATPrice` DOUBLE (22,2) DEFAULT 0.00, 
	`ShipRepVATUncoll` DOUBLE (22,2) DEFAULT 0.00, 
	`ShipRemindCost` DOUBLE (22,2) DEFAULT 0.00, 
	`ShipCountryReg` INTEGER (11), 
	`ShipCountry` INTEGER (11), 
	`ShipAutoRemDay` INTEGER (11) DEFAULT 0, 
	`ShipAutoRemTimes` INTEGER (11) DEFAULT 0, 
	`ShipIncassoTimes` tinyint (4) NOT NULL  DEFAULT 0, 
	`ShipBarcode` varchar (50),
	PRIMARY KEY (ShipmentID)
) TYPE=MyISAM

There seem to be big problems regarding DATE/TIME values (which were not present in the previous version).

Running this query: SELECT * FROM fanagenda WHERE clubid = 1 AND actid = 1

On this table, used to return timevalues for actstart and actend. now they return empty values!

CREATE TABLE `fanagenda` 
(
	`actid` INTEGER (11) NOT NULL  AUTO_INCREMENT , 
	`actdate` DATE, 
	`actstart` TIME, 
	`actend` TIME, 
	`actplace` varchar (50), 
	`actcity` varchar (50), 
	`actcountry` INTEGER (11) NOT NULL  DEFAULT 0, 
	`actdesc` varchar (255), 
	`acthide` tinyint (1) NOT NULL  DEFAULT 0, 
	`clubid` INTEGER (11) NOT NULL  DEFAULT 0,
	PRIMARY KEY (actid)
) TYPE=MyISAM

Changed bug-description.

This should be the same issue as bug 4578, resulting in the columns reporting a binary column type.  When that bug has been corrected, if you still have this problem please update us.

I installed a newer ODBC driver and problem was gone 3.5.11