>Description:
I've not been on the mysql mailing list for a while, so apologies if this
is a known issue. I just installed the latest version and noticed this
problem.
I have these two tables, let's say:
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
INSERT INTO t1 VALUES (1,'a',1);
INSERT INTO t1 VALUES (2,'b',1);
INSERT INTO t1 VALUES (3,'c',2);
CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
INSERT INTO t2 VALUES (1,'x');
INSERT INTO t2 VALUES (2,'y');
INSERT INTO t2 VALUES (3,'z');
I want to find rows in t2 which are not referenced in t1 (id2=3 in this
example), so I run:
SELECT t2.id2
FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2
WHERE id1 IS NULL;
or, something I believe will give the same result,
SELECT t2.id2
FROM t2 NATURAL LEFT OUTER JOIN t1
WHERE id1 IS NULL;
These two queries give different results! BUT if I delete the index on
t1.id1, they work the same. Have I missed something in my understanding?
Thank you!
=========================================
>Originator: Alexander Kourakos
>MySQL support: none
>Synopsis: Bug with NATURAL LEFT OUTER JOIN and indices.
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.22 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 7.11 Distrib 3.22.22, for ibm-linux-gnu on i486
TCX Datakonsult AB, by Monty
Server version 3.22.22-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/local/mySQL/socket
Uptime: 1 hour 58 min 16 sec
Threads: 1 Questions: 1733 Slow queries: 2 Opens: 147 Flush tables: 1 Open tables: 9
System: Linux oxygene 2.0.36 #1 Sun Nov 22 08:11:42 EST 1998 i486 unknown
Architecture: i486
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-ibm-linux/2.8.1/specs
gcc version 2.8.1
Compilation info: CC='gcc' CFLAGS='-O2 -m486 -s' CXX='g++' CXXFLAGS='-O2 -m486 -s'
LDFLAGS='-s'
Configure command: ./configure --prefix=/usr/local --localstatedir=/var/local/mySQL
--with-unix-socket-path=/var/local/mySQL/socket --enable-thread-safe-client
--enable-assembler --without-debug --without-perl --without-readline --enable-shared
--with-mysqld-user=mysql i486-ibm-linux
Perl: This is perl, version 5.005_03 built for i486-linux
