Test code for ResultSet numeric getXXX methods

Hi,

The attached patch adds a test case that tests all combinations of
various ResultSet getXXX (byte, short, int, long, float, double,
bigdecimal) work properly against various (int2, int4, int8, float4,
float8, numeric, varchar) column types.

The current codebase works nicely with all combinations but the
difference becomes more significant when (if) the binary transfer
patches are applied.

The patch also modifies v3/ConnectionFactoryImpl to request that the
server sends floating point values with full precision instead of
chopping off two digits (which is the default setting). The setting
is first mentioned in postgresql 7.4.0 release notes so it should be
supported by all v3 protocol capable servers.

The test case loops the same tests a few times in anticipation of the
binary transfer patches because the first few queries will still go
through the text protocol. The looping is quite ugly to be added to all
test cases but this is the most important test case for binary result
set transfers which is why I think it's justified here.

diff -urN --exclude=CVS --exclude=build pgjdbc.clean/org/postgresql/core/v3/ConnectionFactoryImpl.java pgjdbc/org/postgresql/core/v3/ConnectionFactoryImpl.java
--- pgjdbc.clean/org/postgresql/core/v3/ConnectionFactoryImpl.java 2007-02-28 08:11:00.000000000 +0200
+++ pgjdbc/org/postgresql/core/v3/ConnectionFactoryImpl.java 2007-07-20 04:14:47.000000000 +0300
@@ -85,7 +85,8 @@
{ "user", user },
{ "database", database },
{ "client_encoding", "UNICODE" },
- { "DateStyle", "ISO" }
+ { "DateStyle", "ISO" },
+ { "extra_float_digits", "2" },
};

sendStartupPacket(newStream, params, logger);
diff -urN --exclude=CVS --exclude=build pgjdbc.clean/org/postgresql/test/jdbc2/Jdbc2TestSuite.java pgjdbc/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
--- pgjdbc.clean/org/postgresql/test/jdbc2/Jdbc2TestSuite.java 2005-08-01 09:54:15.000000000 +0300
+++ pgjdbc/org/postgresql/test/jdbc2/Jdbc2TestSuite.java 2007-07-20 04:02:35.000000000 +0300
@@ -47,6 +47,7 @@
suite.addTestSuite(ResultSetMetaDataTest.class);
suite.addTestSuite(ArrayTest.class);
suite.addTestSuite(RefCursorTest.class);
+ suite.addTestSuite(TestNumericOverflows.class);

// Time, Date, Timestamp
suite.addTestSuite(DateTest.class);
diff -urN --exclude=CVS --exclude=build pgjdbc.clean/org/postgresql/test/jdbc2/TestNumericOverflows.java pgjdbc/org/postgresql/test/jdbc2/TestNumericOverflows.java
--- pgjdbc.clean/org/postgresql/test/jdbc2/TestNumericOverflows.java 1970-01-01 02:00:00.000000000 +0200
+++ pgjdbc/org/postgresql/test/jdbc2/TestNumericOverflows.java 2007-07-20 22:56:34.000000000 +0300
@@ -0,0 +1,544 @@
+/*-------------------------------------------------------------------------
+*
+* Copyright (c) 2004-2007, PostgreSQL Global Development Group
+*
+* IDENTIFICATION
+* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/GetXXXTest.java,v 1.5 2005/01/11 08:25:48 jurka Exp $
+*
+*-------------------------------------------------------------------------
+*/
+package org.postgresql.test.jdbc2;
+
+import java.math.BigDecimal;
+import java.sql.*;
+import java.util.*;
+import junit.framework.TestCase;
+import org.postgresql.test.TestUtil;
+
+/**
+ * Tests various ResultSet getXXX methods for numeric values against different
+ * database column types. Tests that when required overflow errors are properly
+ * detected or ignored (float/double) or that some numbers are properly
+ * truncated.
+ *
+ * @author Mikko Tiihonen
+ */
+public class TestNumericOverflows extends TestCase
+{
+ private Connection con;
+
+ private PreparedStatement pstmt;
+
+ private int round;
+
+ private boolean mustFail;
+ private boolean mustRound;
+ private boolean mustMatch;
+
+ private static String[][] OKVALUES = {
+ // values that fit into int1
+ {
+ String.valueOf(0),
+ String.valueOf(Byte.MAX_VALUE),
+ String.valueOf(Byte.MIN_VALUE),
+ },
+ // values that fit into int2
+ {
+ String.valueOf(Short.MAX_VALUE),
+ String.valueOf(Short.MIN_VALUE),
+ },
+ // values that fit into int4
+ {
+ String.valueOf(Integer.MAX_VALUE),
+ String.valueOf(Integer.MIN_VALUE),
+ },
+ // values that fit into int8
+ {
+ String.valueOf(Long.MAX_VALUE),
+ String.valueOf(Long.MIN_VALUE),
+ },
+ // values that fit into float4
+ {
+ String.valueOf(1 << 24),
+ String.valueOf(-(1 << 24)),
+ String.valueOf(Float.NaN),
+ String.valueOf(Float.NEGATIVE_INFINITY),
+ String.valueOf(Float.POSITIVE_INFINITY),
+ // normalised min/max values
+ "3.4E38",
+ "-3.4E38",
+ "1.1754945E-38",
+ "-1.1754945E-38",
+ // denormalised min/max values
+// BUG or not? backend seems to only accept normalised values
+// see: http://en.wikipedia.org/wiki/IEEE_754#Single-precision_32_bit pt 7-9
+// String.valueOf(Float.MAX_VALUE),
+// String.valueOf(-Float.MAX_VALUE),
+// String.valueOf(Float.MIN_VALUE),
+// String.valueOf(-Float.MIN_VALUE),
+ },
+ // values that fit into float8
+ {
+ String.valueOf(1L << 53),
+ String.valueOf(-(1L << 53)),
+ String.valueOf(Double.NaN),
+ String.valueOf(Double.NEGATIVE_INFINITY),
+ String.valueOf(Double.POSITIVE_INFINITY),
+ String.valueOf(Double.MAX_VALUE),
+ String.valueOf(-Double.MAX_VALUE),
+ // normalised min/max values
+ "1.7976931348623157E308",
+ "-1.7976931348623157E308",
+ "2.22507386E-308",
+ "-2.22507386E-308",
+ // actual min/max values
+// String.valueOf(Double.MIN_VALUE),
+// String.valueOf(-Double.MIN_VALUE),
+ },
+ };
+
+ private static String[][] FAILVALUES = {
+ // values that just barely wont fit into int1
+ {
+ String.valueOf(Byte.MAX_VALUE + 1),
+ String.valueOf(Byte.MIN_VALUE - 1),
+ },
+ // values that just barely wont fit into int2
+ {
+ String.valueOf(Short.MAX_VALUE + 1),
+ String.valueOf(Short.MIN_VALUE - 1),
+ },
+ // values that just barely wont fit into int4
+ {
+ String.valueOf(Integer.MAX_VALUE + 1L),
+ String.valueOf(Integer.MIN_VALUE - 1L),
+ },
+ // values that just barely wont fit into int8
+ {
+ "9223372036854775809",
+ "-9223372036854775810",
+ },
+ };
+
+
+ private static final String[] ROUNDVALUES = {
+ "0.1",
+ "-0.1",
+ "3.5",
+ "-3.5",
+ };
+
+ private static final String[][] OVERFLOWVALUES = {
+ // values that wont fit into float4
+ {
+ String.valueOf((1L << 53) + 1L),
+ String.valueOf(-(1L << 53) - 1L),
+ },
+ {
+ "3.4028236E38",
+ "-3.4028236E38",
+ "3.4028235E39",
+ "-3.4028235E39",
+ "0.9E-45",
+ "1.4e-46",
+ },
+ // values that wont fit into float8
+ {
+ "1.8976931348623157e+308",
+ "-1.8976931348623157e+308",
+ "1.7976931348623157e+309",
+ "-1.7976931348623157e+309",
+ "0.9e-324",
+ "-4.9e-324",
+ "0.9e-325",
+ "-4.9e-325",
+ }
+ };
+
+ public TestNumericOverflows(String name )
+ {
+ super(name);
+ }
+
+ protected void setUp() throws Exception
+ {
+ con = TestUtil.openDB();
+ mustFail = false;
+ mustRound = false;
+ mustMatch = true;
+ }
+
+ protected void tearDown() throws Exception
+ {
+ if (pstmt != null) {
+ pstmt.close();
+ }
+ TestUtil.dropTable( con, "test_overflow" );
+ TestUtil.closeDB(con);
+ }
+
+ /**
+ * Tests that valid byte value is read from different type columns.
+ */
+ public void testByte() throws SQLException
+ {
+ String[] types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(4)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[0], types[i], Byte.class);
+ }
+ }
+
+ /**
+ * Tests that valid short value is read from different type columns.
+ */
+ public void testShort() throws SQLException
+ {
+ String[] types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(6)"};
+ for (int i=0; i<types.length; ++i) {
+ run(append(OKVALUES[0], OKVALUES[1]), types[i], Short.class);
+ }
+ }
+
+ /**
+ * Tests that valid int value is read from different type columns.
+ */
+ public void testInt() throws SQLException
+ {
+ String[] types = new String[]{"int4", "int8", "float8", "numeric", "varchar(11)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[2], types[i], Integer.class);
+ }
+
+ types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(6)"};
+ for (int i=0; i<types.length; ++i) {
+ run(append(OKVALUES[0], OKVALUES[1]), types[i], Integer.class);
+ }
+ }
+
+ /**
+ * Tests that valid long value is read from different type columns.
+ */
+ public void testLong() throws SQLException
+ {
+ String[] types = new String[]{"int8", "numeric", "varchar(20)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[3], types[i], Long.class);
+ }
+
+ types = new String[]{"int4", "int8", "float8", "numeric", "varchar(11)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[2], types[i], Long.class);
+ }
+
+ types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(6)"};
+ for (int i=0; i<types.length; ++i) {
+ run(append(OKVALUES[0], OKVALUES[1]), types[i], Long.class);
+ }
+ }
+
+ /**
+ * Tests that valid float value is read from different type columns.
+ */
+ public void testFloat() throws SQLException
+ {
+ String[] types = new String[]{"float4", "float8", "varchar(14)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[4], types[i], Float.class);
+ }
+
+ types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(6)"};
+ for (int i=0; i<types.length; ++i) {
+ run(append(OKVALUES[0], OKVALUES[1]), types[i], Float.class);
+ }
+ }
+
+ /**
+ * Tests that valid double value is read from different type columns.
+ */
+ public void testDouble() throws SQLException
+ {
+ String[] types = new String[]{"float8", "varchar(23)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[5], types[i], Double.class);
+ }
+
+ types = new String[]{"float8", "varchar(14)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[4], types[i], Double.class);
+ }
+
+ types = new String[]{"int4", "int8", "float8", "numeric", "varchar(11)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OKVALUES[2], types[i], Double.class);
+ }
+
+ types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(6)"};
+ for (int i=0; i<types.length; ++i) {
+ run(append(OKVALUES[0], OKVALUES[1]), types[i], Double.class);
+ }
+ }
+
+ /**
+ * Tests that column whose value does not fit into byte fails.
+ */
+ public void testByteOverflow() throws SQLException
+ {
+ mustFail = true;
+ String[] types = new String[]{"int2", "int4", "int8", "float4", "float8", "numeric", "varchar(4)"};
+ for (int i=0; i<types.length; ++i) {
+ run(FAILVALUES[0], types[i], Byte.class);
+ }
+ }
+
+ /**
+ * Tests that column whose value does not fit into short fails.
+ */
+ public void testShortOverflow() throws SQLException
+ {
+ mustFail = true;
+ String[] types = new String[]{"int4", "int8", "float4", "float8", "numeric", "varchar(6)"};
+ for (int i=0; i<types.length; ++i) {
+ run(FAILVALUES[1], types[i], Short.class);
+ }
+ }
+
+ /**
+ * Tests that column whose value does not fit into int fails.
+ */
+ public void testIntOverflow() throws SQLException
+ {
+ mustFail = true;
+ String[] types = new String[]{"int8", "float8", "numeric", "varchar(11)"};
+ for (int i=0; i<types.length; ++i) {
+ run(FAILVALUES[2], types[i], Integer.class);
+ }
+ }
+
+ /**
+ * Tests that column whose value does not fit into long fails.
+ */
+ public void testLongOverflow() throws SQLException
+ {
+ mustFail = true;
+ String[] types = new String[]{"numeric", "varchar(23)"};
+ for (int i=0; i<types.length; ++i) {
+ run(FAILVALUES[3], types[i], Long.class);
+ }
+ }
+
+ /**
+ * Tests that float value from any too large too small number does not throw
+ * exceptions but probably just gives a meaningless answer.
+ */
+ public void testFloatNoOverflow() throws SQLException
+ {
+ mustMatch = false;
+ String[] types = new String[]{"int8", "float8", "numeric", "varchar(17)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OVERFLOWVALUES[0], types[i], Float.class);
+ }
+ types = new String[]{"float8", "numeric", "varchar(14)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OVERFLOWVALUES[1], types[i], Float.class);
+ }
+ }
+
+ /**
+ * Tests that double value from any too large too small number does not throw
+ * exceptions but probably just gives a meaningless answer.
+ */
+ public void testDoubleNoOverflow() throws SQLException
+ {
+ mustMatch = false;
+ String[] types = new String[]{"numeric", "varchar(24)"};
+ for (int i=0; i<types.length; ++i) {
+ run(OVERFLOWVALUES[2], types[i], Double.class);
+ }
+ }
+
+ private void testRounding(Class getType) throws SQLException
+ {
+ mustRound = true;
+ String[] types = new String[]{"float4", "float8", "numeric", "varchar(4)"};
+ for (int i=0; i<types.length; ++i) {
+ run(ROUNDVALUES, types[i], getType);
+ }
+ }
+
+ /**
+ * Tests that byte from decimal number returns the integer part.
+ */
+ public void testByteRounding() throws SQLException
+ {
+ testRounding(Byte.class);
+ }
+
+ /**
+ * Tests that short from decimal number returns the integer part.
+ */
+ public void testShortRounding() throws SQLException
+ {
+ testRounding(Short.class);
+ }
+
+ /**
+ * Tests that int from decimal number returns the integer part.
+ */
+ public void testIntRounding() throws SQLException
+ {
+ testRounding(Integer.class);
+ }
+
+ /**
+ * Tests that long from decimal number returns the integer part.
+ */
+ public void testLongRounding() throws SQLException
+ {
+ testRounding(Long.class);
+ }
+
+ private String[] append(String[] v1, String[] v2) {
+ ArrayList res = new ArrayList();
+ res.addAll(Arrays.asList(v1));
+ res.addAll(Arrays.asList(v2));
+ return (String[]) res.toArray(new String[res.size()]);
+ }
+
+ private void run(String[] values, String columnType, Class getType) throws SQLException {
+ TestUtil.createTempTable(con, "test_overflow", "key int2, val " +
+ columnType);
+ insertValues(values, columnType);
+
+ pstmt = con.prepareStatement("select key, val from test_overflow order by key");
+ for (round = 0; round< TestUtil.getPrepareThreshold() + 1; ++round) {
+ checkResults(values, columnType, getType);
+ }
+ }
+
+ private void checkResults(String[] values, String columnType, Class type)
+ throws SQLException {
+
+ ResultSet rs = pstmt.executeQuery();
+ while (rs.next())
+ {
+ int key = rs.getInt(1);
+
+ if (mustFail) {
+ checkFail(values[key], columnType, type, rs);
+ continue;
+ } else {
+ if (mustRound) {
+ checkRoundedBasicType(values[key], columnType, type, rs);
+ continue;
+ } else {
+ checkBasicType(values[key], columnType, type, rs);
+ }
+ }
+
+ if (!mustMatch) {
+ continue;
+ }
+
+ BigDecimal correct;
+ try {
+ correct = new BigDecimal(values[key]);
+ } catch (NumberFormatException ex) {
+ // it's +-infinite or nan, and already checked by previous test
+ try {
+ rs.getBigDecimal(2);
+ fail("Did not fail when requesting invalid big decimal");
+ } catch (SQLException e) {
+ // ok
+ }
+ continue;
+ }
+ BigDecimal val = rs.getBigDecimal(2);
+ correct = correct.setScale(350);
+ val = val.setScale(350);
+
+ assertEquals("R" + round + ". Inexact match for '" + values[key] +
+ "' stored in column '" + columnType+ "'",
+ correct, val);
+ }
+ }
+
+ private void checkBasicType(String value, String columnType, Class type, ResultSet rs) throws SQLException {
+ if (type == Byte.class) {
+ assertEquals("R" + round + ". Inexact match for '" + value +
+ "' stored in column '" + columnType + "'", Byte.parseByte(value), rs.getByte(2));
+ } else if (type == Short.class) {
+ assertEquals("R" + round + ". Inexact match for '" + value +
+ "' stored in column '" + columnType + "'", Short.parseShort(value), rs.getShort(2));
+ } else if (type == Integer.class) {
+ assertEquals("R" + round + ". Inexact match for '" + value +
+ "' stored in column '" + columnType + "'", Integer.parseInt(value), rs.getInt(2));
+ } else if (type == Long.class) {
+ assertEquals("R" + round + ". Inexact match for '" + value +
+ "' stored in column '" + columnType + "'", Long.parseLong(value), rs.getLong(2));
+ } else if (type == Float.class) {
+ assertEquals("R" + round + ". Inexact match for '" + value +
+ "' stored in column '" + columnType + "'", Float.parseFloat(value), rs.getFloat(2));
+ } else if (type == Double.class) {
+ assertEquals("R" + round + ". Inexact match for '" + value +
+ "' stored in column '" + columnType + "'", Double.parseDouble(value), rs.getDouble(2));
+ } else {
+ fail("Unsupported type");
+ }
+ }
+
+ private void checkRoundedBasicType(String value, String columnType, Class type, ResultSet rs) throws SQLException {
+ value = value.substring(0, value.indexOf('.'));
+ checkBasicType(value, columnType, type, rs);
+ }
+
+ private void checkFail(String value, String columnType, Class type, ResultSet rs) throws SQLException {
+ try {
+ BigDecimal val = null;
+ if (type == Byte.class) {
+ byte v = rs.getByte(2);
+ val = BigDecimal.valueOf(v);
+ } else if (type == Short.class) {
+ short v = rs.getShort(2);
+ val = BigDecimal.valueOf(v);
+ } else if (type == Integer.class) {
+ int v = rs.getInt(2);
+ val = BigDecimal.valueOf(v);
+ } else if (type == Long.class) {
+ long v = rs.getLong(2);
+ val = BigDecimal.valueOf(v);
+ } else if (type == Float.class) {
+ float v = rs.getFloat(2);
+ val = BigDecimal.valueOf(v);
+ } else if (type == Double.class) {
+ double v = rs.getDouble(2);
+ val = BigDecimal.valueOf(v);
+ } else {
+ fail("Unsupported type");
+ }
+ fail("R" + round + ". Value of '" + val +
+ "' instead of overflow exception for '" + value +
+ "' stored in column '" + columnType.toUpperCase() +
+ "' when converting to " + type.getSimpleName());
+ } catch (SQLException ex) {
+ // the method is supposed to check that the getXXX throws an error
+ }
+ }
+
+ private void insertValues(String[] values, String columnType) throws SQLException {
+ Statement stmt = con.createStatement();
+ for (int i=0; i<values.length; ++i) {
+ int res;
+ try {
+ res =
+ stmt.executeUpdate("insert into test_overflow values (" + i +
+ ", '" + values[i] + "')");
+ } catch (SQLException e) {
+ fail("Failed to insert '" + values[i] + "' into column type '" +
+ columnType + "': " + e.getMessage());
+ return;
+ }
+ assertEquals(1, res);
+ }
+ stmt.close();
+ }
+}