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

Jan Wieck wrote:
> 
> 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.
...
>     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?


Sybase' example:

select title, price 
from titles 
where price =   (select price    from titles    where title = "Straight Talk About Computers")

select t1.title, t1.price 
from titles t1, titles t2
where t1.price = t2.price and      t2.title = "Straight Talk About Computers"

- yes, executor should ensure that there was only one
record with t2.title equal "Straight Talk About Computers",
this could be flagged in some way...

On the other hand, I'm not sure that we have to concern
about such cases and about "unwise" queries at all -:)
So, forgive me for noise -:)

There are many other things to do and handling subqueries
in FROM is one of them.

Vadim