MySQL Bugs: #24342: Incorrect results with query over MERGE table

Bug #24342	Incorrect results with query over MERGE table
Submitted:	15 Nov 2006 18:32	Modified:	1 May 2007 17:58
Reporter:	Razvan Surdulescu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.27, 5.1	OS:	Linux (Linux RedHat)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	regression

Description:
I have 3 tables (q1, q2, q3) and a MERGE table (q) that is a UNION(q1, q2, q3). All tables have compound indexes. When I run a query over the MERGE table, if the query retrieves data from the first underlying table (q1), it works fine. However, if the query retrieves data from the second underlying table (q2), it only returns the first row.

The repeat scenario below will illustrate this very clearly (the comments at the end explain what should be the expected result and how the repeat case differs). The SQL below is extremely delicate, meaning that even the slightest change makes it no longer reproduce. For example, the test case will not repeat if you do any of the following:
  * Remove one of the INSERT statements
  * Change one of the INSERT statements (like 'ebay ' to 'ebayx')
  * Remove q3 from the UNION

I would guess this means that the bug is likely some subtle memory or data corruption somewhere that this case happens to hit exactly right.

Besides MySQL 5.0.27, I also tested this on MySQL 5.0.18 and 5.0.22.

How to repeat:

flush tables;

-- create the constituent tables
-- note that all schemas for q1, q2, q3 is exactly identical
drop table if exists q1;
drop table if exists q2;
drop table if exists q3;

CREATE TABLE q1 (
  Query varchar(255) default NULL,
  LoadDate date default NULL,
  KEY Query (Query,LoadDate),
  KEY LoadDate (LoadDate)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE q2 (
  Query varchar(255) default NULL,
  LoadDate date default NULL,
  KEY Query (Query,LoadDate),
  KEY LoadDate (LoadDate)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE q3 (
  Query varchar(255) default NULL,
  LoadDate date default NULL,
  KEY Query (Query,LoadDate),
  KEY LoadDate (LoadDate)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- insert values into q1, q2, q3
-- in order for the scenario to repeat, these values have to be exactly
-- like they are below, any change however small will not make it repeat
insert into q1 values('ebaay','2006-09-23');
insert into q1 values('ebaby','2006-09-23');
insert into q1 values('ebaey','2006-09-23');
insert into q1 values('ebags','2006-09-23');
insert into q1 values('ebai','2006-09-23'); 
insert into q1 values('ebaj','2006-09-23'); 
insert into q1 values('ebat','2006-09-23'); 
insert into q1 values('ebau','2006-09-23'); 
insert into q1 values('ebaum','2006-09-23');
insert into q1 values('EBAY','2006-09-23'); 
insert into q1 values('EBay','2006-09-23'); 
insert into q1 values('Ebay','2006-09-23'); 
insert into q1 values('eBAY','2006-09-23'); 
insert into q1 values('eBay','2006-09-23'); 
insert into q1 values('ebAY','2006-09-23'); 
insert into q1 values('ebaY','2006-09-23'); 
insert into q1 values('ebay ','2006-09-23');
insert into q1 values('ebay','2006-09-23'); 
insert into q1 values('ebayx','2006-09-23');
insert into q1 values('ebayx','2006-09-23');
insert into q1 values('ebayx','2006-09-23');
insert into q1 values('EBAYE','2006-09-23');
insert into q1 values('ebaye','2006-09-23');
insert into q1 values('ebays','2006-09-23');
insert into q1 values('ebayu','2006-09-23');
insert into q1 values('ebayy','2006-09-23');
insert into q1 values('ebayx','2006-09-23');
insert into q1 values('ebayx','2006-09-23');

insert into q2 values('ebaay','2006-09-24');
insert into q2 values('ebaby','2006-09-24');
insert into q2 values('ebags','2006-09-24');
insert into q2 values('ebai','2006-09-24'); 
insert into q2 values('ebail','2006-09-24');
insert into q2 values('ebaj','2006-09-24'); 
insert into q2 values('ebasy','2006-09-24');
insert into q2 values('ebat','2006-09-24'); 
insert into q2 values('ebau','2006-09-24'); 
insert into q2 values('ebaum','2006-09-24');
insert into q2 values('EBAY ','2006-09-24');
insert into q2 values('EBAY','2006-09-24'); 
insert into q2 values('EBAy','2006-09-24'); 
insert into q2 values('EBay','2006-09-24'); 
insert into q2 values('EbAY','2006-09-24'); 
insert into q2 values('Ebay','2006-09-24'); 
insert into q2 values('eBAY','2006-09-24'); 
insert into q2 values('eBay','2006-09-24'); 
insert into q2 values('ebAY','2006-09-24'); 
insert into q2 values('ebaY','2006-09-24'); 
insert into q2 values('ebay ','2006-09-24');
insert into q2 values('ebay','2006-09-24'); 
insert into q2 values('ebayx','2006-09-24');
insert into q2 values('ebayx','2006-09-24');
insert into q2 values('ebayx','2006-09-24');
insert into q2 values('ebayx','2006-09-24');
insert into q2 values('EBAYE','2006-09-24');
insert into q2 values('ebaye','2006-09-24');
insert into q2 values('ebays','2006-09-24');
insert into q2 values('ebayy','2006-09-24');
insert into q2 values('ebayx','2006-09-24');
insert into q2 values('ebayx','2006-09-24');

insert into q3 values('ebaay','2006-09-25');
insert into q3 values('ebaby','2006-09-25');
insert into q3 values('ebags','2006-09-25');
insert into q3 values('ebai','2006-09-25'); 
insert into q3 values('ebaj','2006-09-25'); 
insert into q3 values('ebat','2006-09-25'); 
insert into q3 values('ebau','2006-09-25'); 
insert into q3 values('ebaum','2006-09-25');
insert into q3 values('EBAY ','2006-09-25');
insert into q3 values('EBAY','2006-09-25'); 
insert into q3 values('EBAy','2006-09-25'); 
insert into q3 values('EBay','2006-09-25'); 
insert into q3 values('EbAY','2006-09-25'); 
insert into q3 values('Ebay','2006-09-25'); 
insert into q3 values('eBAY','2006-09-25'); 
insert into q3 values('eBay','2006-09-25'); 
insert into q3 values('ebAY','2006-09-25'); 
insert into q3 values('ebaY','2006-09-25'); 
insert into q3 values('ebay ','2006-09-25');
insert into q3 values('ebay','2006-09-25'); 
insert into q3 values('ebayx','2006-09-25');
insert into q3 values('ebayx','2006-09-25');
insert into q3 values('ebayx','2006-09-25');
insert into q3 values('ebayx','2006-09-25');
insert into q3 values('EBAYE','2006-09-25');
insert into q3 values('ebaye','2006-09-25');
insert into q3 values('ebays','2006-09-25');
insert into q3 values('ebayu','2006-09-25');
insert into q3 values('ebayx','2006-09-25');
insert into q3 values('ebayx','2006-09-25');

-- create the MERGE table over (q1, q2, q3) above
-- the schema for q is exactly identical to that for (q1, q2, q3)
drop table if exists q;

CREATE TABLE q (
	Query varchar(255) default NULL,
	LoadDate date default NULL,
	KEY Query (Query,LoadDate),
	KEY LoadDate (LoadDate)
) ENGINE=MERGE DEFAULT CHARSET=latin1 UNION=(q1, q2, q3);

flush tables;

-- if all went right, this should report 90 rows
select count(*) from q;

-- this query works: it retrieves 9 rows from the MERGE
-- table that match the string 'ebay' (in case INsensitive fashion)
-- from 2006-09-23
select Query, LoadDate from q where Query = 'EBAY' and LoadDate = '2006-09-23';

-- this query does NOT work: it retrieves only 1 row from the MERGE
-- table, even though there are 12 such rows in the underlying q2 table
-- it appears that the query just retrieves the first row, then stops
select Query, LoadDate from q where Query = 'EBAY' and LoadDate = '2006-09-24';

Suggested fix:
This appears to be some sort of memory corruption in the MERGE table handler.

This appears to be a related bug: http://bugs.mysql.com/bug.php?id=9112

Verified against 5.0 and 5.1 (current dev trees).  I could not repeat it against 4.0 or 4.1.

I simplified your test case, but thank you for submitting a very thorough case.

FLUSH TABLES;
DROP TABLE IF EXISTS t1, t2, t3, t4;
CREATE TABLE t1 ( a VARCHAR(255), b INT, INDEX (a,b) ) ENGINE=MyISAM;
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t2;
CREATE TABLE t4 ( a VARCHAR(255), b INT, INDEX (a,b) ) ENGINE=MERGE UNION=(t1,t2,t3);
INSERT INTO t1 VALUES ('abcd ',1);
INSERT INTO t1 VALUES ('abcd',1);
INSERT INTO t2 VALUES ('abcd ',2);
INSERT INTO t2 VALUES ('abcd',2);
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t3 VALUES ('abcd ',3);
INSERT INTO t3 VALUES ('abcd',3);
INSERT INTO t3 SELECT * FROM t3;
-- 2, correct
SELECT COUNT(*) x FROM t1 WHERE a = 'abcd' AND b = 1;
-- 2, correct
SELECT COUNT(*) x FROM t4 WHERE a = 'abcd' AND b = 1;
-- 4, correct
SELECT COUNT(*) x FROM t2 WHERE a = 'abcd' AND b = 2;
-- 1, incorrect
SELECT COUNT(*) x FROM t4 WHERE a = 'abcd' AND b = 2;
-- 4, correct
SELECT COUNT(*) x FROM t3 WHERE a = 'abcd' AND b = 3;
-- 1, incorrect
SELECT COUNT(*) x FROM t4 WHERE a = 'abcd' AND b = 3;
-- 2, incorrect
SELECT COUNT(*) FROM t4 WHERE a = 'abcd' AND b > 1;

Hello, I noticed there hasn't been any activity on this bug report for about a month. This bug is causing us a lot of headaches, is there a plan to release a fix for it in an upcoming release? Thanks, Razvan.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17687

ChangeSet@1.2358, 2007-01-05 14:50:19-08:00, acurtis@xiphis.org +3 -0
  Bug#24342
    "Incorrect results with query over MERGE table"
    mi_rnext_same uses wrong value when checking keys

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24196

ChangeSet@1.2630, 2007-04-11 01:40:35+05:00, svoj@mysql.com +5 -0
  BUG#24342 - Incorrect results with query over MERGE table
  MERGE engine may return incorrect values when several representations
  of equal keys are present in the index. For example "groß" and "gross"
  or "gross" and "gross " (trailing space), which are considered equal,
  but have different lengths.
  
  The problem was that key length was not recalculated after key lookup.
  
  Only MERGE engine is affected.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Pushed into 4.1.23

Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.