Re: [HACKERS] Re: [SQL] cursor and update + view

Vadim wrote:

> Subquery --> Join transformation/optimization implemented in
> rule system will be used for Views only. Being implemented
> in optimizer it will be used in all cases.

Right for the current rule system, because it looks only for
pg_rewrite entries to apply. Since it is called for every
optimizable statement, it could do this as a last step on the
querylist to be returned. Even if there where no rules to
apply.

I still think that it's the right place to do. Transforming a
subselect into a join means to modify the users input, doing
something different finally. This is kind of rewriting like
for view rules. Reading the debug output "After rewriting"
someone should be able to see which relations get scanned,
where and which of their attributes are used for what.

"On the other hand" I thought a little deeper about the
transformation itself. On the first thought it looked so easy
but on the third I confused myself a little. Let's take an
easy subquery

SELECT A.f1 FROM A
WHERE A.f2 IN (SELECT B.f1 FROM B WHERE B.f2 = 'x');

This will return any A.f1 where f2 is referenced by a B.f1
WHERE B.f2 = 'x'. Regardless how often it is referenced, it
will only be returned once. I cannot think of a join that
can do this. The join

SELECT A.f1 FROM A, B
WHERE A.f2 = B.f1 AND B.f2 = 'x';

will return A.f1 as many times as there are duplicates in B
that match. And DISTINCT doesn't help here because it would
remove duplicate A.f1's too (what isn't the same as the
initial subselect does).

Could you give me an example where a subquery could get
translated into a join that produces exactly the same output,
no matter if there are duplicates or not?

Jan

--

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