MySQL Bugs: #41947: select * from [table] where table.id='1'

Bug #41947	select * from [table] where table.id='1'
Submitted:	8 Jan 2009 8:00	Modified:	3 Jan 2011 10:46
Reporter:	Lahiru Thiwanka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
select * from translations where `id` = '1'

//

select * from translations where `id` = '1sdfsd'

executing same result 

select * from translations where `id` like '1'
Result is return

select * from translations where `id` like '1sdf'
Result is NOT return

How to repeat:
select * from translations where `id` = '1'

//

select * from translations where `id` = '1sdfsd'

executing same result 

select * from translations where `id` like '1'
Result is return

select * from translations where `id` like '1sdf'
Result is NOT return

Suggested fix:
select * from translations where `id` = '1'

//

select * from translations where `id` = '1sdfsd'

executing same result 

select * from translations where `id` like '1'
Result is return

select * from translations where `id` like '1sdf'
Result is NOT return

please fix this error

Many thanks for writing a bug report.

Unfortunately, I can't follow your thoughts.

Please provide CREATE TABLE syntax and INSERT statements that you made before testing the select.

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".

The simpler way to test is the folowing links that will retun the same page:

http://bugs.mysql.com/bug.php?id=41947
OR
http://bugs.mysql.com/bug.php?id=41947asdf
OR
http://bugs.mysql.com/bug.php?id=41947asdf123

Why do you think there is any bug here? If you compare string literal to numeric column, string is read until the first non-numeric character. This is documented and had always been the case.

If your test case is different, please, provide it.

but what is the different between thees queries.

select * from translations where `id` = '1'
//
select * from translations where `id` = '1sdfsd'

executing same result

Here is what we get:

mysql> select 1 = '1';
+---------+
| 1 = '1' |
+---------+
|       1 |
+---------+
1 row in set (0.38 sec)

mysql> select 1 = '1abcd';
+-------------+
| 1 = '1abcd' |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '1abcd'
1 row in set (0.00 sec)

mysql> select 1 like '1';
+------------+
| 1 like '1' |
+------------+
|          1 |
+------------+
1 row in set (0.36 sec)

mysql> select 1 like '1abcd';
+----------------+
| 1 like '1abcd' |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

In case of = comparing number to string we compare numbers, and string is converted into number. In case of LIKE we can compare only strings, and number is converted into string. Thus different results.

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