MySQL Bugs: #31186: Update on Varchar treated as Double (improper type conversions)

Bug #31186	Update on Varchar treated as Double (improper type conversions)
Submitted:	25 Sep 2007 14:16	Modified:	25 Oct 2007 16:09
Reporter:	Jon Dowell	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.37-community-nt	OS:	Windows (Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	dataloss, typeconversion

Description:
In both Update and Delete statements, varchar fields are being improperly type converted to Double.

in the Update, a SET site_id = 'chop:' + site_id  converted 'chop:' to a double value, threw an error, and assigned '0' to site_id.

in the Select, a WHERE site_id = 0 should fail; instead, it appears to treat that as equivalent to WHERE site_id IS NOT NULL and returns all rows.

Both of these appear to be type conversion errors, and rather serious ones to boot! 

This could easily result in a data loss, since the update would erase the value of the site_id rather than appending it.

How to repeat:
With a table event_data
containing a site_id column varchar(35) (not the primary key)
and a column date datetime

The query:

UPDATE event_data
SET site_id = 'chop:' + site_id
WHERE site_id LIKE 'test:%'
  AND date > '2007-08-28 7:00:00'

Resulted in numerous messages:
Truncated incorrect DOUBLE value: 'chop:'
[Note that the number of messages was not the same as the rows affected!]

And all rows had site_id set to '0'

Also, a query:
SELECT *
FROM event_data
WHERE site_id = 0

Returns all rows, rather than an error (0 is not a string, and should not compare to one EVER.)

Suggested fix:
Prevent spurious type conversions from varchar to double.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45, and, in case of the same problem, send the results of:

SHOW CREATE TABLE event_data;
SHOW VARIABLES LIKE 'sql_m%';

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".