Re: OR working strangely. - Mailing list pgsql-general

From Tom Lane
Subject Re: OR working strangely.
Date
Msg-id 14949.968961760@sss.pgh.pa.us
Whole thread Raw
In response to OR working strangely.  (Börkur Sigurbjörnsson <borkur@handpoint.com>)
List pgsql-general
=?iso-8859-1?Q?B=F6rkur_Sigurbj=F6rnsson?= <borkur@handpoint.com> writes:
> We are seeing very strange behaviour in our OR statements:

> SELECT l.list AS list, l.locked AS usr
>   FROM svr_lists_questions lq, svr_lists l
>   WHERE ( l.locked != '{a7372de3-92b7-408b-8874-373b883b19a6}' )
>   OR

>     lq.question IN

>       SELECT lq.question
>       FROM svr_lists_questions lq, svr_lists l
>       WHERE lq.list = l.list
>       AND l.locked != '{a7372de3-92b7-408b-8874-373b883b19a6}'
>       GROUP BY lq.question
>     )
>     AND lq.list = l.list
>   )

What version are you running?  This query looks like it could get messed
up by cnfify, which was a bug that was fixed just before 7.0 release:

----------
1.25 Fri Apr 14 0:19:17 2000 by tgl
CVS Tags: REL7_0, REL7_0_PATCHES
Diffs to 1.24

Repair bug reported by Wickstrom: backend would crash if WHERE clause
contained a sub-SELECT nested within an AND/OR tree that cnfify()
thought it should rearrange.  Same physical sub-SELECT node could
end up linked into multiple places in resulting expression tree.
This is harmless for most node types, but not for SubLink.
Repair bug by making physical copies of subexpressions that get
logically duplicated by cnfify().  Also, tweak the heuristic that
decides whether it's a good idea to do cnfify() --- we don't really
want that to happen when it would cause multiple copies of a subselect
to be generated, I think.
----------

You're not seeing a crash like Wickstrom was, but two links to the same
sub-Query node could cause lesser sorts of misbehavior too.

            regards, tom lane

pgsql-general by date:

Previous
From: mikeo
Date:
Subject: Cache lookup for language failed 17533070
Next
From: "Adam Lang"
Date:
Subject: Re: Starting postmaster at boot