Wrong result from query

Question

I have this simple query

SELECT * 
FROM  `book` 
WHERE  `BookID` =  '7u'
LIMIT 1

i expect empty result But i see one result with book id =7. BookID is auto increment. Why query ignore 'u' character?

There are no "characters" in numbers, and BookID is a numeric field. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Dec 31, 2012 at 18:50
When you say this is the wrong result, what were you expecting? Did you want an empty result? (Presumably '7u' is not permitted in your BookID field). — halfer
– halfer, Commented Jan 1, 2013 at 13:25

wallyk · Accepted Answer · 2012-12-31 18:27:16Z

4

Because 7u is not numeric, so apparently mysql is ignoring the u.

Maybe you are thinking of some high level programming languages which use suffixes to qualify the type of number? In C-derived languages, 7u would be an unsigned integer with value 7.

answered Dec 31, 2012 at 18:27

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jeremy Over a year ago

In other words, when you compare a numeric field against a non-numeric value, it probably converts it to an integer/number

user1903750 Over a year ago

can i do something to prevent this or any solution?

wallyk Over a year ago

@user1903750: Remove the u. Why is it there in the first place? Also, look at the data. With an autoincrement column, it is strictly numeric.

halfer · Accepted Answer · 2013-01-01 13:23:06Z

0

Just because of two reasons:

There may be only one record.
you were using LIMIT 1. That display only one record from the output. so use LIMIT 5 OR 2.

edited Jan 1, 2013 at 13:23

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered Jan 1, 2013 at 13:00

Sagar Maiyad

12.7k9 gold badges66 silver badges102 bronze badges

1 Comment

user1903750 Over a year ago

hi. u missed my point. my question is i expect no result but i get one. check wallyk answer.

Ravi Jethva · Accepted Answer · 2013-01-01 13:34:38Z

0

the mysql takes the string as 7u so when you try to do this stuff it coverts in a string but mysql is not in strict mode so it coverts string into integer because your id is intenger and its rounding of 7u becomes 7 thats why its display the one record try without quote 7u instead of '7u' then it gives error

answered Jan 1, 2013 at 13:34

Ravi Jethva

2,0312 gold badges12 silver badges12 bronze badges

Collectives™ on Stack Overflow

Wrong result from query

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related