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@debis.com (Jan Wieck) #