temporary merge table as table name alias

--0016e6d7e681857aa3047a752a53
Content-Type: text/plain; charset=UTF-8

All,

Is is possible to create temporary MERGE tables?

This code gives an error:

-------------------- 8< ---------------------------------------- 8<
--------------------

-- clean slate
DROP TABLE IF EXISTS test_abcdefgh;
DROP TABLE IF EXISTS dante;

-- create demo table
CREATE TABLE test_abcdefgh (
`myvalue` INT(11) UNSIGNED NOT NULL,
`modified` DATETIME DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- sample data
INSERT INTO test_abcdefgh (myvalue) VALUES (1), (3), (5);

-- clone the table structure
CREATE TEMPORARY TABLE dante LIKE test_abcdefgh;

-- change the temporary table to a merge table
ALTER TABLE dante ENGINE=MERGE UNION(test_abcdefgh);

-- check tables
SELECT * FROM test_abcdefgh;
SELECT * FROM dante;

-------------------- 8< ---------------------------------------- 8<
--------------------

Error Code : 1168
Unable to open underlying table which is differently defined or of
non-MyISAM type or doesn't exist

If you change the line to:

> CREATE TABLE dante LIKE test_abcdefgh;

instead of:

> CREATE TEMPORARY TABLE dante LIKE test_abcdefgh;

Then, the error goes away and the code works. Having a TEMPORARY table is
very important for me, though, because I want the table name "dante" to not
be visible to other connections. The whole point of this code is to enable
me to create "alias" names for tables inside a stored procedure.

Can someone tell me why the temporary merge table is failing?

Dante

--0016e6d7e681857aa3047a752a53--