Re: Query returning incorrect results

Michael Fork <mfork(at)toledolink(dot)com> writes:
> When I execute the following two queries, the results differ -- with the
> only change being that another table is joined (a 1-1 join that should not
> affect the results -- I reduced down a much larger query that was
> exhibiting the behavior to what appears to be the cause). I know that
> views have some limitations, and two of the relations used are views, so I
> belive that that may be the problem, but I want to be sure...

Grouped views don't really work in 7.0.* or prior releases, except in
the very simplest cases. The query rewriter basically just does "macro
expansion" of the view into your query, so unless you can write out the
equivalent query as valid SQL without using views, it won't work. In
particular, selecting simultaneously from two views with different
grouping requirements cannot work, because there's only one GROUP BY
phase available. Unfortunately, the rewriter is too stupid to notice
that it can't produce a correct translation :-(

This is fixed in current sources --- a grouped view will be done as
a true sub-query.

regards, tom lane