MySQL Bugs: #9112: Merge table with composite index producing invalid results with some queries

Bug #9112	Merge table with composite index producing invalid results with some queries
Submitted:	10 Mar 2005 23:54	Modified:	30 Sep 2005 18:43
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.25	OS:
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
Queries against MERGE tables which have a composite index can produce invalid results.

In the provided test case, for example, MIN() is reporting NULL though there are non-NULL values present in the column.

Sometimes a FLUSH TABLES corrects the problem.

How to repeat:
DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1 (
 a DOUBLE(16,6),
 b VARCHAR(10),
 INDEX (a,b)
) TYPE=MERGE UNION=(t2,t3);

CREATE TABLE t2 (
 a DOUBLE(16,6),
 b VARCHAR(10),
 INDEX (a,b)
) TYPE=MyISAM;

CREATE TABLE t3 (
 a DOUBLE(16,6),
 b VARCHAR(10),
 INDEX (a,b)
) TYPE=MyISAM;

INSERT INTO t2 VALUES ( NULL, '');
INSERT INTO t2 VALUES ( 9999999999.999999, '');
INSERT INTO t3 SELECT * FROM t2;
SELECT MIN(a), MAX(a) FROM t1;
FLUSH TABLES;
SELECT MIN(a), MAX(a) FROM t1;
DROP TABLE t1, t2, t3;

Suggested fix:
n/a

I cannot repeat this with 4.0.26-debug-log nor with 4.1.14-debug-log on Debian GNU/Linux 2.6.8, gcc 3.3.5. Please describe your test environment more precisely.

I have been running into this problem (or what I think is the same problem) as well but I have not been able to come up with a simple test case. I am running 4.1.13-standard on RHEL4.

I can workaround this problem by changing my queries to use "like 'needle%'" instead of "= 'needle'", although the queries are then much slower. Below are some examples of how those two queries return either results or NULL, respectively, when both should return the same numeric results. 

What's interesting is that this only happens on some parts of the index. I can do a query involving "= 'needle1'" through "needle8" and get results, but "needle9" will fail to find the records unless I change the query to "like 'needle9%'". 

mysql> select  sum(foo),sum(foo+bar) from my_merge_table where key1='some_string'  and key2> date_sub(curdate(),interval 0 day)+0;
+-----------------------+----------------------------------+
| sum(foo) | sum(foo+bar) |
+-----------------------+----------------------------------+
|                  NULL |                             NULL |
+-----------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> explain select  sum(foo),sum(foo+bar) from my_merge_table where key1='some_string'  and key2> date_sub(curdate(),interval 0 day)+0;
+----+-------------+-------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | my_merge_table | range | key1_key2_index      | key1_key2_index |      68 | NULL | 3028 | Using where |
+----+-------------+-------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

# now I use LIKE and I get data back, albeit slowly.

mysql> select  sum(foo),sum(foo+bar) from my_merge_table where key1 like 'some_string%'  and key2> date_sub(curdate(),interval 0 day)+0;
+-----------------------+----------------------------------+
| sum(foo) | sum(foo+bar) |
+-----------------------+----------------------------------+
|                  6762 |                             6762 |
+-----------------------+----------------------------------+
1 row in set (1.31 sec)

mysql> explain select  sum(foo),sum(foo+bar) from my_merge_table where key1 like 'some_string%'  and key2> date_sub(curdate(),interval 0 day)+0;
+----+-------------+-------------------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table             | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-------------------+-------+---------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | my_merge_table | range | key1_key2_index      | key1_key2_index |      68 | NULL | 84109 | Using where |
+----+-------------+-------------------+-------+---------------+----------+---------+------+-------+-------------+
1 row in set (0.00 sec)

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/internals/29527

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/internals/30250

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/internals/30273

It was a MERGE table handler bug. It tried to use an internal variable for a wrong purpose. As a result it got the wrong key length back and thus compared random buffer contents. This could happen with many combinations of commands (the FLUSH healed it by chance only). The necessary condition was a composite, variable length key.

Pushed to 4.0.27, 4.1.15, and 5.0.14.

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/internals/30377

Congratulations on tracking this down and finding a fix! I'm curious now if the comment I added on [31 Jul 21:20] is in fact the same issue. Do you think this fix will address it or should I open another bug with this information?

Difficult to say. I don't know how your table layout and key definitions look like. So I can't tell if you may be affected. Try your commands on a MyISAM table and look if it behaves differently.

I heared of problems with key matching which could be circumvented with LIKE. So your problem may already be reported in another bug report if the above does not help.

The numbers I used in the test had a too high numeric precision (too many digits) to be accurately reproduced on all platforms. I fixed this by reducing the number of digits.
Pushed to 4.0.27, 4.1.15, and 5.0.14.

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/internals/30504

My first reduction of precision was not sufficient for all platforms.
Pushed another reduction to the same versions.

Noted in 4.0.27, 4.1.15, 5.0.14 changelogs.

Ingo-
   I am actually using MyISAM tables. The issue is specific to the MERGE table, not the underlying tables. That is, if I query the underlying table directly, the data is found. If I query the MERGE table, I sometimes get back a null set depending on what part of the data I'm querying. But then if I use LIKE instead of "=" on the MERGE table, I get the answer I want, albeit 20x slower.

   I have been hunting around for an existing bug report on this for a while and thought #9112 was the same thing. If you run across another bug that is more in line with what I am explaining, please post a comment.

Hi Karl,

4.1.15 will be out in the next days. It contains the fix for this bug. Please try with this one first. If it does not solve your problem, report a new bug. I did not find any bug report around LIKE either.

Regards, Ingo

I submitted what appears to be a related bug: http://bugs.mysql.com/bug.php?id=24342

Please add comments to it if you see fit.