Description:
When:
- the optimizer performs range analysis, and
- the range condition compares an unsigned field with
a signed negative constant, and
- the field in the condition is of any integer type except
BIGINT,
the range optimizer incorrectly replaces the negative
constant with '0'. This causes incorrect query results
where (0 = any_negative_number).
How to repeat:
-- Notice that the same result is achived with int, and all other integer
-- types except BIGINT.
create table t2 (a tinyint unsigned);
create index t2i on t2(a);
insert into t2 values (0), (254), (255);
explain select * from t2 where a > -1;
+------+
| a |
+------+
| 254 |
| 255 |
+------+
2 rows in set (0.00 sec)
Notice: '0' is missing from the result, thus here (0 = -1) which is wrong.
Suggested fix:
The cause of the bug is in opt_range.cc, get_mm_leaf(). The problem is
in the call:
if (value->save_in_field(field, 1) < 0)
and then the subsequent call to:
field->get_key_image(str+maybe_null, key_part->length,
field->charset(), key_part->image_type);
If 'field' is of type unsigned int, and 'value' is a negative number,
save_in_field() for all integer types but longlong substitutes the negative
number for '0'. Then get_key_image stores '0' as the value of the key.
As a result comparisons of '0' values to the key are incorrect because from
this point on every negative key is replaced by '0'.
My idea to fix the problem is to find some other way of extracting the
key value from the 'value' Item, than using the 'field' object itself.
Description: When: - the optimizer performs range analysis, and - the range condition compares an unsigned field with a signed negative constant, and - the field in the condition is of any integer type except BIGINT, the range optimizer incorrectly replaces the negative constant with '0'. This causes incorrect query results where (0 = any_negative_number). How to repeat: -- Notice that the same result is achived with int, and all other integer -- types except BIGINT. create table t2 (a tinyint unsigned); create index t2i on t2(a); insert into t2 values (0), (254), (255); explain select * from t2 where a > -1; +------+ | a | +------+ | 254 | | 255 | +------+ 2 rows in set (0.00 sec) Notice: '0' is missing from the result, thus here (0 = -1) which is wrong. Suggested fix: The cause of the bug is in opt_range.cc, get_mm_leaf(). The problem is in the call: if (value->save_in_field(field, 1) < 0) and then the subsequent call to: field->get_key_image(str+maybe_null, key_part->length, field->charset(), key_part->image_type); If 'field' is of type unsigned int, and 'value' is a negative number, save_in_field() for all integer types but longlong substitutes the negative number for '0'. Then get_key_image stores '0' as the value of the key. As a result comparisons of '0' values to the key are incorrect because from this point on every negative key is replaced by '0'. My idea to fix the problem is to find some other way of extracting the key value from the 'value' Item, than using the 'field' object itself.