Bug #504 datetime type returns inconsistant results in select when indexed

Description:
Not sure if the category is right. This is an interesting inconsistancy that occurs when executing the following statements where date_time is of type DATETIME using myisam tables

SELECT * FROM transaction WHERE date_time > 20030527

if date_time has an index then the select statement returns rows where the datetime is greater than midnight 27th May 2003, if it isn't indexed then it returns rows greater than 0 + 20030527 seconds after the zero time, i.e. all the rows.
Further if the table is joined then the results are inconsistant.  I made an example database called datebug with three tables to demonstrate the inconsistancies. I have pasted this into the how to repeat section.  

My work around has been to just use full length dates i.e.

SELECT * FROM transaction WHERE date_time > 20030527000000

Hope this helps.  Keep up the great work.

How to repeat:
-- MySQL dump 9.08
--
-- Host: localhost    Database: datebug
---------------------------------------------------------
-- Server version	4.0.13

--
-- Table structure for table 'discount'
--

CREATE TABLE discount (
  discID int(11) NOT NULL auto_increment,
  transID int(11) NOT NULL default '0',
  reasonID int(11) NOT NULL default '0',
  PRIMARY KEY  (discID)
) TYPE=MyISAM;

--
-- Dumping data for table 'discount'
--

INSERT INTO discount VALUES (1,2,1);
INSERT INTO discount VALUES (2,3,2);

--
-- Table structure for table 'payments'
--

CREATE TABLE payments (
  PaymentID int(11) NOT NULL default '0',
  date_time datetime default NULL,
  PRIMARY KEY  (PaymentID)
) TYPE=MyISAM;

--
-- Dumping data for table 'payments'
--

INSERT INTO payments VALUES (1,'2003-05-26 12:00:00');
INSERT INTO payments VALUES (2,'2003-05-27 15:00:00');

--
-- Table structure for table 'transaction'
--

CREATE TABLE transaction (
  transID int(11) NOT NULL auto_increment,
  date_time datetime default NULL,
  PRIMARY KEY  (transID),
  KEY dateindex (date_time)
) TYPE=MyISAM;

--
-- Dumping data for table 'transaction'
--

INSERT INTO transaction VALUES (1,'2003-05-26 15:30:00');
INSERT INTO transaction VALUES (2,'2003-05-26 15:45:00');
INSERT INTO transaction VALUES (3,'2003-05-27 12:00:00');

-- transaction.date_time has an index, payments.date_time does not
SELECT * FROM transaction WHERE date_time > 20030527;
SELECT * FROM payments WHERE date_time > 20030527
SELECT * FROM transaction, discount where transaction.transID = discount.transID AND date_time > 20030527

Actually, the correct SQL is to use the timestamp format, i.e.:

SELECT * FROM transaction WHERE date_time > '2003-05-27 00:00:00'

But nevertheless, MySQL should not behave inconsistently in this case.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

fixed in 4.0.14