MySQL Bugs: #35805: When querying dates not comparing them correctly

Bug #35805	When querying dates not comparing them correctly
Submitted:	3 Apr 2008 15:01	Modified:	4 May 2008 19:02
Reporter:	Vitaly Babiy	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.45-Debian_1ubuntu3.3-log Debian etch	OS:	Linux (Ubuntu 7.10)
Assigned to:		CPU Architecture:	Any
Tags:	date, query

Description:
When i am querying a table to find everything but a date I get no results

How to repeat:
   1.
      mysql> select dateEntered from qorder ORDER BY dateEntered DESC LIMIT 10;
   2.
      +---------------------+
   3.
      | dateEntered         |
   4.
      +---------------------+
   5.
      | 2008-04-01 17:34:12 |
   6.
      | 2008-04-01 17:10:08 |
   7.
      | 2008-04-01 16:13:43 |
   8.
      | 2008-04-01 15:54:14 |
   9.
      | 2008-04-01 15:27:44 |
  10.
      | 2008-04-01 15:21:55 |
  11.
      | 2008-04-01 15:04:54 |
  12.
      | 2008-04-01 15:00:56 |
  13.
      | 2008-04-01 15:00:27 |
  14.
      | 2008-04-01 14:56:26 |
  15.
      +---------------------+
  16.
      10 rows in set (0.00 sec)
  17.
       
  18.
      mysql> select * from qorder where DATE(dateEntered) = DATE('2008-04-01');
  19.
      Empty set (0.00 sec)

Suggested fix:
it works fine in 5.0.21-Debian_3ubuntu1-log

Thank you for a problem report. Please, send the results of:

show create table like 'qorder'\G
EXPLAIN select * from qorder where DATE(dateEntered) = DATE('2008-04-01')\G

mysql> show create table qorder\G
*************************** 1. row ***************************
       Table: qorder
Create Table: CREATE TABLE `qorder` (
  `id` varchar(255) NOT NULL default '',
  `dateEntered` datetime default NULL,
  `dateOpened` datetime default NULL,
  `datePaymentOpened` datetime default NULL,
  `requestedShippingDate` datetime default NULL,
  `requestedDeliveryDate` datetime default NULL,
  `fob` varchar(255) default NULL,
  `paymentNumber` varchar(255) default NULL,
  `remarks` text,
  `deliveryInstructions` text,
  `status` varchar(255) default NULL,
  `billingInstructions` text,
  `packingInstructions` text,
  `shippingInstructions` text,
  `unitOfMeasure` varchar(255) default NULL,
  `officeInstructions` text,
  `creditRemarks` varchar(255) default NULL,
  `approved` tinyint(1) default NULL,
  `creditApproved` tinyint(1) default NULL,
  `factored` tinyint(1) default NULL,
  `dutyPrepaid` tinyint(1) default NULL,
  `miscCharge` decimal(19,4) default NULL,
  `productCharge` decimal(19,4) default NULL,
  `taxCharge` decimal(19,2) default NULL,
  `shippingCharge` decimal(19,2) default NULL,
  `enteredBy_id` varchar(255) default NULL,
  `issuedBy_id` varchar(255) default NULL,
  `orderTerm_id` varchar(255) default NULL,
  `sellingParty_id` varchar(255) default NULL,
  `orderType_id` varchar(255) default NULL,
  `shipToAddress_id` varchar(255) default NULL,
  `carrier_id` varchar(255) default NULL,
  `creditApprovedBy_id` varchar(255) default NULL,
  `shipFromAddress_id` varchar(255) default NULL,
  `approvedBy_id` varchar(255) default NULL,
  `paymentType_id` varchar(255) default NULL,
  `confirmed` tinyint(1) default NULL,
  `priceHold` tinyint(1) default NULL,
  `freightPaymentMethod_id` varchar(255) default NULL,
  `shippingThirdPartyBillTo` varchar(255) default NULL,
  `miscChargeDescription` varchar(255) default NULL,
  `commissionInstructions` text,
  `requestedShippingDateConfirmed` tinyint(1) default NULL,
  `localOrderNumber` varchar(255) default NULL,
  `foreignOrderNumber` varchar(255) default NULL,
  `foreignParty_id` varchar(255) default NULL,
  `localParty_id` varchar(255) default NULL,
  `labelingInstructions` text,
  `dateOfLastChange` datetime default NULL,
  `dateOfCreation` datetime default NULL,
  `newPoRequired` bit(1) default NULL,
  `newPoAlertSent` bit(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FKC725257D5D5FBA71` (`shipFromAddress_id`),
  KEY `FKC725257D1720DC85` (`creditApprovedBy_id`),
  KEY `FKC725257DDE19E232` (`paymentType_id`),
  KEY `FKC725257D18971CE2` (`orderType_id`),
  KEY `FKC725257DEFA4776A` (`issuedBy_id`),
  KEY `FKC725257DAD2D14F4` (`carrier_id`),
  KEY `FKC725257DE1C92ADE` (`approvedBy_id`),
  KEY `FKC725257D486B70FE` (`orderTerm_id`),
  KEY `FKC725257D821A0C1E` (`enteredBy_id`),
  KEY `FKC725257D64813F02` (`shipToAddress_id`),
  KEY `FKC725257DCD2C8996` (`sellingParty_id`),
  KEY `FKC725257DA5605B67` (`freightPaymentMethod_id`),
  KEY `FKC725257DDC6C9F30` (`freightPaymentMethod_id`),
  KEY `FKC725257D6A6F6EB1` (`localParty_id`),
  KEY `FKC725257DD0C5971A` (`foreignParty_id`),
  KEY `qorder_localOrderNumber` (`localOrderNumber`),
  KEY `qorder_foreignOrderNumber` (`foreignOrderNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> EXPLAIN select * from qorder where DATE(dateEntered) = DATE('2008-04-01')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: qorder
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 131566
        Extra: Using where
1 row in set (0.00 sec)

Thank you for the report.

But version 5.0.45 is a bit old. Additionaly I can not repeat the problem with our binaries. Please upgrade to current version 5.0.51a, try with it and inform us if problem still exists.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".