Обсуждение: WHERE vs HAVING

Planner guru's please!

    I  wonder  what makes the difference between WHERE and HAVING
    that causes HAVING to accept aggregates while WHERE  doesn't.
    It  would  be  extremely nice if it's possible to teach WHERE
    how to handle aggregates properly. Having to push  them  into
    subselects during rewrite if a views aggregate column appears
    in the WHERE clause is a total mess.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

jwieck@debis.com (Jan Wieck) writes:
>     I  wonder  what makes the difference between WHERE and HAVING
>     that causes HAVING to accept aggregates while WHERE  doesn't.

Huh?  It seems inherent in the definition to me: WHERE is a filter
applied to individual tuples before any aggregation stage can happen,
thus it makes no sense for it to include aggregate functions
(except in explicit subselects, which create a new context for the
aggregation to occur in).  HAVING applies to groups of tuples after
aggregation, so aggregate functions can meaningfully be applied to
those groups.

>     It  would  be  extremely nice if it's possible to teach WHERE
>     how to handle aggregates properly. Having to push  them  into
>     subselects during rewrite if a views aggregate column appears
>     in the WHERE clause is a total mess.

Explain to me what you think it should mean.  It sounds to me like
you are trying to have the rewrite system change an incorrect query
into a valid one.  Doesn't strike me as a good idea; does the user
know what he's going to get?
        regards, tom lane

>     Planner guru's please!
> 
>     I  wonder  what makes the difference between WHERE and HAVING
>     that causes HAVING to accept aggregates while WHERE  doesn't.
>     It  would  be  extremely nice if it's possible to teach WHERE
>     how to handle aggregates properly. Having to push  them  into
>     subselects during rewrite if a views aggregate column appears
>     in the WHERE clause is a total mess.

SQL requires the restriction.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026