Postgres Pro
Downloads
Home   >   mailing lists

Thread: OR working strangely.

OR working strangely.

From
Börkur Sigurbjörnsson
Date:
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 ( 1=0 )
+----------------------------------------+----------------------------------
------+
| list                                   | usr
|
+----------------------------------------+----------------------------------
------+
| {0aa12786-331b-f691-8d83-39d78ae8554e} |
{16c1b998-4daf-7379-cefb-276cecb634e8} |
+----------------------------------------+----------------------------------
------+
Query OK, 1 rows in set (0,74 sec)

> 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
  )
+------+-----+
| list | usr |
+------+-----+
+------+-----+
Query OK, 0 rows in set (0,64 sec)


Note that "(criteria 1) OR (1=0)" returns a result, but
"(criteria 1) OR (criteria 2)" returns an empty table!

Criteria 2 should be a don´t care factor in this, since (1=0) is obviously
false.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Börkur Sigurbjörnsson, developer
 borkur@handpoint.com

 tel:    + 354 561 0700
 mobile: + 354 869 2122

                        handPoint
                www.handpoint.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Re: OR working strangely.

From
Tom Lane
Date:
=?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