Bug #45424 UPDATE statement cause full table scan ?
Submitted: 10 Jun 2009 6:58 Modified: 10 Jun 2009 7:59
Reporter: Lance Li Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.51a, 5.0.77 and 5.1.34, 5.1, 6.0 bzr OS:Linux
Assigned to: CPU Architecture:Any
Tags: full table scan, performance, regression, UPDATE

[10 Jun 2009 6:58] Lance Li
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 ?
[10 Jun 2009 7:26] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW STATUS LIKE 'Handler%'; before and after problem UPDATE statement.
[10 Jun 2009 7:40] Lance Li
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)
[10 Jun 2009 7:59] Sveta Smirnova
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.
[10 Jun 2009 8:02] Sveta Smirnova
test case

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