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: CPU Architecture:Any

[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] Valeriy 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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
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.