Thread

bug: HashJoin and backward cursors...

Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-10T09:33:01Z

tempx and tempx2 are tables with single int4 field. Having single record
with value of 1 in both tables:

vac=> explain select * from tempx, tempx2 where tx = tx2;
NOTICE:  QUERY PLAN:

Hash Join  (cost=17.20 size=100 width=8)
  ->  Seq Scan on tempx2  (cost=4.30 size=100 width=4)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Seq Scan on tempx  (cost=4.30 size=100 width=4)

EXPLAIN
vac=> begin;
BEGIN
vac=> declare c cursor for select * from tempx, tempx2 where tx = tx2;
SELECT
vac=> fetch 1 in c;
tx|tx2
--+---
 0|  0
(1 row)

vac=> fetch 1 in c;
tx|tx2
--+---
 1|  1
(1 row)
vac=> fetch backward 1 in c;
tx|tx2
--+---
 0|  0
(1 row)
^^^^^^^^^^^^^^^^^^^^^^ - very nice!
vac=> end;
END
vac=> insert into tempx values (1);
INSERT 320416 1
vac=> begin;
BEGIN
vac=> declare c cursor for select * from tempx, tempx2 where tx = tx2;
SELECT
vac=> fetch 1 in c;
tx|tx2
--+---
 0|  0
(1 row)

vac=> fetch 1 in c;
tx|tx2
--+---
 1|  1
(1 row)

vac=> fetch backward 1 in c;
tx|tx2
--+---
 1|  1
(1 row)
^^^^^^^^^^^^^^^^^^^^^^^^^^ 

HashJoin doesn't care about scan dirrection...

This is another story:

vac=> fetch backward 1 in c;
tx|tx2
--+---
 0|  0
(1 row)

vac=> fetch backward 1 in c;
tx|tx2
--+---
(0 rows)

vac=> fetch 1 in c;
PQexec() -- Request was sent to backend, but backend closed the channel before responding.
        This probably means the backend terminated abnormally before or while processing the request.

Vadim