Postgre driver(9.06.04.00) connected to Amazon redshift, is returning wrong count in SQLRowCount

From: "Asif Shaikh" <asif(dot)shaikh(at)in(dot)ibm(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Postgre driver(9.06.04.00) connected to Amazon redshift, is returning wrong count in SQLRowCount
Date: 2017-08-16 07:08:38
Message-ID: OF25DAC01C.0F2250C7-ON0025817E.002725F7-6525817E.00273CA5@notes.na.collabserv.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

I am Asif from IBM and need an urgent help on following.

We are facing an issue with postgre driver(9.06.04.00) connecting to
Amazon Redshift database.
We are executing the following query
SELECT DISTINCT customer_id INTO <New_Table_NAME> FROM
<Existing_Table_Name>.

The query gets executed properly and the table also gets created.
The problem here is that postgre driver returns wrong count(affected
number of rows) for this query as output paramter in SQLRowCount.
The sequence of driver calls are as follows
SQLDriverConnect - to connect to driver
SQLGetInfo (hDBC, SQL_ODBC_API_CONFORMANCE, (PTR)&confLevel, sizeof
(confLevel), NULL); - To get the conformance level of database.
SQLGetInfo(hDBC, SQL_CURSOR_COMMIT_BEHAVIOR, (PTR)&cursorCB, sizeof
(cursorCB), NULL); - To get the commit behavior for database.
SQLGetInfo(hDBC, SQL_TXN_CAPABLE, (PTR)&txnSupport, sizeof(txnSupport),
NULL); - To get the transaction support information for database.
SQLGetInfo(hDBC, SQL_MAX_CONCURRENT_ACTIVITIES, (PTR)&iMaxConcurrent,
sizeof(iMaxConcurrent), NULL); - To get the maximum concurrent statement
information

The output of above 4 commands is as below(mentioned in bold). These may
not be important for this question but mentioning it just in case if any
output has any relation with the issue we are facing.
Server <DSN NAME> conforms to LEVEL 1.
Server's cursor commit behavior: PRESERVE
Transactions supported: ALL
Maximum number of concurrent statements: 0

Finally the following query is executed using SQLExecDirect
SELECT DISTINCT customer_id INTO <New_Table_NAME> FROM
<Existing_Table_Name>.
After executing this query, we check SQLNumResultCols(hStmt,
&nResultCols); In this case, the result count is 0 which is expected as
its not a normal select query.
Finally we call SQLRowCount API which doesn't modify the rowcount variable
passed to it as output parameter whereas the same variable gets the
correct value stored for other databases which supports such statement
e.g. SQL server or even for update/insert this works fine.

Because of this wrong count, our product is facing some blocking issues
and it is delaying our release.
The above driver calls sequence is from our general tool for odbc
connection test using which also we could reproduce the issue.

Our applications including the odbc connection tester are 64 bit
applications and we are facing this issue on Windows, RHEL and AIX
Please help us rectify the issue asap.

Thanks & Regards
Asif G. Shaikh

Browse pgsql-bugs by date

  From Date Subject
Next Message Augustine, Jobin 2017-08-16 11:09:16 Re: [HACKERS] Replication to Postgres 10 on Windows is broken
Previous Message sogawa 2017-08-16 01:47:04 BUG #14780: PostgreSQL 9.6 selects a wrong plan during aggregation against timestamp columns