MySQL Forums
Forum List  »  Merge Storage Engine

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
MERGE table index fails to contain all records
10146
October 04, 2006 12:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.