#389 Count(*) within prepare incorrect

[Koichi Suzuki]

count(*) within prepared statement returns incorrect result, for both 1.0 and master.

--------8<---- (master) -----------------8<----------------------
$ createdb -p 50004 -h node06 db
$ psql -a -p 50004 -h node06 -f PREPARE-BUG-master.sql db
CREATE TABLE T1 (
C1 varchar,
C2 varchar,
C3 varchar,
C4 varchar,
C5 date
)
DISTRIBUTE BY HASH(C3) ;
CREATE TABLE

CREATE TABLE T2 (
C1 varchar,
C2 varchar,
C3 varchar,
C4 varchar,
C5 date
)
DISTRIBUTE BY HASH(C3) ;
CREATE TABLE

SELECT * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord1 | C | 50004 | node06 | f | f | 1885696643
coord2 | C | 50005 | node07 | f | f | -1197102633
coord3 | C | 50004 | node08 | f | f | 1638403545
coord4 | C | 50005 | node09 | f | f | -890157977
datanode1 | D | 50008 | node06 | f | t | 888802358
datanode2 | D | 50009 | node07 | f | f | -905831925
datanode3 | D | 50008 | node08 | f | f | -1894792127
datanode4 | D | 50009 | node09 | f | f | -1307323892
(8 rows)

INSERT INTO T2 VALUES ( 'c1' , 'c2' , 'A' , 'PGXC' , '2013-01-01');
INSERT 0 1
INSERT INTO T1 VALUES ( 'c1' , 'c2' , 'A' , 'PGXC' , '2013-01-01');
INSERT 0 1

PREPARE cnt (VARCHAR, VARCHAR) AS
SELECT COUNT(*) FROM T1 WHERE C3 = $1 AND C4 = $2 ;
PREPARE

PREPARE del (VARCHAR, VARCHAR) AS
DELETE FROM T1 WHERE C3 = $1 AND C4 = $2 ;
PREPARE

PREPARE ins (VARCHAR, VARCHAR) AS
INSERT INTO T1 SELECT * FROM T2 WHERE C3 = $1 AND C4 = $2 ;
PREPARE

PREPARE upd (VARCHAR, VARCHAR, VARCHAR, VARCHAR) AS
UPDATE T1 SET C1 = $1 , C2 = $2 , C5 = DATE_TRUNC('second',clock_timestamp()) WHERE C3 = $3 AND C4 = $4 ;
PREPARE

PREPARE sel (VARCHAR, VARCHAR) AS
SELECT C2 ,C1 FROM T1 WHERE C3 = $1 AND C4 = $2 ;
PREPARE

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
UPDATE 1
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
UPDATE 1
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
UPDATE 1
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
UPDATE 1
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
UPDATE 1
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
psql:PREPARE-BUG-master.sql:72: ERROR: there is no parameter $3
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

execute cnt('A','PGXC');
count
-------
1
(1 row)

execute del('A','PGXC');
DELETE 1
execute ins('A','PGXC');
INSERT 0 1
execute upd('c1','c2','A','PGXC');
psql:PREPARE-BUG-master.sql:78: ERROR: there is no parameter $3
execute sel('A','PGXC');
c2 | c1
----+----
c2 | c1
(1 row)

----8<---- (rel1.0) -----------8<----------------------
$ createdb -p 50004 -h node06 db
$ psql -a -p 50004 -h node06 -f PREPARE-BUG-REL1_0.sql db
-- craete table
CREATE TABLE T1 (
C1 varchar,
C2 varchar,
C3 varchar,
C4 varchar,
C5 date
)
DISTRIBUTE BY HASH(C3);
CREATE TABLE

-- select pgxc_nodes
SELECT * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord1 | C | 50004 | node06 | f | f | 1885696643
coord2 | C | 50005 | node07 | f | f | -1197102633
coord3 | C | 50004 | node08 | f | f | 1638403545
coord4 | C | 50005 | node09 | f | f | -890157977
datanode1 | D | 50008 | node06 | f | t | 888802358
datanode2 | D | 50009 | node07 | f | f | -905831925
datanode3 | D | 50008 | node08 | f | f | -1894792127
datanode4 | D | 50009 | node09 | f | f | -1307323892
(8 rows)

-- insert
INSERT INTO T1 VALUES ( 'c1' , 'c2' , 'A' , 'PGXC' , '2013-01-01');
INSERT 0 1

-- prepare
PREPARE cnt (VARCHAR, VARCHAR) AS
SELECT COUNT(*) FROM T1 WHERE C3 = $1 AND C4 = $2;
PREPARE

-- select count t1
SELECT COUNT(*) FROM T1 WHERE C3='A' AND C4='PGXC';
count
-------
1
(1 row)

-- execute cnt
execute cnt('A','PGXC');
count
-------
4
(1 row)
------>8------------------------>8------------------

[Koichi Suzuki]

[Koichi Suzuki]