Re: Shortcut evaluation in OR or IN

On Mon, May 06, 2013 at 02:16:38PM +1200, Tim Uckun wrote:
> Say I have a select like this.
>
> SELECT * FROM table where field = X OR field = Y limit 1
>
> And I have two records one that matches X and one that matches Y will I
> always get X because the evaluation will stop after the first clause in the
> OR matches?
>
> What about for IN (X, Y)

There is no short-circuiting; you'll get one record or the other but no
guarantee which. If you want to guarantee what order records come out
in you need to add an ORDER BY. In the specific case you're describing
you could do ORDER BY field = X DESC and get the order you're looking for.

> how about if I am doing an update
>
> UPDATE table1 set x=table2.y where table1.field1 = table2.field1 OR
> table1.field2=table2.field2
>
> Will it update based on field1 if both fields match?

An update affects all rows that match the given condition so you'd get
both rows updated in this case. There's no LIMIT or ORDER BY available
in UPDATE.

Richard