Bug #3221 UNION doesn't work with prepared statements
Submitted: 18 Mar 2004 5:59 Modified: 9 Apr 2004 0:40
Reporter: Georg Richter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.2 OS:Any (all)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[18 Mar 2004 5:59] Georg Richter 
Description:
mysql_stmt_prepare fails when using the same table twice with UNION.
(error from mysqli_stmt_error: Can't reopen table xxxx).

How to repeat:
#include <mysql.h>
#include <stdio.h>

int main() {
	MYSQL		*mysql;
	MYSQL_STMT	*stmt;
	char		query[512];

	mysql = mysql_init(NULL);
	mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, 0);

	/* create a temporary table */
	mysql_query(mysql, "CREATE TEMPORARY TABLE t1 (c1 int, c2 int)");

	/* insert some values */
	mysql_query(mysql, "INSERT INTO t1 VALUES (1,2),(2,1)");

	strcpy(query, "SELECT c1 FROM t1 UNION SELECT c2 FROM t1");

	/* create a prepared statement */
	stmt = mysql_stmt_init(mysql);
	mysql_stmt_prepare(stmt, query, strlen(query));

	printf("Error: %s\n", mysql_stmt_error(stmt));

	mysql_stmt_close(stmt);
	mysql_close(mysql);
}
[9 Apr 2004 0:40] Oleksandr Byelkin 
Thank you for bugreport, but it is known limitation of temporary tables (one 
temporary table can't be used twice. 
 
http://dev.mysql.com/doc/mysql/en/Temporary_table_problems.html