Bug #25574 Warning on type conversion
Submitted: 12 Jan 2007 10:18 Modified: 3 Aug 2010 14:13
Reporter: Yahoo Serious (Silver Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.27, 4.1.21 OS:Windows (Windows 2000)
Assigned to: CPU Architecture:Any
Tags: qc

[12 Jan 2007 10:18] Yahoo Serious
Description:
I would like to get a warning on a 'loose' type conversion when comparing.  Especially when comparing a string to a number.  It took us some time to figure out that 1 would equal the strings "1a", "1,2" and "1|2".  (We figured the integer would be converted to a string, not vice versa.)

This is documented behavior:
  http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html
And can be prevented using BINARY:
  http://dev.mysql.com/doc/refman/5.1/en/charset-binary-op.html

However I think a default warning would be a nice service to the users, with possibly a parameter to suppress it.

How to repeat:
# Create table
DROP TABLE IF EXISTS test.t3;
CREATE TABLE  test.t3 (
  Id int(11) NOT NULL auto_increment,
  SomeString char(20) collate latin1_bin NOT NULL default '""',
  PRIMARY KEY  (Id),
  KEY SomeString (SomeString)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

# Create contents
INSERT INTO test.t3 (SomeString) VALUES ('1'), ('2'), ('1.2'), ('1,2'), ('1a');

# Compare integers to strings
SELECT *
FROM test.t3 AS t1, test.t3 AS t2
WHERE t1.Id = t2.SomeString       # should use BINARY here!
ORDER BY t1.Id;

# Note: 1 equals '1a' and '1,2'

Suggested fix:
Suggested warning: 
Warning: during comparison column t1.Id was converted from data type `int(11)' to `FLOAT'.
Warning: during comparison column t2.SomeString was converted from data type `char(20)' to `FLOAT' (ignoring everything past the first non-numeric character [^0-9e.-]).
[20 Feb 2007 10:42] Valeriy Kravchuk
Thank you for a reasonable feature request.
[3 Aug 2010 14:13] Evgeny Potemkin
Duplicate of the bug#53562.