MySQL Bugs: #34731: highest possible value for INT erroneously filtered by WHERE

Bug #34731	highest possible value for INT erroneously filtered by WHERE
Submitted:	21 Feb 2008 15:30	Modified:	7 Apr 2008 17:30
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.51a, 5.0.54, 5.1.23	OS:	Windows (probably any)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	qc

Description:

select * from ..  where {some INT column} < 2147483648

does not return row with value 2147483647 for that column

How to repeat:
CREATE TABLE `int_simple` (             
              `aa` int(11) NOT NULL,                
              `bb` varchar(20) NOT NULL,            
              PRIMARY KEY  (`aa`)                   
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; 

select * from int_simple;

/*
        aa  bb    
----------  ------
2147483645  x     
2147483647  y 
*/

select * from int_simple where aa < 2147483648; 
-- 2147483647 is highest possible value!

/*
        aa  bb    
----------  ------
2147483645  x     

*/

now .. don't forget to check TINYINT, SMALLINT, BIGINT as well as UNSIGNED option for all!

Thank you for a bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `int_simple` (
    ->               `aa` int(11) NOT NULL,
    ->               `bb` varchar(20) NOT NULL,
    ->               PRIMARY KEY  (`aa`)
    ->             ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Query OK, 0 rows affected (0.36 sec)

mysql> insert into int_simple values(2147483645, 'x');
Query OK, 1 row affected (0.05 sec)

mysql> insert into int_simple values(2147483647, 'y');
Query OK, 1 row affected (0.00 sec)

mysql> select * from int_simple;
+------------+----+
| aa         | bb |
+------------+----+
| 2147483645 | x  |
| 2147483647 | y  |
+------------+----+
2 rows in set (0.03 sec)

mysql> select * from int_simple where aa < 2147483648;
+------------+----+
| aa         | bb |
+------------+----+
| 2147483645 | x  |
+------------+----+
1 row in set (0.02 sec)

.. and also check if there should be similar issues with lower boundaries and "<" operator!

-- finally I was wondering if other datatypes (SET, ENUM) are indirectly affected!

Only occurs when index is used (aa is PRIMARY KEY in example).
Verified as dependent on that condition with MyISAM and InnoDB.

I think it also happens if that column is part of a *compound* PK!
Just for completenes!

#0  handler::compare_key (this=0x8d7e6a8, range=0x8d7e720) at handler.cc:2721
#1  0x0831af7d in handler::read_range_first (this=0x8d7e6a8, start_key=0x0, end_key=0x8d99544, eq_range_arg=false, sorted=false) at handler.cc:2660
#2  0x0831b2c0 in handler::read_multi_range_first (this=0x8d7e6a8, found_range_p=0xb5059f70, ranges=0x8d99538, range_count=1, sorted=false, buffer=0x0) at handler.cc:2527
#3  0x082fe75a in QUICK_RANGE_SELECT::get_next (this=0x8d7eba0) at opt_range.cc:6896
#4  0x08315429 in rr_quick (info=0x8d9eb30) at records.cc:224
#5  0x0828b95f in join_init_read_record (tab=0x8d9eaf0) at sql_select.cc:11310
#6  0x0828b223 in sub_select (join=0x8d9d6e0, join_tab=0x8d9eaf0, end_of_records=false) at sql_select.cc:10669
#7  0x08293280 in do_select (join=0x8d9d6e0, fields=0x8d66220, table=0x0, procedure=0x0) at sql_select.cc:10433
#8  0x082a97ea in JOIN::exec (this=0x8d9d6e0) at sql_select.cc:2118
#9  0x082a5723 in mysql_select (thd=0x8d651e8, rref_pointer_array=0x8d662b0, tables=0x8d9d2d0, wild_num=1, fields=@0x8d66220, conds=0x8d9d578, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153344, result=0x8d9d6c8, unit=0x8d65f54, select_lex=0x8d6618c) at sql_select.cc:2296
#10 0x082a9afe in handle_select (thd=0x8d651e8, lex=0x8d65efc, result=0x8d9d6c8, setup_tables_done_option=0) at sql_select.cc:257
#11 0x0823d809 in mysql_execute_command (thd=0x8d651e8) at sql_parse.cc:2736

SELECT SQL_NO_CACHE * FROM t1 WHERE    indexed < 256;

(gdb) print last_range->flag
$39 = 9  NO_MIN_RNAGE | NEAR_MAX
(gdb) print *start_key
$40 = {key = 0x8d9a540 "/¥¥¥¥¥¥¥ÿ·¥¥¥¥¥¥", length = 0, flag = HA_READ_KEY_OR_NEXT}
(gdb) print *end_key
$41 = {key = 0x8d9a548 "ÿ·¥¥¥¥¥¥", length = 1, flag = HA_READ_BEFORE_KEY}
(gdb) print end_key->key[0]
$43 = -1 'ÿ'

So.
key = 255, and thanks to read-before, key_compare_result_on_equal becomes 1 in read_range_first(). When we compare (255 == 255) in handler::compare_key(), we hence return compare_result_on_equal (== 1 for "row_key > range_key").
We could fudge the special case in the range down here certainly, but much more interestingly, when does 256 become 255, and why isn't it eliminated altogether?

INSERT behaves as expected:

> INSERT INTO t1 VALUES (256,256,'impossible');
Query OK, 1 row affected, 2 warnings

INSERT corrects to MAX-TINY and throws warnings.

range-key (of 256) is also limited to 255, but throws no warning. Since our cond is now "< 255", our "= 255" is ignored.

This produces correct results (key is still corrected to 255, but we use <=), so the "<= 256" cond becomes "<= 255" which selects our "= 255" element:

SELECT SQL_NO_CACHE * FROM t1 WHERE    indexed <= 256;

(gdb) print last_range->flag
$49 = 1  <-- NEAR_MAX not set
(gdb) print *start_key
$50 = {key = 0x8d9a540 "", length = 0, flag = HA_READ_KEY_OR_NEXT}
(gdb) print *end_key
$51 = {key = 0x8d9a548 "ÿ·¥¥¥¥¥¥", length = 1, flag = HA_READ_AFTER_KEY}

Clearing NEAR_MAX manually for the range for "< 256" fixes the behaviour.

int Field_tiny::store(longlong nr, bool unsigned_val) fails with 1 (overflow, obviously)

Hence in get_mm_leaf() @ 4407

  err= value->save_in_field_no_warnings(field, 1);
  if (err > 0 && field->cmp_type() != value->result_type())

err==1, but then since both field and constant are of INT type, we do no error handling and erroneously set some flags. Forcing error handling by using different types like so

SELECT SQL_NO_CACHE * FROM t1 WHERE indexed < 256.0;

results in NEAR_MAX not being set and the query being executed correctly.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43666

ChangeSet@1.2595, 2008-03-10 11:12:12+01:00, tnurnberg@mysql.com +3 -0
  Bug#34731: highest possible value for INT erroneously filtered by WHERE
  
  WHERE f1 < n ignored row if f1 was indexed integer column and
  f1 = TYPE_MAX ^ n = TYPE_MAX+1. The latter value when treated
  as TYPE overflowed (obviously). This was not handled, it is now.

pushed to 5.0.60, 5.1.24-rc in -bugteam

Pushed into 5.0.60

Pushed into 5.1.24-rc

Noted in 5.0.60, 5.1.24 changelogs.

For an indexed integer column col_name and a value N that is one
greater than the maximum value allowed for the data type of col_name,
conditions of the form WHERE col_name < N failed to return rows where
the value of col_name is N - 1. 

Resetting report to Patch queued waiting for push into 6.0.x.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.