>>>>> "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)
