| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.apache.phoenix.end2end; |
| |
| import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertFalse; |
| import static org.junit.Assert.assertNull; |
| import static org.junit.Assert.assertTrue; |
| import static org.junit.Assert.fail; |
| |
| import java.math.BigDecimal; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.util.Properties; |
| |
| import org.apache.phoenix.exception.SQLExceptionCode; |
| import org.apache.phoenix.util.PropertiesUtil; |
| import org.junit.Test; |
| |
| import com.google.common.primitives.Doubles; |
| import com.google.common.primitives.Floats; |
| |
| |
| public class ArithmeticQueryIT extends ParallelStatsDisabledIT { |
| |
| @Test |
| public void testDecimalUpsertValue() 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(31,0), col2 DECIMAL(5), col3 DECIMAL(5,2), col4 DECIMAL)"; |
| createTestTable(getUrl(), ddl); |
| |
| // Test upsert correct values |
| String query = "UPSERT INTO " + testDecimalArithmetic |
| + "(pk, col1, col2, col3, col4) VALUES(?,?,?,?,?)"; |
| PreparedStatement stmt = conn.prepareStatement(query); |
| stmt.setString(1, "valueOne"); |
| stmt.setBigDecimal(2, new BigDecimal("123456789123456789")); |
| stmt.setBigDecimal(3, new BigDecimal("12345")); |
| stmt.setBigDecimal(4, new BigDecimal("12.34")); |
| stmt.setBigDecimal(5, new BigDecimal("12345.6789")); |
| stmt.execute(); |
| conn.commit(); |
| |
| query = "SELECT col1, col2, col3, col4 FROM " + testDecimalArithmetic |
| + " WHERE pk = 'valueOne'"; |
| stmt = conn.prepareStatement(query); |
| ResultSet rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("123456789123456789"), rs.getBigDecimal(1)); |
| assertEquals(new BigDecimal("12345"), rs.getBigDecimal(2)); |
| assertEquals(new BigDecimal("12.34"), rs.getBigDecimal(3)); |
| assertEquals(new BigDecimal("12345.6789"), rs.getBigDecimal(4)); |
| assertFalse(rs.next()); |
| |
| query = |
| "UPSERT INTO " + testDecimalArithmetic + "(pk, col1, col2, col3) VALUES(?,?,?,?)"; |
| stmt = conn.prepareStatement(query); |
| stmt.setString(1, "valueTwo"); |
| stmt.setBigDecimal(2, new BigDecimal("1234567890123456789012345678901.12345")); |
| stmt.setBigDecimal(3, new BigDecimal("12345.6789")); |
| stmt.setBigDecimal(4, new BigDecimal("123.45678")); |
| stmt.execute(); |
| conn.commit(); |
| |
| query = |
| "SELECT col1, col2, col3 FROM " + testDecimalArithmetic + " WHERE pk = 'valueTwo'"; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("1234567890123456789012345678901"), rs.getBigDecimal(1)); |
| assertEquals(new BigDecimal("12345"), rs.getBigDecimal(2)); |
| assertEquals(new BigDecimal("123.45"), rs.getBigDecimal(3)); |
| assertFalse(rs.next()); |
| |
| // Test upsert incorrect values and confirm exceptions would be thrown. |
| try { |
| query = "UPSERT INTO " + testDecimalArithmetic |
| + "(pk, col1, col2, col3) VALUES(?,?,?,?)"; |
| stmt = conn.prepareStatement(query); |
| stmt.setString(1, "badValues"); |
| // one more than max_precision |
| stmt.setBigDecimal(2, new BigDecimal("12345678901234567890123456789012")); |
| stmt.setBigDecimal(3, new BigDecimal("12345")); |
| stmt.setBigDecimal(4, new BigDecimal("123.45")); |
| stmt.execute(); |
| conn.commit(); |
| fail("Should have caught bad values."); |
| } catch (SQLException e) { |
| assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode()); |
| } |
| try { |
| query = "UPSERT INTO " + testDecimalArithmetic |
| + "(pk, col1, col2, col3) VALUES(?,?,?,?)"; |
| stmt = conn.prepareStatement(query); |
| stmt.setString(1, "badValues"); |
| stmt.setBigDecimal(2, new BigDecimal("123456")); |
| // Exceeds specified precision by 1 |
| stmt.setBigDecimal(3, new BigDecimal("123456")); |
| stmt.setBigDecimal(4, new BigDecimal("123.45")); |
| stmt.execute(); |
| conn.commit(); |
| fail("Should have caught bad values."); |
| } catch (SQLException e) { |
| assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode()); |
| } |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testDecimalUpsertSelect() throws Exception { |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| conn.setAutoCommit(false); |
| try { |
| String source = generateUniqueName(); |
| String ddl = "CREATE TABLE " + source + |
| " (pk VARCHAR NOT NULL PRIMARY KEY, col1 DECIMAL(5,2), col2 DECIMAL(5,1), col3 DECIMAL(5,2), col4 DECIMAL(4,4))"; |
| createTestTable(getUrl(), ddl); |
| String target = generateUniqueName(); |
| ddl = "CREATE TABLE " + target + |
| " (pk VARCHAR NOT NULL PRIMARY KEY, col1 DECIMAL(5,1), col2 DECIMAL(5,2), col3 DECIMAL(4,4))"; |
| createTestTable(getUrl(), ddl); |
| |
| String query = "UPSERT INTO " + source + "(pk, col1) VALUES(?,?)"; |
| PreparedStatement stmt = conn.prepareStatement(query); |
| stmt.setString(1, "1"); |
| stmt.setBigDecimal(2, new BigDecimal("100.12")); |
| stmt.execute(); |
| conn.commit(); |
| stmt.setString(1, "2"); |
| stmt.setBigDecimal(2, new BigDecimal("100.34")); |
| stmt.execute(); |
| conn.commit(); |
| |
| // Evaluated on client side. |
| // source and target in different tables, values scheme compatible. |
| query = "UPSERT INTO " + target + "(pk, col2) SELECT pk, col1 from " + source; |
| stmt = conn.prepareStatement(query); |
| stmt.execute(); |
| conn.commit(); |
| query = "SELECT col2 FROM " + target; |
| stmt = conn.prepareStatement(query); |
| ResultSet rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.12"), rs.getBigDecimal(1)); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.34"), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| // source and target in different tables, values requires scale chopping. |
| query = "UPSERT INTO " + target + "(pk, col1) SELECT pk, col1 from " + source; |
| stmt = conn.prepareStatement(query); |
| stmt.execute(); |
| conn.commit(); |
| query = "SELECT col1 FROM " + target; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.1"), rs.getBigDecimal(1)); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.3"), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| // source and target in different tables, values scheme incompatible. |
| try { |
| query = "UPSERT INTO " + target + "(pk, col3) SELECT pk, col1 from " + source; |
| stmt = conn.prepareStatement(query); |
| stmt.execute(); |
| conn.commit(); |
| fail("Should have caught bad upsert."); |
| } catch (SQLException e) { |
| assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(),e.getErrorCode()); |
| } |
| |
| // Evaluate on server side. |
| conn.setAutoCommit(true); |
| // source and target in same table, values scheme compatible. |
| query = "UPSERT INTO " + source + "(pk, col3) SELECT pk, col1 from " + source; |
| stmt = conn.prepareStatement(query); |
| stmt.execute(); |
| conn.commit(); |
| query = "SELECT col3 FROM " + source; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.12"), rs.getBigDecimal(1)); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.34"), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| // source and target in same table, values requires scale chopping. |
| query = "UPSERT INTO " + source + "(pk, col2) SELECT pk, col1 from " + source; |
| stmt = conn.prepareStatement(query); |
| stmt.execute(); |
| conn.commit(); |
| query = "SELECT col2 FROM " + source; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.1"), rs.getBigDecimal(1)); |
| assertTrue(rs.next()); |
| assertEquals(new BigDecimal("100.3"), rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| // source and target in same table, values scheme incompatible. should throw |
| query = "UPSERT INTO " + source + "(pk, col4) SELECT pk, col1 from " + source; |
| stmt = conn.prepareStatement(query); |
| try { |
| stmt.execute(); |
| conn.commit(); |
| fail(); |
| } catch (SQLException e) { |
| assertEquals(SQLExceptionCode.DATA_EXCEEDS_MAX_CAPACITY.getErrorCode(), e.getErrorCode()); |
| } |
| query = "SELECT col4 FROM " + source; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| assertNull(rs.getBigDecimal(1)); |
| assertTrue(rs.next()); |
| assertNull(rs.getBigDecimal(1)); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testDecimalAveraging() 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(31, 11), col2 DECIMAL(31,1), col3 DECIMAL(38,1))"; |
| createTestTable(getUrl(), ddl); |
| |
| String query = |
| "UPSERT INTO " + testDecimalArithmetic + "(pk, col1, col2, col3) VALUES(?,?,?,?)"; |
| PreparedStatement stmt = conn.prepareStatement(query); |
| stmt.setString(1, "1"); |
| stmt.setBigDecimal(2, new BigDecimal("99999999999999999999.1")); |
| stmt.setBigDecimal(3, new BigDecimal("99999999999999999999.1")); |
| stmt.setBigDecimal(4, new BigDecimal("9999999999999999999999999999999999999.1")); |
| stmt.execute(); |
| conn.commit(); |
| stmt.setString(1, "2"); |
| stmt.setBigDecimal(2, new BigDecimal("0")); |
| stmt.setBigDecimal(3, new BigDecimal("0")); |
| stmt.setBigDecimal(4, new BigDecimal("0")); |
| stmt.execute(); |
| conn.commit(); |
| stmt.setString(1, "3"); |
| stmt.setBigDecimal(2, new BigDecimal("0")); |
| stmt.setBigDecimal(3, new BigDecimal("0")); |
| stmt.setBigDecimal(4, new BigDecimal("0")); |
| stmt.execute(); |
| conn.commit(); |
| |
| // Averaging |
| // result scale should be: max(max(ls, rs), 4). |
| // We are not imposing restriction on precisioin. |
| query = "SELECT avg(col1) FROM " + testDecimalArithmetic; |
| stmt = conn.prepareStatement(query); |
| ResultSet rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| BigDecimal result = rs.getBigDecimal(1); |
| assertEquals(new BigDecimal("33333333333333333333.03333333333"), result); |
| |
| query = "SELECT avg(col2) FROM " + testDecimalArithmetic; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| result = rs.getBigDecimal(1); |
| assertEquals(new BigDecimal("33333333333333333333.0333"), result); |
| |
| // We cap our decimal to a precision of 38. |
| query = "SELECT avg(col3) FROM " + testDecimalArithmetic; |
| stmt = conn.prepareStatement(query); |
| rs = stmt.executeQuery(); |
| assertTrue(rs.next()); |
| result = rs.getBigDecimal(1); |
| assertEquals(new BigDecimal("3333333333333333333333333333333333333"), result); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testRandomFunction() throws Exception { |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| conn.setAutoCommit(false); |
| try { |
| String testRandomFunction = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE " + testRandomFunction + " (pk VARCHAR NOT NULL PRIMARY KEY)"; |
| createTestTable(getUrl(), ddl); |
| conn.createStatement().execute("upsert into " + testRandomFunction + " values ('x')"); |
| conn.createStatement().execute("upsert into " + testRandomFunction + " values ('y')"); |
| conn.createStatement().execute("upsert into " + testRandomFunction + " values ('z')"); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery( |
| "select rand(), rand(), rand(1), rand(2), rand(1) from " + testRandomFunction); |
| assertTrue(rs.next()); |
| double rand0 = rs.getDouble(1); |
| double rand1 = rs.getDouble(3); |
| double rand2 = rs.getDouble(4); |
| assertTrue(rs.getDouble(1) != rs.getDouble(2)); |
| assertTrue(rs.getDouble(2) != rs.getDouble(3)); |
| assertTrue(rs.getDouble(3) == rs.getDouble(5)); |
| assertTrue(rs.getDouble(4) != rs.getDouble(5)); |
| assertTrue(rs.next()); |
| assertTrue(rand0 != rs.getDouble(1)); |
| assertTrue(rand1 != rs.getDouble(3)); |
| assertTrue(rand2 != rs.getDouble(4)); |
| double rand01 = rs.getDouble(1); |
| double rand11 = rs.getDouble(3); |
| double rand21 = rs.getDouble(4); |
| assertTrue(rs.getDouble(1) != rs.getDouble(2)); |
| assertTrue(rs.getDouble(2) != rs.getDouble(3)); |
| assertTrue(rs.getDouble(3) == rs.getDouble(5)); |
| assertTrue(rs.getDouble(4) != rs.getDouble(5)); |
| assertTrue(rs.next()); |
| assertTrue(rand01 != rs.getDouble(1)); |
| assertTrue(rand11 != rs.getDouble(3)); |
| assertTrue(rand21 != rs.getDouble(4)); |
| assertTrue(rs.getDouble(1) != rs.getDouble(2)); |
| assertTrue(rs.getDouble(2) != rs.getDouble(3)); |
| assertTrue(rs.getDouble(3) == rs.getDouble(5)); |
| assertTrue(rs.getDouble(4) != rs.getDouble(5)); |
| double rand12 = rs.getDouble(3); |
| |
| rs = conn.createStatement().executeQuery( |
| "select rand(), rand(), rand(1), rand(2), rand(1) from " + testRandomFunction); |
| assertTrue(rs.next()); |
| assertTrue(rs.getDouble(1) != rs.getDouble(2)); |
| assertTrue(rs.getDouble(2) != rs.getDouble(3)); |
| assertTrue(rs.getDouble(3) == rs.getDouble(5)); |
| assertTrue(rs.getDouble(4) != rs.getDouble(5)); |
| assertTrue(rand0 != rs.getDouble(1)); |
| assertTrue(rand1 == rs.getDouble(3)); |
| assertTrue(rand2 == rs.getDouble(4)); |
| assertTrue(rs.next()); |
| assertTrue(rand01 != rs.getDouble(1)); |
| assertTrue(rand11 == rs.getDouble(3)); |
| assertTrue(rand21 == rs.getDouble(4)); |
| assertTrue(rs.next()); |
| assertTrue(rand12 == rs.getDouble(3)); |
| |
| String testRandomFunction1 = generateUniqueName(); |
| ddl = "CREATE TABLE " + testRandomFunction1 |
| + " (pk VARCHAR NOT NULL PRIMARY KEY, v1 UNSIGNED_DOUBLE)"; |
| createTestTable(getUrl(), ddl); |
| conn.createStatement().execute( |
| "upsert into " + testRandomFunction1 + " select pk, rand(1) from " + testRandomFunction); |
| conn.commit(); |
| |
| rs = conn.createStatement().executeQuery( |
| "select count(*) from " + testRandomFunction1 + " where v1 = rand(1)"); |
| assertTrue(rs.next()); |
| assertEquals(3, rs.getInt(1)); |
| |
| rs = conn.createStatement().executeQuery( |
| "select count(*) from " + testRandomFunction1 + " where v1 = rand(2)"); |
| assertTrue(rs.next()); |
| assertEquals(0, rs.getInt(1)); |
| |
| conn.createStatement().execute( |
| "delete from " + testRandomFunction1 + " where v1 = rand(2)"); |
| conn.commit(); |
| |
| rs = conn.createStatement().executeQuery( |
| "select count(*) from " + testRandomFunction1 + " where v1 = rand(1)"); |
| assertTrue(rs.next()); |
| assertEquals(3, rs.getInt(1)); |
| |
| conn.setAutoCommit(true); |
| conn.createStatement().execute("upsert into " + testRandomFunction1 |
| + " select pk, rand(2) from " + testRandomFunction1); |
| |
| rs = conn.createStatement().executeQuery( |
| "select count(*) from " + testRandomFunction1 + " where v1 = rand(1)"); |
| assertTrue(rs.next()); |
| assertEquals(0, rs.getInt(1)); |
| |
| rs = conn.createStatement().executeQuery( |
| "select count(*) from " + testRandomFunction1 + " where v1 = rand(2)"); |
| assertTrue(rs.next()); |
| assertEquals(3, rs.getInt(1)); |
| |
| conn.createStatement().execute( |
| "delete from " + testRandomFunction1 + " where v1 = rand(2)"); |
| |
| rs = conn.createStatement().executeQuery( |
| "select count(*) from " + testRandomFunction1 + " where v1 = rand(2)"); |
| assertTrue(rs.next()); |
| assertEquals(0, rs.getInt(1)); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| 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(); |
| } |
| } |
| @Test |
| public void testSumDouble() throws Exception { |
| String tableName = "TBL_" + generateUniqueName(); |
| initSumDoubleValues(tableName, null, getUrl()); |
| String query = "SELECT SUM(d) FROM " + tableName ; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertTrue(Doubles.compare(rs.getDouble(1), 0.015)==0); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSumUnsignedDouble() throws Exception { |
| String tableName = "TBL_" + generateUniqueName(); |
| initSumDoubleValues(tableName, null, getUrl()); |
| String query = "SELECT SUM(ud) FROM " + tableName ; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertTrue(Doubles.compare(rs.getDouble(1), 0.015)==0); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSumFloat() throws Exception { |
| String tableName = "TBL_" + generateUniqueName(); |
| initSumDoubleValues(tableName, null, getUrl()); |
| String query = "SELECT SUM(f) FROM " + tableName ; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertTrue(Floats.compare(rs.getFloat(1), 0.15f)==0); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| @Test |
| public void testSumUnsignedFloat() throws Exception { |
| String tableName = "TBL_" + generateUniqueName(); |
| initSumDoubleValues(tableName, null, getUrl()); |
| String query = "SELECT SUM(uf) FROM " + tableName; |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertTrue(Floats.compare(rs.getFloat(1), 0.15f)==0); |
| assertFalse(rs.next()); |
| } finally { |
| conn.close(); |
| } |
| } |
| |
| private String initIntegerTable(Connection conn) throws SQLException { |
| String arithmetic_test = generateUniqueName(); |
| String ddl = "CREATE TABLE " + arithmetic_test |
| + " (six INTEGER PRIMARY KEY, four INTEGER, three INTEGER)"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + arithmetic_test + " VALUES(6, 4, 3)"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| return arithmetic_test; |
| } |
| |
| @Test |
| public void testOrderOfOperationsAdditionSubtraction() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 + 4 - 3 |
| // 10 - 3 |
| // 7 |
| rs = conn.createStatement().executeQuery("SELECT six + four - three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 - 3 + 6 |
| // 1 + 6 |
| // 7 |
| rs = conn.createStatement().executeQuery("SELECT four - three + six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsAdditionMultiplication() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 + 4 * 3 |
| // 6 + 12 |
| // 18 |
| rs = conn.createStatement().executeQuery("SELECT six + four * three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(18, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 * 3 + 6 |
| // 12 * 6 |
| // 18 |
| rs = conn.createStatement().executeQuery("SELECT four * three + six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(18, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsAdditionDivision() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 + 4 / 3 |
| // 6 + 1 |
| // 7 |
| rs = conn.createStatement().executeQuery("SELECT six + four / three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 / 3 + 6 |
| // 1 + 6 |
| // 7 |
| rs = conn.createStatement().executeQuery("SELECT four / three + six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsAdditionModulus() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 + 4 % 3 |
| // 6 + 1 |
| // 7 |
| rs = conn.createStatement().executeQuery("SELECT six + four % three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 % 3 + 6 |
| // 1 + 6 |
| // 7 |
| rs = conn.createStatement().executeQuery("SELECT four % three + six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsSubtrationMultiplication() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 - 4 * 3 |
| // 6 - 12 |
| // -6 |
| rs = conn.createStatement().executeQuery("SELECT six - four * three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(-6, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 * 3 - 6 |
| // 12 - 6 |
| // 6 |
| rs = conn.createStatement().executeQuery("SELECT four * three - six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(6, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsSubtractionDivision() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 - 4 / 3 |
| // 6 - 1 (integer division) |
| // 5 |
| rs = conn.createStatement().executeQuery("SELECT six - four / three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(5, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 / 3 - 6 |
| // 1 - 6 (integer division) |
| // -5 |
| rs = conn.createStatement().executeQuery("SELECT four / three - six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(-5, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsSubtractionModulus() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 - 4 % 3 |
| // 6 - 1 |
| // 5 |
| rs = conn.createStatement().executeQuery("SELECT six - four % three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(5, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 % 3 - 6 |
| // 1 - 6 |
| // -5 |
| rs = conn.createStatement().executeQuery("SELECT four % three - six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(-5, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsMultiplicationDivision() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 * 4 / 3 |
| // 24 / 3 |
| // 8 |
| rs = conn.createStatement().executeQuery("SELECT six * four / three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(8, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 / 3 * 6 |
| // 1 * 6 (integer division) |
| // 6 |
| rs = conn.createStatement().executeQuery("SELECT four / three * six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(6, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsMultiplicationModulus() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 * 4 % 3 |
| // 24 % 3 |
| // 0 |
| rs = conn.createStatement().executeQuery("SELECT six * four % three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(0, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 % 3 * 6 |
| // 1 * 6 |
| // 6 |
| rs = conn.createStatement().executeQuery("SELECT four % three * six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(6, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testOrderOfOperationsDivisionModulus() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = initIntegerTable(conn); |
| ResultSet rs; |
| |
| // 6 / 4 % 3 |
| // 1 % 3 (integer division) |
| // 1 |
| rs = conn.createStatement().executeQuery("SELECT six / four % three FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getLong(1)); |
| assertFalse(rs.next()); |
| |
| // 4 % 3 / 6 |
| // 1 / 6 |
| // 0 (integer division) |
| rs = conn.createStatement().executeQuery("SELECT four % three / six FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(0, rs.getLong(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testCastingOnConstantAddInArithmeticEvaluation() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String testTable = generateUniqueName(); |
| String ddl = "CREATE TABLE IF NOT EXISTS " + testTable |
| + " (k1 INTEGER NOT NULL, v1 INTEGER CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + testTable + " (k1, v1) VALUES (2, 2)"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery( |
| "SELECT k1 / (v1 + 0.5) FROM " + testTable); |
| assertTrue(rs.next()); |
| double d = rs.getDouble(1); |
| assertEquals(0.8, d, 0.01); |
| } |
| |
| @Test |
| public void testCastingOnConstantSubInArithmeticEvaluation() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String testTable = generateUniqueName(); |
| String ddl = "CREATE TABLE IF NOT EXISTS " + testTable |
| + " (k1 INTEGER NOT NULL, v1 INTEGER CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + testTable + " (k1, v1) VALUES (2, 2)"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery( |
| "SELECT k1 / (v1 - 0.5) FROM " + testTable); |
| assertTrue(rs.next()); |
| assertEquals(1.333333333, rs.getDouble(1), 0.001); |
| } |
| |
| @Test |
| public void testFloatingPointUpsert() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String test = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE " + test + " (id VARCHAR not null primary key, name VARCHAR, lat FLOAT)"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + test + "(id,name,lat) VALUES ('testid', 'testname', -1.00)"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT lat FROM " + test); |
| assertTrue(rs.next()); |
| assertEquals(-1.0f, rs.getFloat(1), 0.001); |
| } |
| |
| @Test |
| public void testFloatingPointMultiplicationUpsert() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String test = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE " + test + " (id VARCHAR not null primary key, name VARCHAR, lat FLOAT)"; |
| conn.createStatement().execute(ddl); |
| String dml = |
| "UPSERT INTO " + test + "(id,name,lat) VALUES ('testid', 'testname', -1.00 * 1)"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT lat FROM " + test); |
| assertTrue(rs.next()); |
| assertEquals(-1.0f, rs.getFloat(1), 0.001); |
| } |
| |
| @Test |
| public void testSystemTableHasDoubleForExponentialNumber() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String test = generateUniqueName(); |
| String ddl = "CREATE TABLE " + test + " (id VARCHAR not null primary key, num FLOAT)"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + test + "(id,num) VALUES ('testid', 1.2E3)"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT 1.2E3 FROM \"SYSTEM\".\"CATALOG\" LIMIT 1"); |
| assertTrue(rs.next()); |
| assertTrue(rs.getObject(1) instanceof Double); |
| } |
| |
| @Test |
| public void testFloatingPointWithExponentialNotation() throws Exception { |
| Float[] expected = {1.5E7f, 1.5E-7f, -1.5E-7f, 12E-5f, -.12E+34f}; |
| String[] values = {"1.5e7", "1.5e-7", "-1.5e-7", "12E-5", "-.12E+34"}; |
| ResultSet rs = createTableWithValues(values, "FLOAT"); |
| for (int i = 0; i < expected.length; i++) { |
| assertEquals(expected[i], rs.getFloat(i+1), 0.001); |
| } |
| } |
| |
| @Test |
| public void testDoubleWithExponentialNotation() throws Exception { |
| Double[] expected = {1.5E7d, 1.5E-7d, -1.5E-7d, 12E-5d, -.654E-321d, .1234E+56d}; |
| String[] values = {"1.5e7", "1.5e-7", "-1.5e-7", "12E-5", "-.654E-321", ".1234E+56"}; |
| ResultSet rs = createTableWithValues(values, "DOUBLE"); |
| for (int i = 0; i < expected.length; i++) { |
| assertEquals(expected[i], rs.getDouble(i+1), 0.001); |
| } |
| } |
| |
| private ResultSet createTableWithValues(String[] values, String valueType) throws SQLException { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String test = generateUniqueName(); |
| StringBuilder ddl = new StringBuilder( |
| "CREATE TABLE " + test + " (id VARCHAR not null primary key"); |
| StringBuilder dmll = new StringBuilder("UPSERT INTO " + test + "(id,"); |
| StringBuilder dmlr = new StringBuilder(") VALUES ('testid'"); |
| StringBuilder select = new StringBuilder("SELECT"); |
| for(int i = 0; i < values.length; i++) { |
| ddl.append(", num").append(i).append(" ").append(valueType); |
| dmll.append("num").append(i).append(","); |
| dmlr.append(", ").append(values[i]); |
| select.append(" num").append(i).append(","); |
| } |
| ddl.append(")"); |
| dmlr.append(")"); |
| dmll.deleteCharAt(dmll.length()-1); |
| select.deleteCharAt(select.length()-1); |
| select.append(" FROM " + test); |
| conn.createStatement().execute(ddl.toString()); |
| conn.createStatement().execute(dmll.toString() + dmlr.toString()); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery(select.toString()); |
| rs.next(); |
| return rs; |
| } |
| } |
