>Description:
Query doesn't work on temprory table while works on normal tables.
>How-To-Repeat:
CREATE TEMPORARY TABLE tree (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
left_col INT NOT NULL,
UNIQUE (left_col),
right_col INT NOT NULL,
UNIQUE (right_col)
);
INSERT INTO tree (right_col, left_col, name) VALUES ('12', '1', 'Root');
INSERT INTO tree (right_col, left_col, name) VALUES ('7', '2', 'Category1');
INSERT INTO tree (right_col, left_col, name) VALUES ('4', '3', 'Elem1');
INSERT INTO tree (right_col, left_col, name) VALUES ('6', '5', 'Elem2');
INSERT INTO tree (right_col, left_col, name) VALUES ('11', '8', 'Category2');
INSERT INTO tree (right_col, left_col, name) VALUES ('10', '9', 'Elem3');
SELECT c.name
FROM tree c, tree p
LEFT JOIN tree m
ON m.left_col < c.left_col AND c.right_col < m.right_col
AND p.left_col < m.left_col AND m.right_col < p.right_col
WHERE p.left_col < c.left_col
AND c.right_col < p.right_col
AND m.id IS NULL AND p.id = 1;
SELECT will not work but print error:
Can't reopen table: 'c'
>Fix:
No idea
>Submitter-Id: ilya
>Originator: Ilya Martynov
>Organization:
>
>MySQL support: none
>Synopsis: Query doesn't work on temprorary table
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.36 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on i586
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.36-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 5 hours 25 min 5 sec
Threads: 13 Questions: 1258 Slow queries: 0 Opens: 45 Flush tables: 1 Open
tables: 16 Queries per second avg: 0.064
>Environment:
System: Linux abra.ru 2.4.5 #1 óÂÔ éÀÎ 30 13:57:23 MSD 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20010319 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='-O2 -fomit-frame-pointer -g' CXX='g++'
CXXFLAGS='-O2 -fomit-frame-pointer -g -felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 éÀÎ 25 22:23 /lib/libc.so.6 -> libc-2.2.3.so
-rwxr-xr-x 1 root root 1104456 íÁÊ 29 02:49 /lib/libc-2.2.3.so
-rw-r--r-- 1 root root 2522408 íÁÊ 29 02:51 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 íÁÊ 29 02:51 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --exec-prefix=/usr
--libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql
--localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info
--mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler
--with-berkeley-db --with-innobase --enable-static --enable-shared --with-raid
--without-readline --with-unix-socket-path=/var/run/mysqld/mysqld.sock
--with-mysqld-user=mysql --without-bench --with-extra-charsets=all
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
