Обсуждение: Subselects - recursion problem

People,

The following script works (I have confirmed it by doing two separate
views and doing a select on them) - but I don't understand why there
isn't a recursion problem with c1.policy and c2.policy - is there some
sort of trick happening?

Thanks,

Phil.


SELECT c1.loc, c1.lob, c1.policy
FROM crec
AS c1
WHERE c1.t_type = '1'
AND c1.t_diss = '2'
AND c1.recon = 'Y'
AND c1.policy = (   SELECT c2.policy
                    FROM crec
                    AS c2
                    WHERE c2.t_type = '1'
                    AND c2.t_diss = '0'
                    AND c2.recon = 'N'
                    AND c1.loc = c2.loc
                    AND c1.lob = c2.lob
                    AND c1.policy = c2.policy )
ORDER BY c1.loc, c1.lob, c1.policy ;



--
Philip Rhoades

Pricom Pty Limited  (ACN 003 252 275  ABN 91 003 252 275)
GPO Box 3411
Sydney NSW      2001
Australia
Mobile:  +61:0411-185-652
Fax:  +61:2:8923-5363
E-mail:  phil@chu.com.au

Philip Rhoades wrote:
> The following script works (I have confirmed it by doing two separate
> views and doing a select on them) - but I don't understand why there
> isn't a recursion problem with c1.policy and c2.policy - is there
> some sort of trick happening?

The subquery is evaluated for each row of the outer query.  The
references to c1 are for each evaluation replaced by the current values
of the outer query.  For the purpose of the subquery, they behave like
constants.

Internally, the query might actually be transformed into a join of c1
and c2.  Maybe that helps you grasp it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, May 30, 2005 at 07:33:04PM +1000, Philip Rhoades wrote:
> People,
>
> The following script works (I have confirmed it by doing two separate
> views and doing a select on them) - but I don't understand why there
> isn't a recursion problem with c1.policy and c2.policy - is there some
> sort of trick happening?

"recursion problem" ? It's called a correlated subquery. SQL is
declarative, you state what you want and the database figures out how
to get the answer for you. I think you need to go and read up on the
basics of SQL.

If you want to see *how* the database is working out the answer, use
explain and it'll display the query plan.

Hope this helps,

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn,


On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> On Mon, May 30, 2005 at 07:33:04PM +1000, Philip Rhoades wrote:
> > People,
> >
> > The following script works (I have confirmed it by doing two separate
> > views and doing a select on them) - but I don't understand why there
> > isn't a recursion problem with c1.policy and c2.policy - is there some
> > sort of trick happening?
>
> "recursion problem" ? It's called a correlated subquery. SQL is
> declarative, you state what you want and the database figures out how
> to get the answer for you. I think you need to go and read up on the
> basics of SQL.


Declarative or not, it looks strange having the output of the first
select dependent on a second select, which is dependent on the output of
the first select . .


> If you want to see *how* the database is working out the answer, use
> explain and it'll display the query plan.


 Sort  (cost=402711.95..402711.96 rows=1 width=20)
   Sort Key: loc, lob, policy
   ->  Index Scan using crec_9 on crec c1  (cost=0.00..402711.94 rows=1
width=20)
         Index Cond: (t_diss = '2'::bpchar)
         Filter: ((t_type = '1'::bpchar) AND (recon = 'Y'::bpchar) AND
(policy = (subplan)))
         SubPlan
           ->  Index Scan using crec_1 on crec c2  (cost=0.00..6.01
rows=1 width=10)
                 Index Cond: (($0 = loc) AND ($1 = lob) AND ($2 =
policy))
                 Filter: ((t_type = '1'::bpchar) AND (t_diss =
'0'::bpchar) AND (recon = 'N'::bpchar))


I can see that PG doesn't use "policy" in the filter of the subplan at
all but that doesn't help very much . .

Thanks anyway.

Phil.
--
Philip Rhoades

Pricom Pty Limited  (ACN 003 252 275  ABN 91 003 252 275)
GPO Box 3411
Sydney NSW      2001
Australia
Mobile:  +61:0411-185-652
Fax:  +61:2:8923-5363
E-mail:  phil@chu.com.au

On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote:
> Martijn,
>
>
> On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> > "recursion problem" ? It's called a correlated subquery. SQL is
> > declarative, you state what you want and the database figures out how
> > to get the answer for you. I think you need to go and read up on the
> > basics of SQL.
>
> Declarative or not, it looks strange having the output of the first
> select dependent on a second select, which is dependent on the output of
> the first select . .

Not really, if you think about it. SQL is a form of relational algebra
and like in normal algebra it's not unusual to have various variables
relating to eachother in various ways. Some expressions may look
recursive, but that's just another relationship.

As you can see from the query plan, it scans through each record in c1
filtering out rows based on the easy conditions. It then works out the
subquery for each row and compares the result with policy. If it works
the row is returned otherwise it keeps going. There's no recursion.

Perhaps the easiest way to think about it is having the subquery as a
function, and write it like:

SELECT c1.loc, c1.lob, c1.policy
FROM crec AS c1
WHERE c1.t_type = '1'
AND c1.t_diss = '2'
AND c1.recon = 'Y'
AND c1.policy = Subquery( c1 )
ORDER BY c1.loc, c1.lob, c1.policy ;

That doesn't look recursive to me, yet it's the same thing...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn,


On Mon, 2005-05-30 at 21:08, Martijn van Oosterhout wrote:
> On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote:
> > Martijn,
> >
> >
> > On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> > > "recursion problem" ? It's called a correlated subquery. SQL is
> > > declarative, you state what you want and the database figures out how
> > > to get the answer for you. I think you need to go and read up on the
> > > basics of SQL.
> >
> > Declarative or not, it looks strange having the output of the first
> > select dependent on a second select, which is dependent on the output of
> > the first select . .
>
> Not really, if you think about it. SQL is a form of relational algebra
> and like in normal algebra it's not unusual to have various variables
> relating to eachother in various ways. Some expressions may look
> recursive, but that's just another relationship.


OK.


> As you can see from the query plan, it scans through each record in c1
> filtering out rows based on the easy conditions. It then works out the
> subquery for each row and compares the result with policy. If it works
> the row is returned otherwise it keeps going. There's no recursion.


OK, makes sense in English . .


> Perhaps the easiest way to think about it is having the subquery as a
> function, and write it like:
>
> SELECT c1.loc, c1.lob, c1.policy
> FROM crec AS c1
> WHERE c1.t_type = '1'
> AND c1.t_diss = '2'
> AND c1.recon = 'Y'
> AND c1.policy = Subquery( c1 )
> ORDER BY c1.loc, c1.lob, c1.policy ;
>
> That doesn't look recursive to me, yet it's the same thing...
>
> Hope this helps,


No, that looks just as recursive to me but I get the picture from the
English explanation so thanks again.

Regards,

Phil.
--
Philip Rhoades

Pricom Pty Limited  (ACN 003 252 275  ABN 91 003 252 275)
GPO Box 3411
Sydney NSW      2001
Australia
Mobile:  +61:0411-185-652
Fax:  +61:2:8923-5363
E-mail:  phil@chu.com.au