Bug #45424 UPDATE statement cause full table scan ?

Description:
I have a table having about 10M records, and using int unsigned `id` as primary key. 

When I try to UPDATE a record by using `id` and providing an out-of-ranged but looks-like-number value, the sql statement will run very long time and it looks like causing a full table scan, which will be a really serious performance issue, and from the DB server side, I cannot find any workaround.

This dose not affect SELECT stat.

I have both MyISAM and InnoDB version of this table and the problem is totally same.

I have reproduced this bug on 5.0.51a, 5.0.77 and 5.1.34 .

I'm not sure this is a parser or optimizer bug, and just randomly choose one as the bug category.

How to repeat:
The example presented here is not the same one above, but I think is necessary for demonstrate the bug.

the table:

mysql> show create table past30;                       
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                           |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| past30 | CREATE TABLE `past30` (
  `vid` int(10) unsigned NOT NULL,
  `date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `count` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`vid`,`date`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show table status like "past30"\G
*************************** 1. row ***************************
           Name: past30
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 34197504
 Avg_row_length: 59
    Data_length: 2044723200
Max_data_length: 0
   Index_length: 562036736
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-06-08 14:46:12
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 5120 kB
1 row in set (0.12 sec)

the problem:

mysql> update past30 set count=100 where vid=-1;   
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update past30 set count=100 where vid='-1';
Query OK, 0 rows affected (2 min 9.28 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update past30 set count=100 where vid='-1'+0;
Query OK, 0 rows affected (0.00 sec)

mysql> update past30 set count=100 where vid='aaaabbbb';                  
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

and for SELECT:

mysql> select * from past30 where vid=-1;        
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'vid' at row 1 | 
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from past30 where vid='-1';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;                      
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'vid' at row 1 | 
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Using the same checking mechanism for SELECT ?

Thank you for the report.

Please provide output of SHOW STATUS LIKE 'Handler%'; before and after problem UPDATE statement.

here it is.

mysql> SHOW STATUS LIKE 'Handler%'; update past30 set count=100 where vid='-1';  SHOW STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 0      | 
| Handler_delete             | 0      | 
| Handler_discover           | 0      | 
| Handler_prepare            | 0      | 
| Handler_read_first         | 1      | 
| Handler_read_key           | 2      | 
| Handler_read_next          | 0      | 
| Handler_read_prev          | 0      | 
| Handler_read_rnd           | 0      | 
| Handler_read_rnd_next      | 184780 | 
| Handler_rollback           | 1      | 
| Handler_savepoint          | 0      | 
| Handler_savepoint_rollback | 0      | 
| Handler_update             | 0      | 
| Handler_write              | 29     | 
+----------------------------+--------+
15 rows in set (0.00 sec)

Query OK, 0 rows affected (2 min 4.60 sec)
Rows matched: 0  Changed: 0  Warnings: 0

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_commit             | 2        | 
| Handler_delete             | 0        | 
| Handler_discover           | 0        | 
| Handler_prepare            | 2        | 
| Handler_read_first         | 2        | 
| Handler_read_key           | 4        | 
| Handler_read_next          | 0        | 
| Handler_read_prev          | 0        | 
| Handler_read_rnd           | 0        | 
| Handler_read_rnd_next      | 34984419 | 
| Handler_rollback           | 1        | 
| Handler_savepoint          | 0        | 
| Handler_savepoint_rollback | 0        | 
| Handler_update             | 0        | 
| Handler_write              | 44       | 
+----------------------------+----------+
15 rows in set (0.00 sec)

Thank you for the report.

Verified as described using version 5.1 and 6.0.

Version 5.0 uses table scan for both SELECT and UPDATE statements. Version 4.1 uses index.

test case

Attachment: bug45424.test (application/octet-stream, text), 880 bytes.