List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 3 2000 11:38pm
Subject:Re: Seems to be a bug when doing an IS NULL test with a LEFT JOIN when an index is involved.
View as plain text  
>>>>> "Matt" == Matt Hoskins <matt@stripped> writes:

Matt> On Mon, 3 Jan 2000, Michael Widenius wrote:

mysql> select * from test t1 LEFT JOIN test t2 
Matt> ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
Matt> +------+------+---------+------+------+---------+
Matt> | id   | pid  | rep_del | id   | pid  | rep_del |
Matt> +------+------+---------+------+------+---------+
Matt> |    2 |    1 |    NULL | NULL | NULL |    NULL |
Matt> +------+------+---------+------+------+---------+
Matt> 1 row in set (0.01 sec)
>> 
>> Could you please send me a mysqldump of both tables so that I can
>> check this on my side?

Matt> D'oh, I forgot to say what was in the table. Actually there are only two
Matt> entries in the test table ;). I've included two dumps, where the only
Matt> difference is an index on the rep_del column existing in one, and not in
Matt> the other.

Matt> Matt

Matt> CREATE TABLE test (
Matt>   id int(11),
Matt>   pid int(11),
Matt>   rep_del tinyint(4),
Matt>   KEY id (id),
Matt>   KEY pid (pid),
Matt>   KEY rep_del (rep_del)
Matt> );
Matt> INSERT INTO test VALUES (1,NULL,NULL);
Matt> INSERT INTO test VALUES (2,1,NULL);

Matt> CREATE TABLE test (
Matt>   id int(11),
Matt>   pid int(11),
Matt>   rep_del tinyint(4),
Matt>   KEY id (id),
Matt>   KEY pid (pid)
Matt> );
Matt> INSERT INTO test VALUES (1,NULL,NULL);
Matt> INSERT INTO test VALUES (2,1,NULL);

Thanks.  This was a bug in the relative new LEFT JOIN optimization
that didn't take into account that a key_column could have NULL
values.

Here is a patch for this:

*** /my/monty/master/mysql-3.23.8-alpha/sql/sql_select.cc	Tue Dec 28 05:41:30 1999
--- ./sql_select.cc	Tue Jan  4 01:34:11 2000
***************
*** 1221,1227 ****
      }
    }
    /* Mark that we can optimize LEFT JOIN */
!   if (key_field->val->type() == Item::NULL_ITEM)
      key_field->field->table->reginfo.not_exists_optimize=1;
  }
  
--- 1221,1228 ----
      }
    }
    /* Mark that we can optimize LEFT JOIN */
!   if (key_field->val->type() == Item::NULL_ITEM &&
!       !key_field->field->real_maybe_null())
      key_field->field->table->reginfo.not_exists_optimize=1;
  }
  


Regards,
Monty
Thread
Seems to be a bug when doing an IS NULL test with a LEFT JOIN when an index is involved.Matt Hoskins2 Jan
Re: Seems to be a bug when doing an IS NULL test with a LEFT JOIN when an index is involved.Michael Widenius4 Jan
  • anyone know what is the --with-raid compilation optionClickAgents4 Jan
    • Re: anyone know what is the --with-raid compilation optionBenjamin Pflugmann4 Jan
    • Re: anyone know what is the --with-raid compilation optionTonu Samuel4 Jan