| Bug #42609 | Column Matches using =, incorrectly, on select and updates. | ||
|---|---|---|---|
| Submitted: | 4 Feb 2009 22:10 | Modified: | 4 Feb 2009 23:24 |
| Reporter: | Charlie Farrow | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.27 | OS: | Linux (Fedora Core 6) |
| Assigned to: | CPU Architecture: | Any | |
[4 Feb 2009 22:24]
Charlie Farrow
Changed title to something better. You should also note that the result is of course different if no quotes are used in the query, as this generates an error (id=a1) correctly.
[4 Feb 2009 23:24]
MySQL Verification Team
Thank you for the bug report. Please read the below Manual's chapter for explanation why the behavior you noticed: http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html Thanks in advance.
Description: I tried to find something on this bug but wasn't able to. Its so simple, but could be really bad and cause severe data loss. It comes because mySQL trunicates a value it is expecting and turns it into something its not, causing updates to happen when they shouldn't. It is also repeatable in a straight select statement. How to repeat: Create a simple table: create table test (id UNSIGNED NOT NULL AUTO_INCREMENT, col1 VARCHAR(45), col2 VARCHAR(45)) ENGINE=InnoDB; Put a few rows in it: insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); insert into test (col1, col2) values ('a','b'); Table now like: +----+------+------+ | id | col1 | col2 | +----+------+------+ | 0 | a | b | | 1 | a | b | | 2 | a | b | | 3 | a | b | | 4 | a | b | | 5 | a | b | | 6 | a | b | | 7 | a | b | +----+------+------+ Now issue: update test set col1='z' where id='a1'; Query OK, 1 row affected, 8 warnings (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Which is incorrect; no warning and certainly no id that has anything other than a integer in it. It gets worse, if you had a table with many rows and you were not updating on the primary key, it would be possible to update even millions of rows with no real match. Suggested fix: = should be an equals, it really shouldn't trunicate without a warning!