List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 21 1999 9:53pm
Subject:Bug report - 3.23.3 and indexes
View as plain text  
>>>>> "Richard" == Richard Ellerbrock <richarde@stripped> writes:

Richard> If I index on a varchar field and then I do a select as follows:
Richard> select * from xxx where xxx like 'abc%';

Richard> No records are returned. If I remove the index from field xxx, all works just
> fine. This does not matter if the field is defined as NOT NULL.

Richard> If I use regex as in

Richard> select * from xxx where xxx rlike 'abc';

Richard> It works with or without the index. The table type is myisam. I cannot change
> it to isam as there are not null issues.

Richard> --
Richard> Richard Ellerbrock
Richard> richarde@stripped

Hi!

Here is a patch that fixes this.

*** /my/monty/master/mysql-3.23.3-alpha/sql/opt_range.cc	Mon Aug 30 00:03:06 1999
--- ./opt_range.cc	Wed Sep 22 00:46:09 1999
***************
*** 908,914 ****
        DBUG_RETURN(0);				// Can only optimize strings
  
      offset=maybe_null;
!     length=key_part->part_length+offset;
      if (field->type() == FIELD_TYPE_BLOB)
      {
        offset+=HA_KEY_BLOB_LENGTH;
--- 908,914 ----
        DBUG_RETURN(0);				// Can only optimize strings
  
      offset=maybe_null;
!     length=key_part->part_length;
      if (field->type() == FIELD_TYPE_BLOB)
      {
        offset+=HA_KEY_BLOB_LENGTH;
***************
*** 921,926 ****
--- 921,927 ----
        else
  	field_length=length;
      }
+     length+=offset;
      if (!(min_str=sql_alloc(length*2)))
        DBUG_RETURN(0);
      max_str=min_str+length;
***************
*** 958,964 ****
--- 959,968 ----
      if (!tree)
        DBUG_RETURN(0);
      if (type == Item_func::ISNOTNULL_FUNC)
+     {
        tree->min_flag=NEAR_MIN;		    /* IS NOT NULL ->  X > NULL */
+       tree->max_flag=NO_MAX_RANGE;
+     }
      DBUG_RETURN(tree);
    }
  
Regards,
Monty

PS: I will try to get 3.23.4 out tomorrow (which will include this
    patch)

Thread
Bug report - 3.23.3 and indexesRichard Ellerbrock21 Sep
  • Bug report - 3.23.3 and indexesMichael Widenius22 Sep