MySQL Bugs: #67427: Conversion from INT to STRING is causing a like comparison where an equals was u

Bug #67427	Conversion from INT to STRING is causing a like comparison where an equals was u
Submitted:	30 Oct 2012 20:20	Modified:	13 Nov 2012 19:15
Reporter:	adam steele	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6	OS:	Any (Conversion issue from INT to STR)
Assigned to:		CPU Architecture:	Any
Tags:	conversion, INTEGER, string

Description:
http://stackoverflow.com/questions/13144113/mysql-query-string-field-with-int-returns-equi...

This was where I posted the question initially if you would like to reference it for any reason.

If you do a query on a text field, with an integer such as:

select * from something WHERE column = 1

it will pull everything as if you typed:

select * from something WHERE column LIKE '1%'

How to repeat:
table_name: 
id - INT 
account - varChar(X)

entries:

ID - account
1 - "1"
2 - "12"
3 - "123"
4 - "1234"
so:

1:

SELECT * FROM table_name WHERE account LIKE '1%'
will return entries 1, 2, 3, 4

2:

SELECT * FROM table_name WHERE account = 1
will return entries 1, 2, 3, 4

3:

SELECT * FROM table_name WHERE account = '1'
will return entry 1

Query 2 should equal query 3, however query 1 equals query 2.

Suggested fix:
Honestly this really depends on the backend.  This started as a mistake on my part however, simply added quotes around it achieved the desired affect.

Not reproducible for me on neither 5.6.7 nor 5.5.28:

DROP TABLE IF EXISTS `bugxx`;

CREATE TABLE `bugxx` (
  `id` int(11) DEFAULT NULL,
  `account` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `bugxx` */

insert  into `bugxx`(`id`,`account`) values (1,'1');
insert  into `bugxx`(`id`,`account`) values (2,'12');

SELECT * FROM bugxx WHERE account = 1
/*returns

    id  account  
------  ---------
     1  1        
*/

-- Peter(not a MySQL/Oracle person)

I couldn't repeat the described problem on 5.5.28 or 5.6.7.
Can you send us the output of this when run at the mysql command line client?

drop table if exists t1;
create table t1(a int,b varchar(10),key(b))engine=myisam;
insert into t1 values (1,'1'),(2,'12'),(3,'123'),(4,'1234');
select * from t1 where b like '1%';
select * from t1 force index(b) where b = 1;
select * from t1 ignore index(b) where b = 1;

alter table t1 engine=innodb;
select * from t1 where b like '1%';
select * from t1 force index(b) where b = 1;
select * from t1 ignore index(b) where b = 1;
select version();

Ok so I was able to repeat this again but under very particular circumstances.

CREATE TABLE `bugs` (
  `id` int(11) NOT NULL,
  `test` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO bugs (test) values ("1234");
INSERT INTO bugs (test) values ("1234-0");
INSERT INTO bugs (test) values ("1234-1");
INSERT INTO bugs (test) values ("1234-2");
INSERT INTO bugs (test) values ("1234-3");
INSERT INTO bugs (test) values ("1234-4");

select * from bugs where test = 1
null

select * from bugs where test = 12
null

select * from bugs where test = 123
null

select * from bugs where test = 1234  //  where test LIKE '1234%'
id  |  test
1      1234
2      1234-0
3      1234-1
4      1234-2
5      1234-3
6      1234-4

select * from bugs where test = '1234'
id  |  test
1      1234

Expected, or at least documented, behavior:

http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly.
[...]
The following rules describe how conversion occurs for comparison operations: 
[...]
* In all other cases, the arguments are compared as floating-point (real) numbers. 

Trailing non-numeric characters are ignored in string to float conversion,
so e.g. 

  3.14 = "3.14 is Pi"

evaluates to true

Thank you for the report.

Hartmut is absolutely correct: this is documented behavior and not a bug.