Re: Shortcut evaluation in OR or IN

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Shortcut evaluation in OR or IN
Date: 2013-05-06 08:43:11
Message-ID: km7qev$qjp$1@gonzo.reversiblemaps.ath.cx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 2013-05-06, Tim Uckun <timuckun(at)gmail(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Poole 2013-05-06 08:43:22 Re: Shortcut evaluation in OR or IN
Previous Message Tim Uckun 2013-05-06 02:16:38 Shortcut evaluation in OR or IN