Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ResultSet.getString() returns inconsistent results with SQL Type double #1286

Closed
1 of 2 tasks
james-mcguire-lm opened this issue Aug 2, 2018 · 13 comments
Closed
1 of 2 tasks

Comments

@james-mcguire-lm
Copy link

@james-mcguire-lm james-mcguire-lm commented Aug 2, 2018

According to the JDBC docs, getString() can be used to return a SQL Type double. However, in my testing, pgjdbc is not returning a consistent string for a given double value.

  • bug report
  • feature request

Describe the issue
Create a table with a double column. Create a row with a integer value. Use JDBC to query that column 6 times. On the 6th time, a .0 will be appended to the string returned.

I've reproduced with:

  • pgjdbc versions: postgresql-42.2.2.jre6.jar, postgresql-42.2.4.jre6.jar, postgresql-42.2.4.jar
  • Java 1.8.0_172-b11 (and earlier)
  • Windows 7 and Windows 10
  • PostgreSQL 10.3 and 10.4

To Reproduce

  1. Create a table with a double column
CREATE TABLE "test_table" (
    "test_col" double precision NOT NULL
);
  1. Insert a new row with an integer value
INSERT INTO "test_table" ("test_col") VALUES (1);
  1. Use pgjdbc to run a select statement 6 or more times
import java.sql.*;  

public class JdbcSelectTest {   
	public static void main(String[] args) {
		try (
         Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/dbname", "username", "password");
         Statement stmt = conn.createStatement();
		) {
			String strSelect = "select test_col from test_table;";
			PreparedStatement ps;
			ResultSet rset;
			for(int i=0; i<10;i++)
			{
				 ps = conn.prepareStatement(strSelect);
				 rset = ps.executeQuery();
				 while(rset.next()) {  
					String test_col = rset.getString("test_col");
					System.out.println(test_col);
				 }
				 rset.close();
				 ps.close();
			}
		} catch(SQLException ex)
		{
			ex.printStackTrace();
		}
	}
}
  1. Observe inconsistent output:
C:\JavaJDBCTest>java JdbcSelectTest
1
1
1
1
1
1.0
1.0
1.0
1.0
1.0

C:\JavaJDBCTest>

Expected behaviour
GetString() should return a consistent output (ether append the .0 or do not).

@davecramer
Copy link
Member

@davecramer davecramer commented Aug 2, 2018

This is an artifact of switching to binary after the 5th iteration of the statement. I'm pretty sure this has been reported before but I can't find the issue

@james-mcguire-lm
Copy link
Author

@james-mcguire-lm james-mcguire-lm commented Aug 2, 2018

#130 ?

@davecramer
Copy link
Member

@davecramer davecramer commented Aug 2, 2018

@gvatreya
Copy link

@gvatreya gvatreya commented Jun 11, 2019

Is this resolved, We hit this issue recently?
I'm using 42.2.5.jre7 version of the driver.
Any work arounds?

I have patched it locally like below, but uncomfortable

...
String myAttribute = resultSet.getString("myAttribute");
...
// FIXME: A bug in latest JDBC driver after PG 10.6 upgrade returns decimal strings
// Temporary Fix: will always end with ".0" because of floor in SQl
if(myAttribute != null && myAttribute.endsWith(".0")) {
    final BigDecimal decimal = new BigDecimal(myAttribute);
    myAttribute = String.valueOf(decimal.intValue());
}
...

@mshajarrazip
Copy link
Contributor

@mshajarrazip mshajarrazip commented Jul 9, 2019

Output is consistent if we use CONCUR_UPDATABLE for concurrency instead of the default CONCUR_READ_ONLY.

ps = conn.prepareStatement(strSelect, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

@davecramer
Copy link
Member

@davecramer davecramer commented Jul 9, 2019

So I'm curious. You are getting a string from a decimal. Why is this an issue ?

@mshajarrazip
Copy link
Contributor

@mshajarrazip mshajarrazip commented Jul 11, 2019

In org.posgresql.PGProperty.java:94

PREPARE_THRESHOLD("prepareThreshold", "5",
"Statement prepare threshold. A value of {@code -1} stands for forceBinary"),

So, I changed the parameter from "5" to "8", I get the following output:

12345
12345
12345
12345
12345
12345
12345
12345
12345.0
12345.0

Further, in PgResultSet.getString(int) line: 1841:

if (isBinary(columnIndex) && getSQLType(columnIndex) != Types.VARCHAR) { ... }

isBinary() returns true when the output changes at the 8th iteration.

So, actually, it is not an issue? Better change the documentation.

@davecramer
Copy link
Member

@davecramer davecramer commented Jul 11, 2019

@mshajarrazip what it is, is the difference between parsing a string and reading a binary value.

Could you propose documentation that you would consider suitable ?

@mshajarrazip
Copy link
Contributor

@mshajarrazip mshajarrazip commented Jul 12, 2019

@davecramer

"getString() on double type may yield a slightly different representation after 5 query executions when query result switches to binary mode due to default behaviour of server side prepared statements when PreparedStatement API is used."

If necessary, something like this in the documentation for resultset? What do you think?

davecramer pushed a commit that referenced this issue Jul 19, 2019
* docs: add note on behavior of ResultSet.getString() (#1286)

* docs: revise note on behavior of ResultSet.getString() (#1286)

revision based on comment by @bokken
@mshajarrazip
Copy link
Contributor

@mshajarrazip mshajarrazip commented Jul 21, 2019

Should we close this issue?

@soymsk
Copy link

@soymsk soymsk commented Dec 17, 2019

Sorry for adding a comment this. (Please tell me if I should open new one)

I have same issue but with PGPoint values.
JDBC driver properly decodes values in string mode, however, in binary mode it returns null.
Because jdbc driver does not know how to parse PGPoint from binaries.

Here is my environment:

  • pgjdgc: postgresql-9.4-1205-jdbc41.jar
  • Java: 1.8.0_232
  • OS: macOS 10.14.6
  • PostgreSQL: PostgreSQL 11.5 on x86_64-pc-linux-gnu

So Its similar to the case of Double values, but when I execute queries over prepareThreshold it returns like:

(33.3333, 123.333)
(33.3333, 123.333)
(33.3333, 123.333)
(33.3333, 123.333)
(33.3333, 123.333)
(33.3333, 123.333)
null
null
null

Should we add parsing here or is it expected?

Please give me advise, so I may be able to push PR to fix this 😄

@davecramer
Copy link
Member

@davecramer davecramer commented Dec 17, 2019

@james-mcguire-lm Well if you can reproduce this on a current version I'd be interested. 9.4.1205 is very old and not maintained

@soymsk
Copy link

@soymsk soymsk commented Dec 18, 2019

thanks, this version is used by embulk-input-postgresql, anyway I'll try to reproduce by newer version.

davecramer pushed a commit to davecramer/pgjdbc that referenced this issue Jul 5, 2021
…jdbc#1528)

* docs: add note on behavior of ResultSet.getString() (pgjdbc#1286)

* docs: revise note on behavior of ResultSet.getString() (pgjdbc#1286)

revision based on comment by @bokken
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

5 participants