Thread: 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.
Attachment
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.
Attachment
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