List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 2 1999 9:37pm
Subject:Bug with NATURAL LEFT OUTER JOIN and indices?
View as plain text  
>>>>> "Alexander" == Alexander Kourakos <Alexander@stripped>
> writes:

>> Description:
Alexander>   I've not been on the mysql mailing list for a while, so apologies if this
Alexander> is a known issue. I just installed the latest version and noticed this
Alexander> problem.

Alexander>   I have these two tables, let's say:

Alexander> CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);   
Alexander> INSERT INTO t1 VALUES (1,'a',1);
Alexander> INSERT INTO t1 VALUES (2,'b',1);
Alexander> INSERT INTO t1 VALUES (3,'c',2);

Alexander> CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));   
Alexander> INSERT INTO t2 VALUES (1,'x');
Alexander> INSERT INTO t2 VALUES (2,'y');
Alexander> INSERT INTO t2 VALUES (3,'z');

Alexander>   I want to find rows in t2 which are not referenced in t1 (id2=3 in this
Alexander> example), so I run:

Alexander> SELECT t2.id2
Alexander>  FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2
Alexander>  WHERE id1 IS NULL;

Alexander> or, something I believe will give the same result,

Alexander> SELECT t2.id2
Alexander>  FROM t2 NATURAL LEFT OUTER JOIN t1
Alexander>  WHERE id1 IS NULL;

Alexander> These two queries give different results! BUT if I delete the index on
Alexander> t1.id1, they work the same. Have I missed something in my understanding?

Alexander> Thank you!

Hi!

Thank for the clear bug report!

The problem was that the NATURAL JOIN code didn't notice that you was
indirectly using columns that wasn't indexed.

The included patch fixes this problem!

Regards,
Monty

*** /my/monty/master/mysql-3.22.21/sql/sql_base.cc	Sun Apr  4 01:11:53 1999
--- ./sql_base.cc	Thu Jun  3 00:31:53 1999
***************
*** 1292,1300 ****
        uint i,j;
        for (i=0 ; i < t1->fields ; i++)
        {
  	for (j=0 ; j < t2->fields ; j++)
  	{
! 	  if (!my_strcasecmp(t1->field[i]->field_name,t2->field[j]->field_name))
  	  {
  	    Item_func_eq *tmp=new Item_func_eq(new Item_field(t1->field[i]),
  					       new Item_field(t2->field[j]));
--- 1311,1321 ----
        uint i,j;
        for (i=0 ; i < t1->fields ; i++)
        {
+ 	// This could be optimized to use hashed names if t2 had a hash
  	for (j=0 ; j < t2->fields ; j++)
  	{
! 	  if (!my_strcasecmp(t1->field[i]->field_name,
! 			     t2->field[j]->field_name))
  	  {
  	    Item_func_eq *tmp=new Item_func_eq(new Item_field(t1->field[i]),
  					       new Item_field(t2->field[j]));
***************
*** 1302,1307 ****
--- 1323,1330 ----
  	    {
  	      tmp->fix_length_and_dec();	// Update cmp_type
  	      cond_and->list.push_back(tmp);
+ 	      t1->used_keys&= t1->field[i]->part_of_key;
+ 	      t2->used_keys&= t2->field[j]->part_of_key;
  	    }
  	    break;
  	  }

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