in phoenix-core/src/it/java/org/apache/phoenix/end2end/ArithmeticQueryIT.java [431:662]
public void testDecimalArithmeticWithIntAndLong() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.setAutoCommit(false);
try {
String testDecimalArithmetic = generateUniqueName();
String ddl = "CREATE TABLE " + testDecimalArithmetic +
" (pk VARCHAR NOT NULL PRIMARY KEY, " +
"col1 DECIMAL(38,0), col2 DECIMAL(5, 2), col3 INTEGER, col4 BIGINT, col5 DECIMAL)";
createTestTable(getUrl(), ddl);
String query = "UPSERT INTO " + testDecimalArithmetic
+ "(pk, col1, col2, col3, col4, col5) VALUES(?,?,?,?,?,?)";
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, "testValueOne");
stmt.setBigDecimal(2, new BigDecimal("1234567890123456789012345678901"));
stmt.setBigDecimal(3, new BigDecimal("123.45"));
stmt.setInt(4, 10);
stmt.setLong(5, 10L);
stmt.setBigDecimal(6, new BigDecimal("111.111"));
stmt.execute();
conn.commit();
stmt.setString(1, "testValueTwo");
stmt.setBigDecimal(2, new BigDecimal("12345678901234567890123456789012345678"));
stmt.setBigDecimal(3, new BigDecimal("123.45"));
stmt.setInt(4, 10);
stmt.setLong(5, 10L);
stmt.setBigDecimal(6, new BigDecimal("123456789.0123456789"));
stmt.execute();
conn.commit();
// INT has a default precision and scale of (10, 0)
// LONG has a default precision and scale of (19, 0)
query = "SELECT col1 + col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
BigDecimal result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1234567890123456789012345678911"), result);
query = "SELECT col1 + col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1234567890123456789012345678911"), result);
query = "SELECT col2 + col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("133.45"), result);
query = "SELECT col2 + col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("133.45"), result);
query = "SELECT col5 + col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("121.111"), result);
query = "SELECT col5 + col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("121.111"), result);
query = "SELECT col1 - col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1234567890123456789012345678891"), result);
query = "SELECT col1 - col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1234567890123456789012345678891"), result);
query = "SELECT col2 - col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("113.45"), result);
query = "SELECT col2 - col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("113.45"), result);
query = "SELECT col5 - col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("101.111"), result);
query = "SELECT col5 - col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("101.111"), result);
query = "SELECT col1 * col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1.234567890123456789012345678901E+31"), result);
query = "SELECT col1 * col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1.234567890123456789012345678901E+31"), result);
query = "SELECT col1 * col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1.234567890123456789012345678901E+31"), result);
try {
query =
"SELECT col1 * col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueTwo'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
fail("Should have caught error.");
} catch (SQLException e) {
assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode());
}
try {
query =
"SELECT col1 * col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueTwo'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
fail("Should have caught error.");
} catch (SQLException e) {
assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode());
}
query = "SELECT col4 * col5 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(0, result.compareTo(new BigDecimal("1111.11")));
query = "SELECT col3 * col5 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(0, result.compareTo(new BigDecimal("1111.11")));
query = "SELECT col2 * col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1234.5"), result);
// Result scale has value of 0
query = "SELECT col1 / col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1.2345678901234567890123456789E+29"), result);
query = "SELECT col1 / col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("1.2345678901234567890123456789E+29"), result);
// Result scale is 2.
query = "SELECT col2 / col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("12.34"), result);
query = "SELECT col2 / col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("12.34"), result);
// col5 has NO_SCALE, so the result's scale is not expected to be truncated to col5 value's scale of 4
query = "SELECT col5 / col3 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("11.1111"), result);
query = "SELECT col5 / col4 FROM " + testDecimalArithmetic + " WHERE pk='testValueOne'";
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
assertTrue(rs.next());
result = rs.getBigDecimal(1);
assertEquals(new BigDecimal("11.1111"), result);
} finally {
conn.close();
}
}