MERGE table index fails to contain all records
Posted by:
ahigerd
Date: October 04, 2006 12:29PM
I was hoping to use a MERGE table to unify a table and its "deleted" counterpart, so that I could have a single table I could use to view audit history. Unfortunately, when I make queries against the MERGE table, only records in the "deleted" table appear in the result set. On the bright side, I was able to make a simple reproducible example of this error; this experimentation demonstrates that the behavior is more complex than simply failing to index one of the tables in the merge.
Example of behavior:
=====================================
mysql> create table test1 (id integer primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> create table test2 (id integer primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> create table testm (id integer, index(id)) engine=merge union=(test1,test2);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into test2 values (4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from testm;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
mysql> select * from testm where id=2;
Empty set (0.00 sec)
mysql> select * from testm where id<5;
+------+
| id |
+------+
| 2 |
| 3 |
| 4 |
+------+
3 rows in set (0.00 sec)
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.0.24-log |
+---------------+------------+
1 row in set (0.00 sec)