| Bug #11521 | Negative integer keys incorrectly substituted for 0 during range analysis. | ||
|---|---|---|---|
| Submitted: | 23 Jun 2005 6:35 | Modified: | 15 Dec 2009 16:57 |
| Reporter: | Timour Katchaounov | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 4.1 and 5.x | OS: | Any (all) |
| Assigned to: | CPU Architecture: | Any | |
[15 Dec 2009 16:57]
Valeriy Kravchuk
I can not repeat this with current 5.0 from bzr: 77-52-7-73:5.0 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.89-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table t2; Query OK, 0 rows affected (0.00 sec) mysql> create table t2 (a tinyint unsigned); Query OK, 0 rows affected (0.00 sec) mysql> create index t2i on t2(a); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t2 values (0), (254), (255); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> explain select * from t2 where a > -1; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t2 | index | t2i | t2i | 2 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.06 sec) mysql> select * from t2 where a > -1; +------+ | a | +------+ | 0 | | 254 | | 255 | +------+ 3 rows in set (0.00 sec) So, the bug was fixed somehow already.
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.