CODEWITHSUNDEEP
mysqlshort-circuiting
DOOManiac
DOOManiac

Reputation: 6284

Does MySQL Short Circuit the IF() function?

I need to query data from a second table, but only if a rare set of conditions in the primary table is met:

SELECT ..., IF(a AND b AND c AND (SELECT 1 FROM tableb ...)) FROM tablea ...

a, b, and c conditions are almost always false, so my thinking is the subquery will never execute for most rows in the result set and thus be way faster than a join. But that would only true if the IF() statement short circuits.

Does it?

Thanks for any help you guys can provide.

Upvotes: 10

Views: 7156

Answers (4)

J Jorgenson
J Jorgenson

Reputation: 1499

The answer is YES.
The IF(cond,expr_true,expr_false) within a mysql query is short-circuited.

Here a test, using @variables to prove the fact:

SET @var:=5;  
SELECT IF(1 = 0, (@var:=@var + 1), @var ); -- using ':=' operator to modify 'true' expr @var 
SELECT IF(1 = 1, @var, (@var:=@var + 1) ); -- using ':=' operator to modify 'false' expr @var 
SELECT @var;

The result is '5' from all three SELECT queries.

Had the IF() function NOT short circuited, the result would be a '5' from SELECT #1, and '6' from SELECT #2, and a '7' from the last "select @var".

This is because the 'true' expression is NEVER executed, in select #1 and nor is the false expression executed for select #2.

Note the ':=' operator is used to modify an @var, within an SQL query (select,from, and where clauses). You can get some really fancy/complex SQL from this. I've used @vars to apply 'procedural' logic within a SQL query.

-- J Jorgenson --

Upvotes: 13

waded
waded

Reputation: 78

It depends.

IF doesn't short-circuit such that it can be used to avoid truncation warnings with GROUP_CONCAT, for example in:

set @@group_concat_max_len = 5;

select if(true or @var:=group_concat('warns if evaluated'), 'actual result', @var);

the result will be 'actual result' but you'll get a warning:

Warning (Code 1260): Row 1 was cut by GROUP_CONCAT()

which is the same warning you get with less trivial GROUP_CONCAT expressions, such as distinct keys, and without the IF at all.

Upvotes: 2

DOOManiac
DOOManiac

Reputation: 6284

With J. Jorgenson's help I came up with my own test case. His example does not try to short circuit in the condition evaluation, but using his idea I came up with my own test and verified that MySQL does indeed short-circuit the IF() condition check.

SET @var:=5;
SELECT IF(1 = 0 AND (@var:=10), 123, @var); #Expected output: 5
SELECT IF(1 = 1 AND (@var:=10), @var, 123); #Expected output: 10

On the second example, MySQL is properly short-circuiting: @var never gets set to 10.

Thanks for the help J. Jorgenson!

Upvotes: 4

Scott Stafford
Scott Stafford

Reputation: 44798

Try it in the SQL analyzer. If you want to be on the safe side and not have to trust the database to work one way (and not to change that behavior ever in new versions), just make two queries and do the IF programmatically.

Upvotes: 0

Related Questions