ResultSet.updateRow causes "syntax error at end of input" when no primary key

[davecramer]

Reported by

Tomas Janco tomas.janco@myinphinity.com

I have found following problem with JDBC driver:
When an updatable result set is created for a table without primary key, any update fails with error: "syntax error at end of input"
The driver generates invalid SQL query to update the table.

JDBC driver version: 42.2.18 (jre8)
Server version: PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestPgSql {
/* test table schema:
CREATE TABLE public.sample
(
id integer,
value character varying(255) COLLATE pg_catalog."default"
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
INSERT INTO public.sample(id, value)
VALUES (1, 'abcd');
*/

public static void main(String args[]) throws Exception {
    Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/...?user=....&password=....");
    String sql = "SELECT * FROM sample WHERE id = 1;";
    PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_FORWARD_ONLY);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    rs.updateString("value", "something");
    rs.updateRow();
    rs.close();
}

}

Expected behavior:
The code successfully updates the table OR throws an error explaining primary key is not present in result set and is required for updatable result set.

Actual behavior:
Incorrect SQL command is generated internally: "UPDATE sample SET "value" = $1 WHERE "
The query is missing the WHERE condition expression.
This results in following exception being thrown:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
at org.postgresql.jdbc.PgResultSet.updateRow(PgResultSet.java:1445)
at TestPgSql.main(TestPgSql.java:35)

[Fzhlib]

Hi dave

I also found this problem.
Is it too late to throw this exception in " SQL Command generate" step?
How about throw this error in PgResultSet.isUpdateable(), and throw
No primary key found for table
instead of
syntax error at end of input

And here is my solution:
at PgResultSet.java : 1670
updateable = (i == numPKcolumns);
change to
updateable = (i == numPKcolumns) & (numPKcolumns > 0);
Make sure it have defined one or more primary keys in table.

[davecramer]

makes sense, can you provide a PR ?

[Fzhlib]

yes,i'm glad to do this.