[BUGS] BUG? select count(*) from table don't get value via PQgetvalue() function in libpq(C) - Mailing list pgsql-bugs

Hi, postgresql:

       First, my englist is not good. So I try to my best. Hope you can understand me.

       I programing a program use linux via C language. And I used libpq connect to postgresql.

       Then I want calculate rows in a table. So I write PQexecparams() perfrom sql statement: select count(*) from tablename.

Like this:

    PGresult *res;

    PQtrace(pgconn, stdout);

    res = PQexecParams(pgconn, "select count(*) from chat_connstate", 0, NULL, NULL, NULL, NULL, 1);

Then I check res variable use

PQresultStatus(res) == PGRES_TUPLES_OK

       that the status is successed.

So, I use PQgetvalue() function get the value, like this:

       Int result = *((int*)PQgetvalue(res, 0, 0));

       Or

       Long result = *((long*)PQgetvalue(res, 0, 0);

But, the result variable value is zero, the I use PQfname(res, 0) and PQfnumber(res, “count”) check status that output is corrected.

So, I change my code to:

       Int result = *((int*)PQgetvalue(res, 0, PQfnumber(res, “count”);

But, the result value  still is zero.

I login postgresql use psql, then perfrom select count(*) from tablename. The output is current, like:

       chatdb=# select count(*) from chat_connstate;

       count

-------

          1

(1 行记录)

Then I perfrom “select * from tablename” in PQexecparams, and get the values. The values is currect this time.

So, I test

       Select sum(*) from tablename;

       Int result = *((int*)PQgetvalue(res, 0, 0);   //the result value is zero

       Select avg(*) from tablename;

       Double result = *((double*)PQgetvalue(res, 0, 0); // the result value is not zero, but still is not equal to psql output.

Finally, I have no ideas for new test way. Please help me.

Thanks.

output infomations use psql and PQtrace() function in gdb:  (The system and postgresql environment in the bottom.)

chatdb=# select * from chat_connstate;

id |                       ip                       | port | connfd | account |                  token                   |          expires           |        create_date        

----+------------------------------------------------+------+--------+---------+------------------------------------------+----------------------------+----------------------------

  1 | 127.0.0.1                                      |    1 |      7 |         | 34a57ff275715c87235bf880f4c642d2539372fc | 2017-02-18 22:33:55.381506 | 2017-02-18 22:33:55.381506

(1 行记录)

1. Select count(*) from chat_connstate

(gdb)

41       chat_psql_init_connstate(1);

(gdb) s

chat_psql_init_connstate (clear=1) at chat_psql.c:38

38       PQtrace(pgconn, stdout);

(gdb) n

39       res = PQexecParams(pgconn, "select count(*) from chat_connstate",

(gdb)

To backend> Msg P

To backend> ""

To backend> "select count(*) from chat_connstate"

To backend (2#)> 0

To backend> Msg complete, length 44

To backend> Msg B

To backend> ""

To backend> ""

To backend (2#)> 0

To backend (2#)> 0

To backend (2#)> 1

To backend (2#)> 1

To backend> Msg complete, length 15

To backend> Msg D

To backend> P

To backend> ""

To backend> Msg complete, length 7

To backend> Msg E

To backend> ""

To backend (4#)> 0

To backend> Msg complete, length 10

To backend> Msg S

To backend> Msg complete, length 5

From backend> 1

From backend (#4)> 4

From backend> 2

From backend (#4)> 4

From backend> T

From backend (#4)> 30

From backend (#2)> 1

From backend> "count"

From backend (#4)> 0

From backend (#2)> 0

From backend (#4)> 20

From backend (#2)> 8

From backend (#4)> -1

From backend (#2)> 1

From backend> D

From backend (#4)> 18

From backend (#2)> 1

From backend (#4)> 8

From backend (8)>

From backend> C

From backend (#4)> 13

From backend> "SELECT 1"

From backend> Z

From backend (#4)> 5

From backend> Z

From backend (#4)> 5

From backend> I

43       if(chat_psql_resultstate(res, PGRES_TUPLES_OK) == CHAT_ERROR){

(gdb) n

49       int result = *((int*)PQgetvalue(res, 0, 0));

(gdb) n

50       PQuntrace(pgconn);

(gdb) print result

$111 = 0

---

chatdb=# select count(*) from chat_connstate;

count

-------

     1

2

41       chat_psql_init_connstate(1);

(gdb) s

chat_psql_init_connstate (clear=1) at chat_psql.c:38

38       PQtrace(pgconn, stdout);

(gdb) n

39       res = PQexecParams(pgconn, "select sum(connfd) from chat_connstate",

(gdb) n

And system environment:

sudo lsb_release -a

[sudo] password for ubuntu:

No LSB modules are available.

Distributor ID: Ubuntu

Description:   Ubuntu 14.04.5 LTS

Release:  14.04

Codename:    trusty

uname -a

Linux ubuntu-VB 4.4.0-62-generic #83~14.04.1-Ubuntu SMP Wed Jan 18 18:10:30 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Postgresql environment

Postgresql-9.5 postgresql-contrib-9.5 libpq-dev from apt-get

Dpkg -s postgresql-9.6 infomations:

ubuntu@ubuntu-VB:~/sdb/chat$ sudo dpkg -s postgresql-9.5

[sudo] password for ubuntu:

Package: postgresql-9.5

Status: install ok installed

Priority: optional

Section: database

Installed-Size: 18411

Maintainer: Debian PostgreSQL Maintainers <pkg-postgresql-public@lists.alioth.debian.org>

Architecture: amd64

Version: 9.5.6-1.pgdg14.04+1

Depends: libc6 (>= 2.16), libgssapi-krb5-2 (>= 1.8+dfsg), libldap-2.4-2 (>= 2.4.7), libpam0g (>= 0.99.7.1), libpq5 (>= 9.2~beta3), libssl1.0.0 (>= 1.0.0), libxml2 (>= 2.7.4), postgresql-client-9.5, postgresql-common (>= 158~), tzdata, ssl-cert, locales

Recommends: postgresql-contrib-9.5, sysstat

Suggests: locales-all

Description: object-relational SQL database, version 9.5 server

PostgreSQL is a powerful, open source object-relational database

system. It is fully ACID compliant, has full support for foreign

keys, joins, views, triggers, and stored procedures (in multiple

languages). It includes most SQL:2008 data types, including INTEGER,

NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It

also supports storage of binary large objects, including pictures,

sounds, or video. It has native programming interfaces for C/C++,

Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and

exceptional documentation.

.

This package provides the database server for PostgreSQL 9.5.

Homepage: http://www.postgresql.org/