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
  • 12,320
  • 16
  • 77
  • 156

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,350
  • 22
  • 49
A Null Pointer
  • 2,261
  • 3
  • 26
  • 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
  • 18,365
  • 21
  • 80
  • 122
  • 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