Not using key on a LEFT JOIN if types doesn"t match
am 25.09.2002 13:45:13 von mlo>Description:
When trying to LEFT JOIN two tables, and the columens matched
in the ON-clause has diffrent types, it seems like MySQL can
see a possible_keys, but doesn't use it. It does how ever use
it if the field is included in the SELECT-clause.
>How-To-Repeat:
Just run "mysql test" and feed it with the lines below and the
first "EXPLAIN"-stetments will show that MySQL doesn't use a
key to do the LEFT JOIN. The other two does, one because the
the field refered is included in the SELECT-clouse, the other
because the ALTER TABLE fixes the fact that the field-types is
diffrent.
CREATE TABLE A (
prim int(10) unsigned NOT NULL auto_increment,
ref bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (prim)
) TYPE=MyISAM;
INSERT INTO A VALUES (1,1);
INSERT INTO A VALUES (2,3);
CREATE TABLE B (
prim varchar(10) NOT NULL default '',
name varchar(70) NOT NULL default '',
PRIMARY KEY (prim)
) TYPE=MyISAM;
INSERT INTO B VALUES ('1','Someone');
INSERT INTO B VALUES ('3','Someoneelse');
-- PROBLEM:
EXPLAIN
SELECT A.prim , B.name
FROM A
LEFT JOIN B ON (A.ref = B.prim);
-- WORK-AROUND-1:
EXPLAIN
SELECT A.prim , B.prim
FROM A
LEFT JOIN B ON (A.ref = B.prim);
-- WORK-AROUND-2:
ALTER TABLE B MODIFY prim bigint(20) unsigned NOT NULL default '0';
EXPLAIN
SELECT A.prim , B.name
FROM A
LEFT JOIN B ON (A.ref = B.prim);
DROP TABLE A;
DROP TABLE B;
>Fix:
Work-a-round included abover. Possible doint a FLOOR or CONCAT would also help.
>Submitter-Id:
>Originator:
>Organization:
>MySQL support: none
>Synopsis: LEFT JOIN not using a key when types doesn't match
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.52 (Official MySQL RPM)
>Environment:
Tested on a Redhat Linux server...
System: Linux raman.uni2.net 2.4.9-31smp #1 SMP Tue Feb 26 06:55:00 EST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.2 2.96-108.7.2)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Sep 4 11:19 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 1285884 Aug 7 14:13 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27336078 Aug 7 12:56 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Aug 7 12:57 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/info --includedir=/usr/include --mandir=/usr/share/man '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc
------------------------------------------------------------ ---------
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-thread12587@lists.mysql.com
To unsubscribe, e-mail