    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) #



Wed, 12 May 1999 19:51:18 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
WHERE vs HAVING
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

Wed, 12 May 1999 14:06:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] 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.

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, Pennsylvania 19026

Tue, 6 Jul 1999 23:40:33 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] WHERE vs HAVING