Re: Query not working as expected...

"Titus J. Anderson" <titus.anderson@louisville.edu> writes:
> Notice the last row.  The row with did 5738 is NOT in the result set from the
> second query, which is the subquery of the above command.  So why is this
> showing up in the results?  Anyone have an idea?

What version are you running?  This looks a lot like a bug that was
fixed in 7.1.3:

2001-07-31 14:39  tgl

    * src/: backend/optimizer/path/allpaths.c,
    backend/optimizer/util/clauses.c, backend/utils/adt/ruleutils.c,
    include/optimizer/clauses.h (REL7_1_STABLE): Fix optimizer to not
    try to push WHERE clauses down into a sub-SELECT that has a
    DISTINCT ON clause, per bug report from Anthony Wood.  While at it,
    improve the DISTINCT-ON-clause recognizer routine to not be fooled
    by out-of-order DISTINCT lists.  Also, back-patch earlier fix to
    not push down into sub-SELECT with LIMIT.

            regards, tom lane