A bug in integer judgment

Original article: http://dinglin.iteye.com/blog/1317874

Problem:

This bug comes from an official bug report. Thanks to @ Yin Feng _ xiao xi. The phenomenon is very easy to describe. This problem exists in Versions later than 5.1.

Create Table 'tb '( 'A' int (11) default null, 'B' int (11) default null, Key 'A' ('A ') ) Engine = InnoDB default charset = utf8;

Insert into TB values (1, 2), (2, 5), (3, 8), (4, 6 );

Select * from TB Force Index (a) Where a >= 0.5; + ------ + | A | B | + ------ + | 2 | 5 | | 3 | 8 | | 4 | 6 | + ------ + 4 rows in SET (0.00 Sec)

(1, 2) This record does not return.

Note: In the SELECT statement, force index is used to prevent full table scanning. (it will be normal without going to index ).

Cause analysis:

When MySQL uses indexes, it calls the index_read interface of InnoDB and needs to input three pieces of information: query value \ Index \ QUERY direction. The query condition is> =, therefore, ha_read_after_key and ha_read_key_or_next are available in the query direction, corresponding to> and> =.

However, query optimization is required before input.

The process in this example:

1) After MySQL decides to use index A, index [1, max) is obtained based on the input value (0.5). The reason for obtaining 1 is that A is of the int type .;

2) determine the size of 0.5 and 1, 0.5 <1. Therefore, set tree-> min_flag = near_min; to indicate the value to be searched, which is smaller than the minimum value of the input index range. therefore, we decided to use ha_read_after_key, that is,> 0.5, which is OK.

3) Unfortunately, because field A is an integer, the actual input is 1, and the logic is> 1.

 

Therefore, the record a = 1 is ignored during search.

 

Simple modification:

Version 5.0 does not have this problem. At that time, there were no such things as ha_read_after_key and ha_read_key_or_next.

The difference between the two values is that when InnoDB searches internally, do you need to determine the value that is equal? A simple modification can be processed as ha_read_key_or_next. you don't have to worry about the consequences of turning MySQL into> =. The MySQL layer will filter again.

 

Of course, the above is a lazy approach. The more formal approach should be to set 0.5 to 1 when judging the size, so that 1 = 1 will be marked as ha_read_key_or_next.