Comparing string against numeric field returning unexpected results [closed]

Question

Closed. This question is seeking recommendations for software libraries, tutorials, tools, books, or other off-site resources. It does not meet Stack Overflow guidelines. It is not currently accepting answers.

We don’t allow questions seeking recommendations for software libraries, tutorials, tools, books, or other off-site resources. You can edit the question so it can be answered with facts and citations.

Closed 20 days ago.

Improve this question

Given the table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Add a couple of rows:

INSERT INTO `users` (`id`,`name`) VALUES (NULL , 'Bob'),(NULL , 'Larry'),(NULL , 'Steve');

Why, OH WHY! does this query return results:

SELECT * FROM `users` WHERE id = "2this-is-not a numeric value"

Result:

query returned 1 row(s) in 0.0003 sec
id  name
-----------------
2   Larry

The string used in the where clause is clearly being converted to a numeric value -- who said to do that?! I cannot find any documentation that suggests mysql or PHP would presume to auto-cast my string literal.

This only works if the numeric character is the first one in the string, "this 2 is not numeric" would not return results. "12 2" would become 12, "1 2" (one-space-two) becomes 1.

Any articles or documentation explaining this behavior would be appreciated.

doens't mysql use ' instead of " to denote string? it might just be looking at the numeric value try to cast it to numerber implicitly and then truncate the non-numeric values. So... Why oh why! :D are you using quotes for a Numeric field forcing it to do an implicit conversion? needless to say I believe it odd behavior as well. — xQbert
– xQbert, Commented Jun 13, 2012 at 18:21
Either. I always use single quotes for code, so I always send double quotes to sql — Chris Baker
– Chris Baker, Commented Jun 13, 2012 at 18:22
I wonder what result you'd get if you cast your string to bigint implicitly in a select. If you get 2... then it's the implicit conversion and my o my is that ODD behavior. — xQbert
– xQbert, Commented Jun 13, 2012 at 18:24
In the actual code, the query is assembled by a function that wraps all the column names in backticks (`) and wraps all the values in quotes. Clearly, this function cannot continue to be used -- auto-casting is frustrating. — Chris Baker
– Chris Baker, Commented Jun 13, 2012 at 18:34

Scott Saunders · Accepted Answer · 2012-06-13 18:19:48Z

9

It's in the MySQL documentation here: http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

answered Jun 13, 2012 at 18:19

Scott Saunders

30.5k14 gold badges60 silver badges66 bronze badges

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

4 Comments

Mike Over a year ago

SELECT CONVERT( "2this-is-not a numeric value", UNSIGNED ) returns 2. Maybe you could add this to your answer.

Chris Baker Over a year ago

DO NOT LIKE. I want 0 results -- no row has an id of "2yourmom". When I say WHERE id = "2yourmom", I mean literally where the id is equal to that value. Auto-cast sucks, whereever it happens, in any language. Anyway... thanks for the link :D

xQbert Over a year ago

+1 this Nails the reason. Implicit converts in mysql WILL drop the non-numeric values after an int and then do the implicit conversion. Scary as hell to me but it's in the docs

Chris Baker Over a year ago

Great, now I need a PHP/JS-esq === operator, hehe

Collectives™ on Stack Overflow

Comparing string against numeric field returning unexpected results [closed]

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related