2

I have a question about and AND inside a where clause of a query. Within the where clause, I have a subquery but it is after a few other, row value conditions, like validated = 1 AND (subquery). I have a question about the AND...

How does MySQL treat it? Will it continue to the subquery if the first condition in the where clause, before the AND is false?

Improve this question
5
  • 1
    The documentation does not set the order of expression parts evaluation. Moreover while reading how optimizer works one may understand that the evaluation order can be changed while comparing to the order in the query text. So "before in a text" do not mean "before in evaluation".
    – Akina
    Commented Sep 19, 2018 at 18:55
  • @Akina Can you link the documentation? What I meant, specifically, is in code, the && operator returns false if the first condition is false, and does not evaluate the second, or after, condition(s). Does MySQL do that, if evaluating from left to right, or will it continue to evaluate the whole expression?
    – Ice76
    Commented Sep 19, 2018 at 19:11
  • 1
    It is possible that "second condition" will be evaluated first, and "first condition" will be evaluated last.
    – Akina
    Commented Sep 19, 2018 at 19:15
  • @Akina Is there a doc to precedence?
    – Ice76
    Commented Sep 19, 2018 at 19:16
  • Can you link the documentation? This question had interested me ~5 years ago. I remember that I found some information in the documentation that there is something like I have described above. Now I cannot find this place in the documentation quickly... maybe later - or somebody else, maybe you...
    – Akina
    Commented Sep 19, 2018 at 19:22

1 Answer 1

Reset to default
2

I did some experimenting. Here's my guess:

WHERE (constant-expression-evaluating-to-FALSE)
  AND (SELECT ...)

It will evaluate the constant and stop.

WHERE (col ... )  -- some test that can't use an index (but always fails)
  AND (SELECT ...)

It will evaluate the SELECT. Here the Optimizer is being smart -- it realizes that reevaluating the SELECT is likely to be costly, so better to do it once. Actually, the EXPLAIN FORMAT=JSON says "optimized_away_subquery", which probably means that, in the process of deciding what to do with the subquery, it evaluated it.

Note that I am assuming that the subquery is not correlated. That is, nothing from the original table is referenced in it. If it were, then the Optimizer would be obligated to evaluate it for each row in the outer query. And it would probably go back to the original case -- always FALSE would avoid evaluating the subquery. However, multiple TRUEs would lead to multiple evaluations.

(I arrived at this by (1) somewhat understanding the Optimizer, and (2) testing. Caveat: My test was with 5.6.22; older versions, and MariaDB may work differently. However, I believe that 5.7 and 8.0 will work the same.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.