From: jwieck@debis.com
To: vadim@krs.ru
Cc: jwieck@debis.com
Cc: s-fery@kkt.sote.hu
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] cursor and update + view
Date: Thu, 26 Nov 1998 11:37:28 +0100 (MET)
Message-ID: <m0ziyng-000EBfC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <365CCAB0.CE4B0143@krs.ru>
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) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jwieck@debis.com, vadim@krs.ru, s-fery@kkt.sote.hu, hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] cursor and update + view
In-Reply-To: <m0ziyng-000EBfC@orion.SAPserv.Hamburg.dsh.de>
References: <365CCAB0.CE4B0143@krs.ru> <m0ziyng-000EBfC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox