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
