subselects

Bruce Momjian wrote:

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?

Yes, this is right for 6.3. I hope that we'll support subselects in
target list, FROM, etc in future.

BTW, I'm going to implement subselect in (let's say) "natural" way -
without substitution of parent query relations into subselect and so on,
but by execution of (correlated) subqueries for each upper query row
(may be with cacheing of results in hash table for better performance).
Sure, this is much more clean way and much more clear how to do this.
This seems like SQL-func way, but funcs start/run/stop Executor each time
when called and this breaks performance.

Vadim

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

Is that correct?

Yes, this is right for 6.3. I hope that we'll support subselects in
target list, FROM, etc in future.

OK.

BTW, I'm going to implement subselect in (let's say) "natural" way -
without substitution of parent query relations into subselect and so on,
but by execution of (correlated) subqueries for each upper query row
(may be with cacheing of results in hash table for better performance).
Sure, this is much more clean way and much more clear how to do this.
This seems like SQL-func way, but funcs start/run/stop Executor each time
when called and this breaks performance.

Sure, lets see how it performs. Most correlated subqueries are very
slow in commercial databases too. I guess I thought you could do the
whole subquery, then sort on the correlated columns, which allows quick
access to the results, but if the subquery references only a small part
of the upper query's output, it is quicker to do it your way.

--
Bruce Momjian
maillist@candle.pha.pa.us

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

If its easy to add in the next couple of days, sure, go for
it...but can someone explain to me *why* you would use a subselect in
the
target list? I've actually never seen that before :9

I think it is always possible to rewrite a 'subselect in the target
list' as a join.
So if it is complicated to implement now, I'd say leave it out, as there
is no functionality aspect
I could think of.

Andreas