MySQL Bugs: #42583: From with SELECT statement when non integer type is used

Bug #42583	From with SELECT statement when non integer type is used
Submitted:	4 Feb 2009 8:32	Modified:	4 Feb 2009 8:50
Reporter:	Umakant Patil	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (XP Version 2 Service Pack 3)
Assigned to:		CPU Architecture:	Any
Tags:	error, INTEGER, Primay key, SELECT

Description:
Create some table having innodb engine with 3 or more fields. First field should be Primary Key, Auto_increment, BIGINT(20).  Sample is given below

RollNo   Name       City
1        Umakant    XYZ
2        Patil      PQR
3        Problem    TEST

After adding values use below SELECT Statement.
SELECT * FROM <table-name> WHERE RollNo='2wer';

You can see MySQL Query Browser will show error but at same time it will fetch second row also. Same happens using mysql_query() function in PHP. What i think is it should not return result set as there is no such matching value in roll no.

How to repeat:
Create some table having innodb engine with 3 or more fields. First field should be Primary Key, Auto_increment, BIGINT(20).  Sample is given below

RollNo   Name       City
1        Umakant    XYZ
2        Patil      PQR
3        Problem    TEST

After adding values use below SELECT Statement.
SELECT * FROM <table-name> WHERE RollNo='2wer';

You can see MySQL Query Browser will show error but at same time it will fetch second row also. Same happens using mysql_query() function in PHP. What i think is it should not return result set as there is no such matching value in roll no.

Suggested fix:
I dont how to fix it.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

MySQL converts '2wer' to 2 if string is used in the numeric context. So returning the row is expected. Also you should get warning "Truncated incorrect DOUBLE value: '2wer' " So this is not MySQL bug.