>Description:
SELECT
r.resource_id, person_id
FROM
resources r
LEFT JOIN
resource_persons USING (resource_id)
WHERE
r.resource_id IN (1)
This query will crash if table resources has more than one
row and table resource_persons has zero rows.
gdb backtrace, mysql.trace, etc. are available at:
http://tim.desert.net/~tim/sql_select-bug.txt
>How-To-Repeat:
Run the following shell script. Notice that if you only insert one
row into the resources table, the bug isn't triggered. Also, the
resource_persons table must be empty, or the bug isn't triggered.
#!/bin/sh
mysqladmin -f drop thimble 2>/dev/null
mysqladmin create thimble
cat <<EOS | mysql thimble
# MySQL dump 5.13
#
# Host: localhost Database: thimble
#--------------------------------------------------------
# Server version 3.22.20a-debug
#
# Table structure for table 'resource_persons'
#
CREATE TABLE resource_persons (
resource_id mediumint(8) unsigned DEFAULT '0' NOT NULL,
person_id mediumint(8) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (resource_id,person_id)
);
#
# Dumping data for table 'resource_persons'
#
#
# Table structure for table 'resources'
#
CREATE TABLE resources (
resource_id mediumint(8) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (resource_id)
);
#
# Dumping data for table 'resources'
#
INSERT INTO resources VALUES (1);
INSERT INTO resources VALUES (2);
EOS
count=2
while [ $count -gt 0 ]; do
count=`expr $count - 1`
echo " <querying>";
cat <<EOS | mysql thimble
SELECT
r.resource_id, person_id
FROM
resources r
LEFT JOIN
resource_persons USING (resource_id)
WHERE
r.resource_id IN (1)
EOS
done
exit 0
>Fix:
I'm not sure that this is the best way to handle it. But it does
fix the problem.
*** sql/sql_select.cc.orig Thu Apr 1 21:17:41 1999
--- sql/sql_select.cc Thu Apr 1 21:17:58 1999
***************
*** 1438,1444 ****
!(rec_per_key=(double) table->keyfile_info.rec_per_key[key]))
rec_per_key=(double) s->records/rec+1;
! if (rec_per_key/(double) s->records >= 0.01)
tmp=rec_per_key;
else
{
--- 1438,1444 ----
!(rec_per_key=(double) table->keyfile_info.rec_per_key[key]))
rec_per_key=(double) s->records/rec+1;
! if (s->records != 0 && rec_per_key/(double) s->records >=
0.01)
tmp=rec_per_key;
else
{
>Submitter-Id: <submitter ID>
>Originator: Thimble Smith
>Organization:
DesertNet
>MySQL support: none
>Synopsis: mysqld crashes on WHERE IN (1) query
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.20a (Source distribution)
>Server: /usr/local/mysql/bin/mysqladmin Ver 7.8 Distrib 3.22.20a, for
> unknown-freebsd2.2.7 on i386
TCX Datakonsult AB, by Monty
Server version 3.22.20a-debug-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /usr/local/mysql/var/mysql.sock
Uptime: 8 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2
Memory in use: 8260K Max memory used: 8275K
>Environment:
<machine, os, target, libraries (multiple lines)>
System: FreeBSD envy.opus1.com 2.2.7-RELEASE FreeBSD 2.2.7-RELEASE #0: Tue Sep 22 11:25:23
MST 1998 tim@stripped:/usr/src/sys/compile/ENVY i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from
/usr/local/egcs/lib/gcc-lib/i386-unknown-freebsd2.2.7/egcs-2.91.57/specs
gcc version egcs-2.91.57 19980901 (egcs-1.1 release)
Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='-felide-constructors
-fno-exceptions -fno-rtti' LDFLAGS=''
Configure command: ./configure --prefix=/usr/local/mysql
--with-unix-socket-path=/usr/local/mysql/var/mysql.sock --without-readline --with-debug
--with-named-thread-libs=-lc_r --without-docs --with-mysqld-user=mysql
Perl: This is perl, version 5.005_02 built for i386-freebsd
