Re: Query not working as expected...

"Titus J. Anderson" <titus(dot)anderson(at)louisville(dot)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