Thread: BUG #16051: PQgetvalue(res,0,0) is null, PQgetlength(res,0,0) is 10

The following bug has been logged on the website:

Bug reference:      16051
Logged by:          Libpq does not fetch first result in table
Email address:      bartsmink@gmail.com
PostgreSQL version: 12.0
Operating system:   Macos 10.15
Description:

Hi,

I'm running unit tests in doctest and cannot make a simple test succeed. The
code is below:

SUBCASE("TEST first column first row value is stored in db"){
        res = PQexec(conn, "CREATE TABLE \"Schema1\".testdata\
        (\
            \"Time\" decimal,\
            \"RAW\" bytea,\
            \"EPC\" bytea,\
            \"RSSI\" bytea,\
            \"PC\" bytea);\
            ALTER TABLE \"Schema1\".testdata\
            OWNER to sab24;"
        );
        CHECK(PQresultStatus(res) == PGRES_COMMAND_OK);
        if (PQresultStatus(res) != PGRES_COMMAND_OK){
            fprintf(stderr, "CREATE TABLE FAILED: %s",
PQerrorMessage(conn));
            PQclear(res);
        } 
        res = PQexec(conn, "INSERT INTO \"Schema1\".testdata values(1234,
'abc', 'abc', 'abc', 'abc');");
        CHECK(PQresultStatus(res) == PGRES_COMMAND_OK);
        if (PQresultStatus(res) != PGRES_COMMAND_OK){
            fprintf(stderr, "CREATE TABLE FAILED: %s",
PQerrorMessage(conn));
            PQclear(res);
        } 

        res = PQexecParams(conn, 
        "SELECT * FROM testdata;",
        0,
        NULL,
        NULL,
        NULL,
        NULL,
        1);

        CHECK(PQresultStatus(res) == PGRES_TUPLES_OK);
        if (PQresultStatus(res) != PGRES_TUPLES_OK){
            fprintf(stderr, "SELECT TABLE FAILED: %s",
PQerrorMessage(conn));
            PQclear(res);
        } 
        else{
            std::cout << "value is: " << PQgetvalue(res, 0,0) << " length
is: " << PQgetlength(res,0,0) << std::endl;
            REQUIRE(strcmp(PQgetvalue(res, 0,0), "1234") == 0);
        }
    }



Output is:


value is:  length is: 10
===============================================================================
../tests/dbPostgres.cpp:39:
TEST CASE:  Database related tests
  TEST first column first row value is stored in db

../tests/dbPostgres.cpp:242: FATAL ERROR: REQUIRE( strcmp(PQgetvalue(res,
0,0), "1234") == 0 ) is NOT correct!
  values: REQUIRE( -49 == 0 )

===============================================================================
[doctest] test cases:      2 |      1 passed |      1 failed |      0
skipped
[doctest] assertions:     32 |     31 passed |      1 failed |
[doctest] Status: FAILURE!

PG Bug reporting form <noreply@postgresql.org> writes:
> I'm running unit tests in doctest and cannot make a simple test succeed.

Right offhand, I'd wonder about why your test is creating a table
with the fully qualified name \"Schema1\".testdata and then
querying it without the schema qualification.

            regards, tom lane

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG>         res = PQexecParams(conn, 
 PG>         "SELECT * FROM testdata;",
 PG>         0,
 PG>         NULL,
 PG>         NULL,
 PG>         NULL,
 PG>         NULL,
 PG>         1);

That "1" on the end there indicates that you are requesting binary
format results. But this:

 PG> std::cout << "value is: " << PQgetvalue(res, 0,0) << " length
 PG> is: " << PQgetlength(res,0,0) << std::endl;
 PG> REQUIRE(strcmp(PQgetvalue(res, 0,0), "1234") == 0);

is assuming text format.

The binary result for numeric '1234' will be 10 bytes long and consist
of: 00 01 00 00 00 00 00 00 04 d2:

  0001 = ndigits (meaning: 1 base-10000 digit)
  0000 = weight (first digit is * 10000^0)
  0000 = sign (0 = positive)
  0000 = dscale (0 base-10 digits after the point)
  04d2 = decimal 1234

-- 
Andrew (irc:RhodiumToad)

Bart Smink <bartsmink@gmail.com> writes:
> Thanks for taking a look at my code. The difference is because this table
> will be created once by a setup script that connects without the
> search_path option selecting a scheme  in connect, whereas the program that
> will run on this table connects with a search_path option. Changing the
> command to

> res = PQexecParams(conn,
>             "SELECT * FROM \"Schema1\".testdata;",
>             0,
>             NULL,
>             NULL,
>             NULL,
>             NULL,
>             1
>         );

> makes no difference, the test still fails.

[ looks closer... ]  Oh!  You are asking for binary result format
(resultFormat = 1 in the call), so what you get back is not a
pointer to the string "1234", it's a pointer to whatever the
defined binary format for a numeric is.  That likely begins with
a zero byte, causing your program to think it's an empty string.

            regards, tom lane