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:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1 and 5.x OS:Any (all)
Assigned to: CPU Architecture:Any

[23 Jun 2005 6:35] Timour Katchaounov
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.
[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.