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: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 3.51.08 | OS: | Windows (Windows) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[6 Jul 2004 12:16]
Gyuri van de Bilt
[9 Jul 2004 12:05]
Gyuri van de Bilt
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
[13 Jul 2004 12:35]
Gyuri van de Bilt
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
[13 Jul 2004 12:36]
Gyuri van de Bilt
Changed bug-description.
[26 Jul 2004 20:58]
Dean Ellis
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.
[15 Apr 2005 15:06]
Jo Andries
I installed a newer ODBC driver and problem was gone 3.5.11