CODEWITHSUNDEEP
phpmysql
Ognj3n
Ognj3n

Reputation: 759

MySQL showing strange result

I have a table called tabela1512823699024883 that looks like this: enter image description here

On which I run query like this:

SELECT * FROM tabela1512823699024883 WHERE `age` = 'male'

This query does not make sense, because age column is int type and I'm looking for string value in my query, but MySQL still returns no empty rows whatsoever. Here is what query returned: enter image description here So age row does not contains male value in neither rows returned. How can this be possible?

Upvotes: 8

Views: 691

Answers (4)

Will B.
Will B.

Reputation: 18416

Same issue will occur with casting a string to an integer type.

SELECT CAST('male' as SIGNED); #0
#or
SELECT CAST('male' as UNSIGNED); #0

However if a number is supplied within the string.

SELECT CAST('1234male' as UNSIGNED); #1234
#and
SELECT CAST('male1234' as UNSIGNED); #0

To resolve the issue use BINARY on the column, which will also cause the textual value to become case-sensitive.

SELECT * FROM tabela1512823699024883 WHERE BINARY `age` = 'male';

Alternatively to utilize a case-insensitive BINARY criteria CONVERT() the value to the desired character-set and then specify a case-inventive collation. [sic]

SELECT * FROM tabela1512823699024883 WHERE CONVERT(BINARY `age` USING utf8) COLLATE utf8_general_ci = 'male';

Upvotes: 14

Pratik Soni
Pratik Soni

Reputation: 2588

The Problem here is that whenever you try to compare a string to integer column, the value will be cast to the data type of column. Same case will happen with float , big int, tiny int etc.

What ever value is passed in comparison first being cast for that particular data type by mysql engine

Here is the test result of the query casting few values.

select cast('' AS UNSIGNED), cast('anything' AS UNSIGNED), cast(NULL AS UNSIGNED), cast(35.56 AS UNSIGNED), cast(NOW() AS UNSIGNED), cast(1 AS UNSIGNED), cast('Test' AS BINARY), cast('' AS BINARY);

Result of casting few data types

SELECT * FROM `test_type` WHERE flt = 'test'

Here flt is float type column.

the flt is of float type column.

Upvotes: 2

sherzodr
sherzodr

Reputation: 101

"male" is 0! So you keep getting records where age is 0. To really understand what's happening, just do:

select user_name, age, age='male' from tabela1512823699024883;

Your 3rd column will be 1 for the records containing '0' age, and '0' for the records containing non-zero age.

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this simple query:

SELECT 'male' + 0

This seems not to make sense. However the query executes and returns a value of 0. This is because of implicit type conversion. String value 'male' is converted to 0 and then added to 0 and thus 0 is returned .

The same thing happens with your query. 'male' in converted to 0 when compared with a field of type int.

Upvotes: 11

Related Questions