In MySQL why is this IF returning false?

Question

In MySql, if the first argument of an IF() function is a string, why does it return false?

SELECT IF('string', 'string', 'not string'); -- 'not string'

Of course I could sort of fix this if I did

IF(!ISNULL('string'), 'string', 'not string')) -- 'string'

or

IFNULL('string', 'not string'); -- 'string'

It seems somewhat counter-intuitive that it evaluates a string the way that it does seeing as

SELECT IF(1, 'one', 'not one'); -- 'one'

and

SELECT IF('1', 'one', 'not one'); -- 'one'

evaluate the way that they do...

Answer 1

Because 'string' is neither true nor false and the first expression should evaluate to a boolean.

Why does IF(1, ...) evaluate to true? Good question. Maybe a throwback to C (i.e. if it's 1, then it's true)?

EDIT: Actually, by the definition of the command at http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if, expr1 is TRUE if expr1 <> 0 and expr1 <> NULL, which is the case when expr1 == 1.

Answer 2

The first argument given to IF() is a predicate. A string isn't considered a predicate by MySQL, so it defaults to false. As for your last case, a lot of languages (C, Perl, etc) consider nonzero integers true, so MySQL is simply supporting that paradigm.

Answer 3

From MySQL

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

So 1 is true because 1 != 0 and 1 != NULL. This is like what you would see in C.

But for a string, saying a 'test' evaluates to true has no real basis in the definition and does not make logical sense. It needs to be compared to something for a boolean result.

Answer 4

Note that there are similarities in PHP:

"string" != 0 // false
"one"    != 0 // false
"1"      != 0 // true

The confusion seems to arise because MySQL compares to 0 (and null) to get the boolean value of something. In other languages, like PHP and Javascript, a string, when cast to boolean, returns true when non-empty (or not "0").

// php
0 == true          // false
"string" == 0      // true*
"string" == true   // true

// mysql
if(0, "true", "false")   // false
if("string", "true", "false") // false*

The two *starred* lines show the equivalent comparisons, if you know what I mean.