List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:April 8 1999 8:37pm
Subject:select text-field ... left join ... order by
View as plain text  
>Description:

A query like this will make mysqld sit forever with a state of
"Creating tmp table" (as shown by mysqladmin processlist):

    SELECT      a.field, b.text_field
    FROM        a
    LEFT JOIN   b USING (some_field)
    ORDER BY    some_field

If you do a normal join (WHERE a.some_field = b.some_field), it
will work fine.  If you change b.text_field from type 'text' to
type 'varchar(N)', it will work fine.  If you leave out the ORDER
BY, it will work fine.

>How-To-Repeat:
# MySQL dump 5.13
#
# Host: localhost    Database: thimble
#--------------------------------------------------------
# Server version        3.22.20a

#
# Table structure for table 'ids'
#
CREATE TABLE ids (
  table_name varchar(64) DEFAULT '' NOT NULL,
  id int(10) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (table_name,id)
);

#
# Dumping data for table 'ids'
#

INSERT INTO ids VALUES ('issues',2);
INSERT INTO ids VALUES ('cols',7);

#
# Table structure for table 'info'
#
CREATE TABLE info (
  table_name varchar(64) DEFAULT '' NOT NULL,
  text_field varchar(64) DEFAULT '' NOT NULL,
  PRIMARY KEY (table_name)
);

#
# Dumping data for table 'info'
#

INSERT INTO info VALUES ('issues','issue_startdate');
INSERT INTO info VALUES ('cols','column_basename');


SELECT
    id, text_field
FROM
    ids
LEFT JOIN
    info ON (ids.table_name = info.table_name)
ORDER BY
    info.table_name;


ALTER TABLE info MODIFY text_field TEXT NOT NULL;


SELECT
    id, text_field
FROM
    ids
LEFT JOIN
    info ON (ids.table_name = info.table_name)
ORDER BY
    info.table_name;

# it should hang here...

>Fix:

Use VARCHAR field, if possible.

>Submitter-Id:
>Originator:    
>Organization:
>MySQL support: licence
>Synopsis: mysqld hangs creating tmp table when left join + text + order by
>Severity:      non-critical
>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
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /usr/local/mysql/var/mysql.sock
Uptime:                 2 min 56 sec

Threads: 2  Questions: 47  Slow queries: 0  Opens: 12  Flush tables: 1  Open tab
les: 5
>Environment:
        <machine, os, target, libraries (multiple lines)>
System: FreeBSD envy.opus1.com 2.2.7-RELEASE FreeBSD 2.2.7-RELEASE #0: Tue Sep 2
2 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/eg
cs-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-pat
h=/usr/local/mysql/var/mysql.sock --without-readline --with-named-thread-libs=-l
c_r --without-docs --with-mysqld-user=mysql
Perl: This is perl, version 5.005_02 built for i386-freebsd

Thread
select text-field ... left join ... order byThimble Smith9 Apr
  • Re: select text-field ... left join ... order byThimble Smith9 Apr