why mysql returns some rows when it should return null?

Question

here is structure of myTable

----------------
|id|info1|info2|
----------------
|0 |abcde|fghij|
|1 |qwert|yuopa|
----------------

here is the query I call :

SELECT * FROM `myTable` WHERE `id` = 'a'

and it returns the first row which is

|0 |abcde|fghij|

what is happening? normally id is an integer but, by mistake I typed it as a string but I still got a result.

Can you show us the structure of myTable? I'm finding this difficult to replicate... — amaidment
– amaidment, Commented Apr 26, 2012 at 15:49

Dmytro Shevchenko · Accepted Answer · 2012-04-26 15:52:34Z

1

MySQL tries to cast 'a' as integer. The string does not contain any numeric values, so the result of the cast is 0. That's just how MySQL does casting.

SELECT 0 = 'a';

The above query returns 1 (TRUE).

SELECT CAST('a' AS SIGNED INTEGER);

The above query returns 0.

edited Apr 26, 2012 at 15:52

answered Apr 26, 2012 at 15:47

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

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

1 Comment

Dmytro Shevchenko Over a year ago

@bluebrain well, there's that. But more importantly, you shouldn't compare ids in the table to strings. This is obviously a logical error in the query.

Collectives™ on Stack Overflow

why mysql returns some rows when it should return null?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related