MySQL Bugs: #31348: MERGE fails with 'not identical' in V5.0.45

Bug #31348	MERGE fails with 'not identical' in V5.0.45
Submitted:	2 Oct 2007 17:17	Modified:	3 Oct 2007 18:02
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (and Windows 2Ksvr)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
V5.0.45 rejects an ALTER TABLE ... UNION=(....) 
with ERROR 1168 (HY000): Unable to open underlying table which is differently defined  or of non-MyISAM type or doesn't exist

There are no apparent differences between the tables. They were created by program with the same definition, and are accepted as identical by V5.0.27.
I have this problem on hundreds of tables, but it can be reproduced with the simplest 1 table merge. I will upload the required tables.

The problem occurred originally on Linux (FC4). The tables were copied to windows and the problem reproduces.

CONSOLE LOG
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.27-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp  ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table temp   like bug_model;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table temp   engine=merge union=( bug_table );
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

//////////// STOPPED Mysql and changed versions ///////////////

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: coldlogic

+-------------------------+
| version()               |
+-------------------------+
| 5.0.45-community-nt-log |
+-------------------------+
1 row in set (0.20 sec)

mysql> drop table if exists temp  ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table temp   like bug_model;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table temp   engine=merge union=( bug_table );
ERROR 1168 (HY000): Unable to open underlying table which is differently defined
 or of non-MyISAM type or doesn't exist
mysql>

How to repeat:
/* first upload my tables bug_model and bug_table */

select version();
drop table if exists temp  ;
create table temp   like bug_model;
alter table temp   engine=merge union=( bug_table );

Thank you for a problem report. Please, send the results of:

SHOW CREATE TABLE bug_model;

Sorry - my mistake: Although 5.0.27 considers these two tables to be identical and will perform a MERGE on them, they are not in fact identical, so V5.0.45 is correct in rejecting the merge.  One of the columns was changed from NOT NULL to DEFAULT NULL - but that is my bug not yours.

Therefore this is NOT A BUG, unless you consider V5.0.27 NOT rejecting the merge to be an issue.

Looks like it is not a bug in 5.0.45.