Bug #33653 keys in merge tables
Submitted: 3 Jan 2008 15:02 Modified: 4 Feb 2008 5:34
Reporter: Leszek Rychlewski Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:14.12 Distrib 5.0.22 OS:Any
Assigned to:

[3 Jan 2008 15:02] Leszek Rychlewski
Description:
Looks like key order in merge table and sub-tables must match and mismatches are silently ignored.

How to repeat:
mysql> create table t1 (a int, b int, key (b), key(a));
Query OK, 0 rows affected (0.08 sec)

mysql> create table tm (a int, b int, key (a)) engine=merge union=(t1);
Query OK, 0 rows affected (0.06 sec)

mysql> insert t1 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 where a=1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tm where a=1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

Suggested fix:
make sure the order of the keys is identical in all tables (same as columns)
[3 Jan 2008 15:08] MC Brown
I've tested this on 5.0.44 and 5.1.22 with the same result/error message.
[4 Jan 2008 5:34] Valerii Kravchuk
Please, check with a newer version, 5.0.45 at least. On 5.0.54 I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.16 sec)

mysql> create table t1 (a int, b int, key (b), key(a));
Query OK, 0 rows affected (0.09 sec)

mysql> create table tm (a int, b int, key (a)) engine=merge union=(t1);
Query OK, 0 rows affected (0.08 sec)

mysql> insert t1 values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> insert t1 values (1,2);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1 where a=1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.03 sec)

mysql> select * from tm where a=1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
 or of non-MyISAM type or doesn't exist
[5 Feb 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".