can"t reopen table when using a temporary table and a self left join

Description:
When using temporary tables, the following script fails:

-----------------------------------
CREATE TEMPORARY TABLE zz1tmpq (id bigint NOT NULL auto_increment,root_id
int NOT NULL def
ault '0', level int default NULL, parent_id bigint default NULL, left_seq
int default NULL
, right_seq int default NULL, lookup_id bigint default NULL, PRIMARY KEY
(id), KEY parent_
id_k1 (parent_id), KEY left_seq_k1 (left_seq), KEY right_seq_k1
(right_seq), KEY lookup_id
_k1 (lookup_id) );

SELECT p.id FROM zz1tmpq AS p LEFT JOIN zz1tmpq AS c ON p.id = c.parent_id
WHERE c.parent_
id IS NULL AND p.parent_id = 1 AND p.root_id = 0;
----------------------------------------
with the following error:

--------------
ERROR 1137 at line 3: Can't reopen table: 'p'
--------------------

This works for regular tables (non-temporary ones).


How-To-Repeat:
see above.

Fix:
?

Synopsis: temporary tables and self left-join
Submitter-Id:
Originator: Chaim Bear
Organization: none
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld
Class: sw-bug
Release: mysql-3.23.53-max-nt

Exectutable: mysqld-nt
Environment: Intel Pentium III 933 Mhz, 128MB ram
System: Win2k
Architecture: i


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12822@lists.mysql.com
To unsubscribe, e-mail

Re: can"t reopen table when using a temporary table and a self left join

On Wednesday 23 October 2002 07:07, Chaim C. F. Bear wrote:
> Description:

> with the following error:
>=20
> --------------
> ERROR 1137 at line 3: Can't reopen table: 'p'=09
> --------------------
>=20
> This works for regular tables (non-temporary ones).


This is a known issue which is described in "Known Bugs" section in the=20
manual. You can't open temporary table more than once in the same query.

This is something we plan to fix around MySQL 5.0


--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12823@lists.mysql.com
To unsubscribe, e-mail