MySQL Bugs: #8705: merge table of 2 temp tables works only if merge is also temporary

Bug #8705	merge table of 2 temp tables works only if merge is also temporary
Submitted:	22 Feb 2005 18:35	Modified:	29 Jul 2005 14:48
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
It is possible to creat a merge table of two (actually one or more) temporary tables, as shown in "how to repeat".

This table does only work, if it is temporary. Creating a non temporary merge table, with temporary tables in the union, willsucceed, but fail if accesed.

Of course there are dangers in creating something permament on a temporary base, (and what would happen if users in an other session access the merge table, will they get access to the private temp ?). So it should probably not be possible, to create a non-temporary merge on temporary-union.

Either way, it should work, or fail at creation time. If it worked, then clarification is needed on access through other sessions.

In addition:
since temporary tables in the unions are not fully tested, would it be possible to confirm, if the case that works (temp merge on temp union) is tested?
It would be very usefull, if this bit of functionality, could be ensured and trusted, to exist in future 4.1 versions of mysql.
It could then be documented, and it provides a way to reuse temporary tables within one query (YES it does:-) )

How to repeat:
create temporary table tmp1 (a int) select 1 a;
create temporary table tmp2 (a int) select 2 a;

create table mrg1 (a int) engine='merge' union (tmp1, tmp2);
Query OK, 0 rows affected (0.01 sec)

create temporary table mrg2 (a int) engine='merge' union (tmp1, tmp2);
Query OK, 0 rows affected (0.00 sec)

select * from mrg1;
ERROR 1017 (HY000): Can't find file: 'mrg1.MRG' (errno: 2)

select * from mrg2;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

Suggested fix:
Decide on the functionality wanted, and document it.
either have the "create non_temp merge union (temp)" fail,
or make it working and decide on the conflicts mentioned above.

Ensure that 
create temporary table mrg2 (a int) engine='merge' union (tmp1);
is a documented and tested part of mysql, as it is very usefull

merge tables are generally not obeying the datadir. (as tmp tables are written to a different dir, that is why the above fails)

create table src1 (a int) select 1 a;
create table src2 (a int)  data directory='/tmp' select 2 a;
create   table mrx (a int, b int) ENGINE='MERGE' UNION (src1, src2);
select * from mrx;
ERROR 1016 (HY000): Can't open file: 'mrx.MRG' (errno: 143)
# looking at the files
cat  /var/db/mysql/xxx/mrx.MRG
/var/db/mysql/xxx/src1
/var/db/mysql/xxx/src2 # obviously not here

ignore the last comment, it works, if the table specs are correct (sorry), but the original submission is still valid

actually create merge table, doesnt check anything about the tables in the union.

create table merge1 (a int) ENGINE='MERGE' UNION (fredgerws_does_not_exists) ;
Query OK, 0 rows affected (0.00 sec)

It is normal behaviour of MERGE tables that no checks are done at creation time. This is documented.
The weird error messages are normal behaviour too. This is not documented though. ;)
The MERGE engine is to be replaced by something else in the forseable future. This should hopefully fix these problems.

A MERGE table must be temporary if the MyISAM tables are temporary (you found this by yourself). I requested this to be documented soon.

There is a test for temporary MERGE tables in our test suite (see mysql-test/t/merge.test).