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

On 2013-05-06, Tim Uckun <timuckun@gmail.com> wrote:
> --047d7b2e4ea07402b004dc034a3b
> Content-Type: text/plain; charset=UTF-8
>
> 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?

no. there is no guarantee which matching row you will get. Testing may
suggest that one answer is preferred but udating the table can change
which one. also you may get a different row without updating the table.

> What about for IN (X, Y)

same deal.

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

what difference does that make to the result?

> Basically I want to know if and how OR shortcuts the evaluation.

In a word. "unpredictably".

The planner will try to do the cheapest, most useful side first.

--
⚂⚃ 100% natural