Thread: problem with using fetch and a join.

Hi
I'm performing the following sql statements:

begin;
declare test cursor for
select protein.accession
from protein, region
where protein.accession like '%1245%'
and protein.accession = region.accession
and region.protein_database = 1;
fetch forward 2 in test;

result -->

accession
Q12454/3-180
CA11_HUMAN/1245-1463
total of 2 rows

All good.

However if i perform these:

begin;
declare test cursor for
select protein.accession
from protein, region
where protein.accession like '%1245%'
and protein.accession = region.accession
and region.protein_database = 1;
fetch forward 2 in test;
fetch backward 1 in test;

NO RESULT!!

Then if I change the select statement to this:

begin;
declare test cursor for
select accession
from protein
where accession like '%1245%';
fetch forward 100 in test;

result -->
accession
22991245
1245847
22124507
91245
1245381
11245811
.......
21245076
total of 100 results

And with the fetch backward statement added:

begin;
declare test cursor for
select accession from protein
where accession like '%1245%';
fetch forward 3 in test;
fetch backward 2 in test;

Result -->

accession
1245847
22991245

As expected.

The only difference between the two select statements is that the first
statement involves two tables to collect the results.
Can someone explain why the fetch backward statement returns no result
in the first statement but dose in the second.


Many thanks
--
Noel Faux
Department of Biochemistry and Molecluar Biology
Monash University
Clayton 3168
Victoria
Australia

Noel <noel.faux@med.monash.edu.au> writes:
> begin;
> declare test cursor for
> select protein.accession
> from protein, region
> where protein.accession like '%1245%'
> and protein.accession = region.accession
> and region.protein_database = 1;
> fetch forward 2 in test;
> fetch backward 1 in test;

FETCH (or MOVE) BACKWARD doesn't work with most join plan types (or
indeed anything much more complex than a simple seqscan or indexscan).
There ought to be some logic in there to detect and complain about the
non-working cases, but right now I fear you just get the wrong answer
when any given routine doesn't pay attention to the direction flag :-(

I believe it will work when the top plan node is a Sort, so one possible
workaround is to add an explicit ORDER BY to the query.

            regards, tom lane

Tom,
Many thanks for the tip. Yes adding the order by (id) to the statement
allowed the FETCH BACKWARD to work. Is there any reason why the FETCH
BACKWARD statement for such a join not to be supported in the future?

Once again,
Many Thanks
And have a great holiday season :D

Noel Faux
Department of Biochemistry and Molecluar Biology
Monash University
Clayton 3168
Victoria
Australia

----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Saturday, December 21, 2002 1:47 am
Subject: Re: [NOVICE] problem with using fetch and a join.

> Noel <noel.faux@med.monash.edu.au> writes:
> > begin;
> > declare test cursor for
> > select protein.accession
> > from protein, region
> > where protein.accession like '%1245%'
> > and protein.accession = region.accession
> > and region.protein_database = 1;
> > fetch forward 2 in test;
> > fetch backward 1 in test;
>
> FETCH (or MOVE) BACKWARD doesn't work with most join plan types (or
> indeed anything much more complex than a simple seqscan or indexscan).
> There ought to be some logic in there to detect and complain about the
> non-working cases, but right now I fear you just get the wrong answer
> when any given routine doesn't pay attention to the direction flag
> :-(
>
> I believe it will work when the top plan node is a Sort, so one
> possibleworkaround is to add an explicit ORDER BY to the query.
>
>                     regards, tom lane
>

Noel Faux <Noel.Faux@med.monash.edu.au> writes:
> Many thanks for the tip. Yes adding the order by (id) to the statement
> allowed the FETCH BACKWARD to work. Is there any reason why the FETCH
> BACKWARD statement for such a join not to be supported in the future?

If someone wants to make it happen ... my take is that actually making
it work in all cases would be lots more trouble than it's worth.  I'd
settle for producing an error message in place of a wrong answer.

            regards, tom lane

Hi Tom,

I'm also having problems with fecthing and moving backwards within a view.
ie. (comparison_region_protein is a view)

int result = stmt.executeUpdate("declare test cursor for select id from " +
                    "comparison_region_protein where db_db_comparison = 1 order by id");
I can move and fetch forward, but i can't fetch or move backwards. Is this because of the reasons metioned before? Is there a work around?

Many thanks
Noel


Tom Lane wrote:

Noel Faux <Noel.Faux@med.monash.edu.au> writes: 

Many thanks for the tip. Yes adding the order by (id) to the statement 
allowed the FETCH BACKWARD to work. Is there any reason why the FETCH 
BACKWARD statement for such a join not to be supported in the future?   

If someone wants to make it happen ... my take is that actually making
it work in all cases would be lots more trouble than it's worth.  I'd
settle for producing an error message in place of a wrong answer.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org