With the indexes in place does the first return 3 and the 2nd an empty
set? If so, do the queries without the WHERE clauses. I think you'll
find the 2nd is joining ON the primary keys.
jim...
Alexander Kourakos wrote:
>
> >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?
>
> Server version 3.22.22-log
