Does MySQL Short Circuit the IF() function?

Question

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.

Plath

Answer 1

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!

Plath

I think it should be OR in the second select sql SELECT IF(1 = 1 OR (@var:=10), @var, 123); #Expected output: 5 if you want to prove that MySQL is properly short-circuiting for both AND and OR – Wassail

For some reason this is not working as expected on MariaDB 10.3.38 :(, I get '10' as result. Allthough the documentation clearly states it should work: mariadb.com/kb/en/operator-precedence/#short-circuit-evaluation When setting @var in a nested IF() it's not evaluated and output is '5' indeed. – Idioplasm

Answer 2

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 --

Eleph

Answer 3

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.

Paedo

Answer 4

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.

Aquilar