MySQL Bugs: #5524: MERGE engine: comparison against timestamp field fails

Bug #5524	MERGE engine: comparison against timestamp field fails
Submitted:	10 Sep 2004 22:20	Modified:	13 Sep 2004 18:01
Reporter:	Timothy Smith	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (Linux / Solaris)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
When searching against a timestamp column in a MyISAM table, it returns a row properly.  When
searching againsta  MERGE table composed of just that MyISAM table, it returns the empty set.

I tested this on a recent 4.0.20+ bk tree on Linux.  The customer tested it against 4.0.20 on Solaris.

How to repeat:
CREATE TABLE BANNER_LOG_200401 (
  ID int(10) unsigned NOT NULL auto_increment,
  t0 timestamp(14) NOT NULL,
  i char(64) binary NOT NULL default '',
  s char(255) NOT NULL default '',
  j char(255) NOT NULL default '',
  cv enum('c','v') NOT NULL default 'c',
  PRIMARY KEY  (ID),
  KEY i (i(10)),
  KEY cv (cv),
  KEY s (s(10)),
  KEY j (j(10)),
  KEY t0 (t0)
) TYPE=MyISAM;

INSERT INTO BANNER_LOG_200401 VALUES (1,20040101000047,'','','','c');
INSERT INTO BANNER_LOG_200401 VALUES (2,20040101000044,'','','','c');
INSERT INTO BANNER_LOG_200401 VALUES (3,20040101000040,'','','','c');

CREATE TABLE m (
  ID int(10) unsigned NOT NULL default '0',
  t0 timestamp(14) NOT NULL,
  i char(64) binary NOT NULL default '',
  s char(255) NOT NULL default '',
  j char(255) NOT NULL default '',
  cv enum('c','v') NOT NULL default 'c',
  KEY i (i(10)),
  KEY cv (cv),
  KEY s (s(10)),
  KEY j (j(10)),
  KEY t0 (t0)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(BANNER_LOG_200401);

mysql> SELECT * FROM BANNER_LOG_200401 where t0>'2004-01-01' LIMIT 1;
+----+----------------+---+---+---+----+
| ID | t0             | i | s | j | cv |
+----+----------------+---+---+---+----+
|  3 | 20040101000040 |   |   |   | c  |
+----+----------------+---+---+---+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM m where t0>'2004-01-01' LIMIT 1;
Empty set (0.00 sec)

mysql> SELECT * FROM m where UNIX_TIMESTAMP(t0)>'2004-01-01' LIMIT 1;
+----+----------------+---+---+---+----+
| ID | t0             | i | s | j | cv |
+----+----------------+---+---+---+----+
|  1 | 20040101000047 |   |   |   | c  |
+----+----------------+---+---+---+----+
1 row in set (0.00 sec)

Suggested fix:
Unknown

The manual is not overly clear about MERGE tables. It stresses that all merged MyISAM tables must have identical column and index information. It does not exactly say that the same is true for the MERGE table. The only exception is that primary (unique) indexes are to be replaced by normal indexes.

In your example you simply dropped the primary key. So the MyISAM table had 5 keys, while the MERGE table had 4 keys. Since your query uses a range scan over the fourth index (you can see that by putting "explain " in front of the select statement), also the fourth index of the MyISAM table is used. This is however a char(255) column, not a timestamp.

By simply inserting "KEY ID (ID)," as the first key of the merge table, the queries work fine. The number and order of the keys do then match between the MERGE and MyISAM table except of the uniqueness of the primary key.

The reason for the third query to succeed was that the optimizer does not use a range scan over an index for the UNIX_TIMESTAMP() function. Instead a table scan is executed, which delivers all rows independent of the index mismatch.