List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:May 27 1999 4:00pm
Subject:Re: Bug with NATURAL LEFT OUTER JOIN and indices?
View as plain text  
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
Thread
Bug with NATURAL LEFT OUTER JOIN and indices?Alexander Kourakos27 May
  • Re: Bug with NATURAL LEFT OUTER JOIN and indices?Jim Faucette27 May
  • Bug with NATURAL LEFT OUTER JOIN and indices?Michael Widenius2 Jun
  • Bug with NATURAL LEFT OUTER JOIN and indices?Michael Widenius3 Jun