Re: Bug in move 0

-----Original Message-----
From: pgsql-bugs-owner@hub.org
[mailto:pgsql-bugs-owner@hub.org]On Behalf Of Bruce Momjian

I can confirm this bug still exists in the code. Sorry.

Please enter a FULL description of your problem:
------------------------------------------------

If declare a cursor for a query, and move with 0. Then can not

If I remember correctly *move 0* means *move all* in the
implementation of cursor.
I don't know if it's the spec or not.

fetch any record !

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

If I remember correctly *move 0* means *move all* in the
implementation of cursor.

It does (I looked at it before).

I don't know if it's the spec or not.

As best I can tell from SQL92, FETCH RELATIVE 0 implies no movement
(refetch same row). MOVE is not in the spec but it seems it ought
to behave like that too.

The fix seemed too extensive to be making in the last days of 7.0
beta, so I let it go at the time --- but someone should fix this.

regards, tom lane

Seems MOVE 0 moves to the end of the cursor. The FETCH manual says:

SQL92 allows one to repetatively retrieve the cur-
sor at its "current position" using the syntax

FETCH RELATIVE 0 FROM cursor

Postgres does not currently support this notion; in
fact the value zero is reserved to indicate that
all rows should be retrieved and is equivalent to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
specifying the ALL keyword. If the RELATIVE key-
word has been used, the Postgres assumes that the
user intended SQL92 behavior and returns this error
message.

=====================================================================
POSTGRESQL BUG REPORT TEMPLATE
=====================================================================

Your name : Jutso Hung
Your email address : jutso@tpts5.seed.net.tw

System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD K6

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.12-20

PostgreSQL version (example: PostgreSQL-6.5.3): PostgreSQL-7.0Beta1

Compiler used (example: gcc 2.8.0) : egcs-2.91.66

Please enter a FULL description of your problem:
------------------------------------------------

If declare a cursor for a query, and move with 0. Then can not
fetch any record !

a example:

template1=# select * from emp;
empno | name
--------+------------
100001 | John Smith
100002 | Mary White
100003 | Joe Brown
(3 rows)

template1=# begin;
BEGIN
template1=# declare x cursor for select * from emp;
SELECT
template1=# move 0 in x;
MOVE
template1=# fetch all in x;
empno | name
-------+------
(0 rows)

template1=# move backward all in x;
MOVE
template1=# fetch all in x;
empno | name
--------+------------
100001 | John Smith
100002 | Mary White
100003 | Joe Brown
(3 rows)

Jutso Hung <jutso@tpts5.seed.net.tw>

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

If I remember correctly *move 0* means *move all* in the
implementation of cursor.

It does (I looked at it before).

I don't know if it's the spec or not.

As best I can tell from SQL92, FETCH RELATIVE 0 implies no movement
(refetch same row). MOVE is not in the spec but it seems it ought
to behave like that too.

The fix seemed too extensive to be making in the last days of 7.0
beta, so I let it go at the time --- but someone should fix this.

I just sent off an email stating that MOVE 0 goes to the end, and that
the FETCH manual page says:

SQL92 allows one to repetatively retrieve the cur-
sor at its "current position" using the syntax

FETCH RELATIVE 0 FROM cursor

Postgres does not currently support this notion; in
fact the value zero is reserved to indicate that
all rows should be retrieved and is equivalent to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
specifying the ALL keyword. If the RELATIVE key-
word has been used, the Postgres assumes that the
user intended SQL92 behavior and returns this error
message.

So it seems we are OK.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I just sent off an email stating that MOVE 0 goes to the end, and that
the FETCH manual page says:

Postgres does not currently support this notion; in
fact the value zero is reserved to indicate that
all rows should be retrieved and is equivalent to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
specifying the ALL keyword. If the RELATIVE key-
word has been used, the Postgres assumes that the
user intended SQL92 behavior and returns this error
message.

So it seems we are OK.

We may have documented the behavior, but that doesn't make it right ;-)
If someone were to submit a patch to change MOVE 0 into a no-op
(without breaking MOVE ALL of course), I'd vote to apply it.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I just sent off an email stating that MOVE 0 goes to the end, and that
the FETCH manual page says:

Postgres does not currently support this notion; in
fact the value zero is reserved to indicate that
all rows should be retrieved and is equivalent to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
specifying the ALL keyword. If the RELATIVE key-
word has been used, the Postgres assumes that the
user intended SQL92 behavior and returns this error
message.

So it seems we are OK.

We may have documented the behavior, but that doesn't make it right ;-)
If someone were to submit a patch to change MOVE 0 into a no-op
(without breaking MOVE ALL of course), I'd vote to apply it.

If we do that, how does one move to the end of a cursor?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

We may have documented the behavior, but that doesn't make it right ;-)
If someone were to submit a patch to change MOVE 0 into a no-op
(without breaking MOVE ALL of course), I'd vote to apply it.

If we do that, how does one move to the end of a cursor?

MOVE ALL.

The problem right now is just that MOVE ALL is internally represented
as MOVE 0 ... there needs to be a different representation for it.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

We may have documented the behavior, but that doesn't make it right ;-)
If someone were to submit a patch to change MOVE 0 into a no-op
(without breaking MOVE ALL of course), I'd vote to apply it.

If we do that, how does one move to the end of a cursor?

MOVE ALL.

The problem right now is just that MOVE ALL is internally represented
as MOVE 0 ... there needs to be a different representation for it.

regards, tom lane

Added to TODO:

* MOVE 0 should not move to end of cursor

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026