Thread

1. Re: [HACKERS] subselects

   Darren King <darrenk@insightdist.com> — 1998-01-21T14:13:05Z

   > We are only going to have subselects in the WHERE clause, not in the
   > target list, right?
   > 
   > The standard says we can have them either place, but I didn't think we
   > were implementing the target list subselects.
   > 
   > Is that correct?
   
   What about the HAVING clause?  Currently not in, but someone here wants
   to take a stab at it.
   
   Doesn't seem that tough...loops over the tuples returned from the group
   by node and checks the expression such as "x > 5" or "x = (subselect)".
   
   The cost analysis in the optimizer could be tricky come to think of it.
   If a subselect has a HAVING, would have to have a formula to determine
   the selectiveness.  Hmmm...
   
   darrenk
   
   

2. Re: [HACKERS] subselects

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-01-21T15:56:37Z

   > 
   > > We are only going to have subselects in the WHERE clause, not in the
   > > target list, right?
   > > 
   > > The standard says we can have them either place, but I didn't think we
   > > were implementing the target list subselects.
   > > 
   > > Is that correct?
   > 
   > What about the HAVING clause?  Currently not in, but someone here wants
   > to take a stab at it.
   > 
   > Doesn't seem that tough...loops over the tuples returned from the group
   > by node and checks the expression such as "x > 5" or "x = (subselect)".
   > 
   > The cost analysis in the optimizer could be tricky come to think of it.
   > If a subselect has a HAVING, would have to have a formula to determine
   > the selectiveness.  Hmmm...
   
   Code is in the grammar, but have to add Aggreg code to parser and
   optimizer needs a qual restriction on a Aggreg field.  You really just
   need to be able to put a restriction on an aggregate in the WHERE
   clause, but have it evaluated AFTER the GROUP BY.
   
   Well, I have just done lots of work on fixing aggregate issues, so maybe
   I should give it a try, but not for 6.3.  Run out of non-business-work
   time this month.  Boss is going to figure it out soon.  :-)  (Hey, I am
   the boss.)
   
   Give it a stab.  I can help you out.  I can even review your patches and
   give you ideas.
   
   You will have to enable HAVING in gram.y, and have analyze.c call
   transformExpr() for the HAVING clause.  And have rewrite do the HAVING
   clause just like it does the qual.  Then, in the upper optimizer, you
   will see where aggregates are handled in planner.c.  You will need to
   put the HAVING restriction above the GROUPBY and AGG nodes, so you can
   do the restriction AFTER those have been computed.  The tricky part is
   that we only allow aggregates in the target list, so in this case you
   want an aggregate that is not in the target list.
   
   -- 
   Bruce Momjian
   maillist@candle.pha.pa.us