0

i faced a unique problem by accident

But before that i want to show you a table structure

td_category

|---------------------------------------------------------------------|
|  category_id |  category_title  |  category_slug  |  p_cid          |
|---------------------------------------------------------------------|
|      1       |   Shirts         |   1-Shirts      |     0           |
|---------------------------------------------------------------------|
|      2       |   Jeans          |   2-Jeans       |     0           |
|---------------------------------------------------------------------|

Now, 

category_id is INT and auto-increment value
category_title is VARCHAR
category_slug is VARCHAR

Now what i amdoing is that, by mistake i wrote a query

SELECT * FROM td_category WHERE category_id = '2-Jeans'

and instead of giving me any error it displayed the 2nd tuple

Isn't it supposed to throw an error??

please can anybody clarify?

Saswat
  • 11,464
  • 16
  • 68
  • 141

2 Answers2

1

mysql performs implicit conversion for int datatype due to which '2-Jeans' is treated as 2-0 (since Jeans is not an int type and is defaulted to 0 for compatibility as described in the docs here)

Hence the final query as the parser interprets is as below:

SELECT * FROM td_category WHERE category_id = 2;
Gopal Joshi
  • 2,299
  • 20
  • 49
A Null Pointer
  • 2,161
  • 3
  • 23
  • 28
0

The following query will take id as 2 which is your first character and display second record

SELECT * FROM td_category WHERE category_id = '2-Jeans'

Try this query which will return first record

SELECT * FROM td_category WHERE category_id = '1-Jeans'

2-jeans is treated as 2 so return second record and 1-jeans is treated as 1 so return first record.

Check Manual for auto casting in mysql.

Sadikhasan
  • 17,858
  • 20
  • 77
  • 117
  • Check this post may helpful http://stackoverflow.com/questions/21762075/mysql-automatically-cast-convert-a-string-to-a-number – Sadikhasan Jul 24 '14 at 09:10