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

[10 Mar 2005 23:54] Dean Ellis
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
[19 Jul 2005 12:56] Ingo Strüwing
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.
[31 Jul 2005 19:20] Karl Wagner
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)
[8 Sep 2005 19:41] Bugs System
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
[23 Sep 2005 8:15] Bugs System
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
[23 Sep 2005 15:22] Bugs System
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
[26 Sep 2005 13:26] Ingo Strüwing
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.
[27 Sep 2005 13:26] Bugs System
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
[28 Sep 2005 0:20] Karl Wagner
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?
[28 Sep 2005 9:05] Ingo Strüwing
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.
[29 Sep 2005 10:13] Ingo Strüwing
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.
[29 Sep 2005 11:37] Bugs System
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
[29 Sep 2005 13:26] Ingo Strüwing
My first reduction of precision was not sufficient for all platforms.
Pushed another reduction to the same versions.
[30 Sep 2005 18:43] Paul Dubois
Noted in 4.0.27, 4.1.15, 5.0.14 changelogs.
[2 Oct 2005 6:37] Karl Wagner
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.
[18 Oct 2005 10:42] Ingo Strüwing
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
[15 Nov 2006 18:34] Razvan Surdulescu
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.