sql - Are MySQL function arguments passed by value, by reference or lazy? - Stack Overflow

admin管理员组

文章数量:1345073

Say I have 2 SQLs:

1. select if(some_cond, some_varchar, some_large_text) from foo
2. select if(some_cond, some_varchar, md5(some_large_text)) from foo

Does the MySQL engine actually retrieve some_large_text, even if some_cond evaluates to true?

If the arguments are evaluated lazily, then none of these 2 SQLs would read some_large_text. If it's passed by reference, then only the second SQL would need to.

In the worst case, if it's passed by value, then how could I avoid the unnecessary cost of reading some_large_text?

---

Further investigation:

For builtin functions, their C++ implementation decides if an argument is evaluated. Like here in the mysql source code, the builtin SHA2(data, bits) function has an early return when args[0] is null, in which case args[1] would not be evaluated.

It can be proved by this example.

Say I have 2 SQLs:

1. select if(some_cond, some_varchar, some_large_text) from foo
2. select if(some_cond, some_varchar, md5(some_large_text)) from foo

Does the MySQL engine actually retrieve some_large_text, even if some_cond evaluates to true?

If the arguments are evaluated lazily, then none of these 2 SQLs would read some_large_text. If it's passed by reference, then only the second SQL would need to.

In the worst case, if it's passed by value, then how could I avoid the unnecessary cost of reading some_large_text?

---

Further investigation:

For builtin functions, their C++ implementation decides if an argument is evaluated. Like here in the mysql source code, the builtin SHA2(data, bits) function has an early return when args[0] is null, in which case args[1] would not be evaluated.

It can be proved by this example.

• sql
• mysql

Share Improve this question Follow edited yesterday yyyy asked yesterday yyyyyyyy 66833 silver badges1010 bronze badges 5

• 2 Server won't do excess work. If some_cond is true then none expression retrieves your some_large_text value. From the other side, when you access the row then the whole row is loaded into memory unconditionally, including those some_large_text part which is stored within the row record, whereas the overflow pages are retrieved only when the value must be used (i.e. when some_cond is true). – Akina Commented yesterday
• 2 sample fiddle - the function is not evaluated. – Akina Commented yesterday
• @Akina that proves it doesn't evaluate the expression in the if branch not taken. it does not prove anything about whether a column referenced is actually read or not. I would guess that it is in fact read for every row. – ysth Commented yesterday
• 1 @ysth To make sure that overflow pages are [not] read from disk, we must either monitor file access or examine the source code. – Akina Commented yesterday
• @Akina Thank you for providing an intuitive way to observe the side effect. After reading mysql source code, I think there's no universal rule (for builtin functions) deciding whether an argument should be evaluated or not, but rather an implementation detail. See the edited question. – yyyy Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

In general, function arguments are sent by value and all arguments are evaluated.

But IF is not an ordinary function, it's a flow-control function. These functions only evaluate the arguments that are needed based on the condition. MySQL IF() is similar to the ?: ternary operator in C and PHP.

This allows you to write expressions that would get an error in one of the arguments depending on the condition:

IF(n = 0, 0, x / n)

The IF function is basically just a short form of a CASE expression:

CASE WHEN n = 0 THEN 0
     ELSE x / n
END

I believe that this type of argument processing is only available to these built-in functions, you can't write a user-defined function that doesn't have all its arguments evaluated.

本文标签： sqlAre MySQL function arguments passed by valueby reference or lazyStack Overflow