[bug] Temp table cannot be used twice in a query
>Description: When a temp table is included twice in a query mysql fails with the error: ERROR 1137 at line 9: Can't reopen table: 'foo' This happens on both 4.0.17 and 4.0.18. It did not happen on 4.0.14. >How-To-Repeat: create temporary table test (pk int primary key); select * from test as foo, test as bar where foo.pk=bar.pk; >Fix: Dropping the "temporary" from the above example fixes the problem, but is not a great solution. >Submitter-Id: John Heitmann >Originator: >Organization: >MySQL support: none >Synopsis: Temp table can't be used twice in a query [4.0.x] >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-4.0.18 (Source distribution) >Server: /opt/cn/bin/mysqladmin Ver 8.40 Distrib 4.0.18, for pc-linux on i686 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 4.0.18-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /opt/cn/var/mysql4/mysql.sock Uptime: 43 min 7 sec Threads: 9 Questions: 839777 Slow queries: 0 Opens: 2313 Flush tables: 1 Open tables: 64 Queries per second avg: 324.614 >C compiler:cc (GCC) 3.2.1 >C++ compiler: c++ (GCC) 3.2.1 >Environment: System: Linux ***blanked* 2.4.18-24.7.xsmp #3 SMP Thu Feb 13 10:34:29 EST 2003 i686 unknown Architecture: i686 Some paths: /opt/cn/bin/perl /opt/cn/bin/make /opt/cn/bin/gmake /opt/cn/bin/gcc /opt/cn/bin/cc GCC: Reading specs from /opt/cn/lib/gcc-lib/i686-pc-linux-gnu/3.2.1/specs Configured with: ../configure --prefix=/opt/cn --exec-prefix=/opt/cn --includedir=/opt/cn/include/gcc --with-gxx-include-dir=/opt/cn/include/g++ --with-local-prefix=/opt/cn/lib/gcc-lib --enable-languages=c,c++ --enable-threads=posix --disable-maintainer-mode --disable-shared --disable-nls --with-gnu-ld --with-ld=/opt/cn/bin/ld --with-gnu-as --with-as=/opt/cn/bin/as --with-gnu-as --with-gnu-ld Thread model: posix gcc version 3.2.1 Compilation info: CC='/opt/cn/bin/cc' CFLAGS='-O2 -pipe' CXX='/opt/cn/bin/c++' CXXFLAGS='-O2 -pipe' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 2 2003 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 15 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/opt/cn' '--sysconfdir=/opt/cn/etc/mysql4' '--localstatedir=/opt/cn/var/mysql4' '--libexecdir=/opt/cn/libexec/mysql4' '--with-unix-socket-path=/opt/cn/var/mysql4/mysql.sock' '--with-mysqld-user=bschmaus' '--enable-thread-safe-client' '--with-berkeley-db' '--with-low-memory' '--disable-shared' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' 'CC=/opt/cn/bin/cc' 'CFLAGS=-O2 -pipe' 'CXXFLAGS=-O2 -pipe' 'CXX=/opt/cn/bin/c++' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Erroneous results from a join after a delete
Hello,
I have come across a possible bug in MySQL that causes NULL values to
be erroneously returned from a join.
Problem: An inner join between two tables succeeds, but one column is
erroneously returned as all NULL if the join occurs after a delete in
table 1.
Platforms Tested: 3.23.43 (Solaris, Linux, Mac OS X), 3.23.47 (Linux)
Detail:
I have attached a stripped down version of the problem tables. Perform
the following actions after loading the tables:
select t1.*, t2.name from t1, t2 where t2.id=t2_id;
Note that the column 'item_id' returns the expected answers.
delete from t1 where t1_id=97;
select t1.*, t2.name from t1, t2 where t2.id=t2_id;
Note that the column 'item_id' is now unexpectedly all NULL.
Workaround:
The provided tables are pretty close to the edge of the bug. Deleting
columns causes it to go away. Deleting some rows causes it to go away.
Thanks for looking at this, and thanks for a cool product,
John Heitmann
CombineNet
# MySQL dump 8.16
#
# Host: localhostDatabase: bug
#
# Server version3.23.47
#
# Table structure for table 't1'
#
CREATE TABLE t1 (
t1_id int(11) default NULL,
t2_id int(11) default NULL,
type enum('Cost','Percent') default NULL,
cost_unit enum('Cost','Unit') default NULL,
min_value double default NULL,
max_value double default NULL,
t3_id int(11) default NULL,
item_id int(11) default NULL
) TYPE=MyISAM;
#
# Dumping data for table 't1'
#
INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (14,4,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (18,5,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (19,4,'Percent','Cost',-1,0,-1,-1);
INSERT INTO t1 VALUES (20,5,'Percent','Cost',100,-1,22,291);
INSERT INTO t1 VALUES (21,5,'Percent','Cost',100,-1,18,291);
INSERT INTO t1 VALUES (22,1,'Percent','Cost',100,-1,6,291);
INSERT INTO t1 VALUES (23,1,'Percent','Cost',100,-1,21,291);
INSERT INTO t1 VALUES (24,1,'Percent','Cost',100,-1,9,291);
INSERT INTO t1 VALUES (25,1,'Percent','Cost',100,-1,4,291);
INSERT INTO t1 VALUES (26,1,'Percent','Cost',100,-1,20,291);
INSERT INTO t1 VALUES (27,4,'Percent','Cost',100,-1,7,202);
INSERT INTO t1 VALUES (28,1,'Percent','Cost',50,-1,-1,137);
INSERT INTO t1 VALUES (29,2,'Percent','Cost',100,-1,4,354);
INSERT INTO t1 VALUES (30,2,'Percent','Cost',100,-1,9,137);
INSERT INTO t1 VALUES (93,2,'Cost','Cost',-1,1000,-1,-1);
INSERT INTO t1 VALUES (97,2,'Cost','Cost',-1,1000,-1,-1);
#
# Table structure for table 't2'
#
CREATE TABLE t2 (
id int(10) unsigned NOT NULL auto_increment,
name varchar(255) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table 't2'
#
INSERT INTO t2 VALUES (1,'s1');
INSERT INTO t2 VALUES (2,'s2');
INSERT INTO t2 VALUES (3,'s3');
INSERT INTO t2 VALUES (4,'s4');
INSERT INTO t2 VALUES (5,'s5');
-
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
Re: Re: Erroneous results from a join after a delete
On Thu, Jan 31, 2002 at 05:16:23PM +0100, [EMAIL PROTECTED] wrote: > Your message cannot be posted because it appears to be either spam or > simply off topic to our filter. To bypass the filter you must include > one of the following words in your message: > > sql,query > > If you just reply to this message, and include the entire text of it in the > reply, your reply will go through. However, you should > first review the text of the message to make sure it has something to do > with MySQL. Just typing the word MySQL once will be sufficient, for example. > > You have written the following: > > Thanks for the quick response. > > > > select t1.*, t2.name from t1, t2 where t2.id=t2_id; > > > > The above is actually expected behaviour, as you are not doing a join > > at all, but a full Cartesian product. > > That t2_id is actually from t1. Sorry for the confusing naming. Here > is a more verbose, but identical statement: > > select t1.*, t2.name from t1, t2 where t2.id=t1.t2_id; > > Since there is a join condition that works across both tables is this > still considered a cartesian product? I did a quick sanity check and > the number of rows returned from the problem statement equals the number > of rows in t1, rather than t1*t2. Even if it was, I don't see why > there is indeterminism in the return values for columns in the result of > a cartesian product. > > Thanks, > > John > > - 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
