> 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

Wed, 21 Jan 1998 09:13:05 -0500
darrenk@insightdist.com (Darren King)
Re: [HACKERS] subselects
> 
> > 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

Wed, 21 Jan 1998 10:56:37 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] subselects