Bug #7725 Indexed DATETIME Columns Return Random Results

• Submitted: 7 Jan 2005 4:00
• Modified: 9 Jan 2005 9:27
• Reporter: Hans Zaunere
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.8
• OS: Linux (Red Hat ES 3.0)
• Assigned to: Pekka Nousiainen
• CPU Architecture: Any

[7 Jan 2005 4:00] Hans Zaunere

Description:

This may be related to #7424.

The NDB nodes are running on Red Hat ES on Dual Xeon EMT_64 servers with 4gb of RAM each.

Our tables have DATETIME columns, which are frequently searched.  When no index is defined for a DATETIME column, MAX(), MIN(), ORDER BY, <, and > operators behave correctly.

When an index is defined, the above operations return incorrect results.  MAX(), MIN(), <, and > returns rows that are not the maximum or minimum of the dataset, or rows that are not greater than or less than the value specified in the WHERE clause.

How to repeat:
CREATE TABLE `PageData` ( `PageID` bigint(20) unsigned NOT NULL default '0', `StartLineNumber` bigint(20) unsigned NOT NULL default '0', `EndLineNumber` bigint(20) unsigned NOT NULL default '0', `StartDateTime` datetime NOT NULL default '0000-00-00 00:00:00', `EndDateTime` datetime NOT NULL default '0000-00-00 00:00:00', `StationID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY  (`PageID`), KEY `EndDateTime` (`EndDateTime`)) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

CREATE TABLE `PageDataMyISAM` ( `PageID` bigint(20) unsigned NOT NULL default '0', `StartLineNumber` bigint(20) unsigned NOT NULL default '0', `EndLineNumber` bigint(20) unsigned NOT NULL default '0', `StartDateTime` datetime NOT NULL default '0000-00-00 00:00:00', `EndDateTime` datetime NOT NULL default '0000-00-00 00:00:00', `StationID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY  (`PageID`), KEY `EndDateTime` (`EndDateTime`)) ENGINE=MYISAM DEFAULT CHARSET=latin1;

[load test data set into each table by redirecting a file of INSERTs into mysql command line tool]

Running SELECT MIN(EndDateTime),MAX(EndDateTime) FROM PageData; and SELECT MIN(EndDateTime),MAX(EndDateTime) FROM PageDataMyISAM; produce different results.

Result from SELECT * FROM PageData ORDER BY EndDateTime DESC; produces randomly ordered rows.