You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by km...@apache.org on 2008/07/15 18:44:45 UTC
svn commit: r676961 -
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java
Author: kmarsden
Date: Tue Jul 15 09:44:45 2008
New Revision: 676961
URL: http://svn.apache.org/viewvc?rev=676961&view=rev
Log:
DERBY-3762 Convert "org.apache.derbyTesting.functionTests.tests.lang.arithmetic.sql" to junit.
Contributed by Junjie Peng
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java (with props)
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java?rev=676961&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java Tue Jul 15 09:44:45 2008
@@ -0,0 +1,611 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ArithmeticTest
+
+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.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test case for arithmetic.sql. It tests the arithmetic operators.
+ */
+public class ArithmeticTest extends BaseJDBCTestCase {
+
+ public ArithmeticTest(String name) {
+ super(name);
+ }
+
+ public static Test suite(){
+ return TestConfiguration.defaultSuite(ArithmeticTest.class);
+ }
+
+ /**
+ * Test arithmetic on different types.
+ * i.e. int and bigint, or int and smallint.
+ * @throws SQLException
+ */
+ public void testTypes() throws SQLException {
+ String[] tableNames = { "smallint_r", "t", "bigint_r" };
+ String[] types = { "smallint", "int", "bigint", };
+ long[] positiveBoundaries = {32767, 2147483647, 9223372036854775807L};
+
+ for (int i = 0; i < types.length; i++) {
+ doBasically(tableNames[i], types[i], positiveBoundaries[i]);
+ doOverflow(tableNames[i], types[i], positiveBoundaries[i]);
+
+ dropTable(tableNames[i]);
+ }
+ }
+
+ /**
+ * Test basic arithmetic operations.
+ * i.e. int and bigint, or int and smallint.
+ *
+ * @param tableName
+ * the name of table to test.
+ * If a table with the same name has existed,
+ * it will be dropped.
+ * @param type
+ * the type to test. i.e. "smallint" or "bigint".
+ * @param positiveBoundary
+ * for "smallint", it's 32767; for "bigint", it's 9223372036854775807.
+ * @throws SQLException
+ */
+ private void doBasically(String tableName, String type,
+ long positiveBoundary) throws SQLException{
+ String sql = "create table " + tableName + "(i " +
+ type + ", j " + type + ")";
+ Statement st = createStatement();
+ st.addBatch(sql);
+ sql = "insert into " + tableName + " values (null, null)";
+ st.addBatch(sql);
+ sql = "insert into " + tableName + " values (0, 100)";
+ st.addBatch(sql);
+ sql = "insert into " + tableName + " values (1, 101)";
+ st.addBatch(sql);
+ sql = "insert into " + tableName + " values (-2, -102)";
+ st.addBatch(sql);
+
+ st.executeBatch();
+
+ sql = "select * from " + tableName;
+ String[][] result = {
+ {null, null}, {"0", "100"}, {"1", "101"}, {"-2", "-102"},
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql), result);
+
+ sql = "select i + j from " + tableName;
+ result = new String[][]{
+ {null}, {"100"}, {"102"}, {"-104"},
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select i, i + 10 + 20, j, j + 100 + 200" +
+ " from " + tableName;
+ result = new String [][]{
+ {null, null, null, null},
+ {"0", "30", "100", "400"},
+ {"1", "31", "101", "401"},
+ {"-2", "28", "-102", "198"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select i - j, j - i from " + tableName;
+ result = new String [][]{
+ {null, null},
+ {"-100", "100"},
+ {"-100", "100"},
+ {"100", "-100"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select i, i - 10 - 20, 20 - 10 - i, j, " +
+ "j - 100 - 200, 200 - 100 - j from "
+ + tableName;
+ result = new String [][]{
+ {null, null, null, null, null, null},
+ {"0", "-30", "10", "100", "-200", "0"},
+ {"1", "-29", "9", "101", "-199", "-1"},
+ {"-2", "-32", "12", "-102", "-402", "202"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select i, j, i * j, j * i from " + tableName;
+ result = new String [][]{
+ {null, null, null, null},
+ {"0", "100", "0", "0"},
+ {"1", "101", "101", "101"},
+ {"-2", "-102", "204", "204"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select i, j, i * 10 * -20, j * 100 * -200 " +
+ "from " + tableName;
+ result = new String [][]{
+ {null, null, null, null},
+ {"0", "100", "0", "-2000000"},
+ {"1", "101", "-200", "-2020000"},
+ {"-2", "-102", "400", "2040000"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ // try unary minus on some expressions
+ sql = "select -i, -j, -(i * 10 * -20), " +
+ "-(j * 100 * -200) from " + tableName;
+ result = new String[][]{
+ {null, null, null, null},
+ {"0", "-100", "0", "2000000"},
+ {"-1", "-101", "200", "2020000"},
+ {"2", "102", "-400", "-2040000"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ //unary plus doesn't do anything
+ sql = "select +i, +j, +(+i * +10 * -20), " +
+ "+(+j * +100 * -200) from " + tableName;
+ result = new String[][]{
+ {null, null, null, null},
+ {"0", "100", "0", "-2000000"},
+ {"1", "101", "-200", "-2020000"},
+ {"-2", "-102", "400", "2040000"}
+ };
+
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ //test null/null, constant/null, null/constant
+ sql = "select i, j, i / j, 10 / j, j / 10 " +
+ "from " + tableName;
+ result = new String[][]{
+ {null, null, null, null, null},
+ {"0", "100", "0", "0", "10"},
+ {"1", "101", "0", "0", "10"},
+ {"-2", "-102", "0", "0", "-10"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ //test for divide by 0
+ sql = "select j / i from " + tableName;
+ assertStatementError("22012", st, sql);
+
+ sql = "select (j - 1) / (i + 4), 20 / 5 / 4, " +
+ "20 / 4 / 5 from " + tableName;
+ result = new String[][]{
+ {null, "1", "1"},
+ {"24", "1", "1"},
+ {"20", "1", "1"},
+ {"-51", "1", "1"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ //-- test positive/negative, negative/positive
+ //and negative/negative
+ sql = "select j, j / (0 - j), (0 - j) / j, " +
+ "(0 - j) / (0 - j) from " + tableName;
+ result = new String [][] {
+ {null, null, null, null},
+ {"100", "-1", "-1", "1"},
+ {"101", "-1", "-1", "1"},
+ {"-102", "-1", "-1", "1"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ //test some "more complex" expressions
+ sql = "select i, i + 10, i - (10 - 20), i - 10, " +
+ "i - (20 - 10) from " + tableName;
+ result = new String [][] {
+ {null, null, null, null, null},
+ {"0", "10", "10", "-10", "-10"},
+ {"1", "11", "11", "-9", "-9"},
+ {"-2", "8", "8", "-12", "-12"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select 'The next 2 columns should agree', " +
+ "2 + 3 * 4 + 5, 2 + (3 * 4) + 5 " +
+ "from " + tableName;
+ result = new String [][] {
+ {"The next 2 columns should agree", "19", "19"},
+ {"The next 2 columns should agree", "19", "19"},
+ {"The next 2 columns should agree", "19", "19"},
+ {"The next 2 columns should agree", "19", "19"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+
+ sql = "select 'The next column should be 45', " +
+ "(2 + 3) * (4 + 5) from " + tableName;
+ result = new String [][] {
+ {"The next column should be 45", "45"},
+ {"The next column should be 45", "45"},
+ {"The next column should be 45", "45"},
+ {"The next column should be 45", "45"}
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ result);
+ }
+
+ /**
+ * Test overflow on some types.
+ * i.e. int and bigint, or int and smallint.
+ *
+ * @param tableName
+ * the name of table to test.
+ * If a table with the same name has existed,
+ * it will be dropped.
+ * @param type
+ * the type to test. i.e. "smallint" or "bigint".
+ * @param positiveBoundary
+ * for "smallint", it's 32767; for "bigint", it's 9223372036854775807.
+ * @throws SQLException
+ */
+ private void doOverflow(String tableName, String type,
+ long positiveBoundary) throws SQLException{
+ dropTable(tableName);
+ String sql = "create table " + tableName
+ + " (i " + type + ", j " + type + ")";
+ Statement st = createStatement();
+ st.executeUpdate(sql);
+
+ long i = 1L;
+ sql = "insert into " + tableName + " values ("
+ + i + "," + positiveBoundary + ")";
+ assertEquals(1, st.executeUpdate(sql));
+
+ sql = "select i + j from " + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select i - j - j from " + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select j + j from " + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select j * j from " + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "insert into " + tableName + " values "
+ + "(" + (-positiveBoundary - 1) + ", 0)";
+ assertEquals(1, st.executeUpdate(sql));
+
+ sql = "select -i from " + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select -j from " + tableName;
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ new String[][] {
+ { "" + (-positiveBoundary) }, { "0" }, }
+ );
+
+ sql = "select j / 2 * 2 from " + tableName;
+ JDBC.assertFullResultSet(st.executeQuery(sql),
+ new String[][] {
+ { "" + (positiveBoundary - 1) }, { "0" }, }
+ );
+
+ // when type is not bigint, it won't overflow.
+ // Just like testMixedType().
+ if (type.equals("bigint")) {
+ sql = "select 2 * (" + positiveBoundary + " / 2 + 1) from "
+ + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select -2 * (" + positiveBoundary + " / 2 + 2) from "
+ + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select 2 * ((-" + positiveBoundary + " - 1) / 2 - 1) from "
+ + tableName;
+ assertStatementError("22003", st, sql);
+
+ sql = "select -2 * ((-" + positiveBoundary + " - 1) / 2 - 1) from "
+ + tableName;
+ assertStatementError("22003", st, sql);
+
+ //different from arithmetic. This can support better test.
+ sql = "select i / 2 * 2 - 1 from " + tableName;
+ assertStatementError("22003", st, sql);
+ }
+ }
+
+ /**
+ * Test mixed types.i.e. int and bigint, or int and smallint.
+ *
+ * @param tableName
+ * the name of table to test.
+ * @param type
+ * the type to test. i.e. "smallint" or "bigint".
+ * @param i
+ * an integer number to test with.
+ * @throws SQLException
+ */
+ private void doMixedTypeImpl(String tableName, String type,
+ int i) throws SQLException{
+ String sql = "create table " + tableName
+ + " (y " + type+ ")" ;
+ Statement st = createStatement();
+ st.executeUpdate(sql);
+
+ long y = 2L;
+ sql = "insert into " + tableName + " values (" + y + ")";
+ assertEquals(1, st.executeUpdate(sql));
+
+ sql = "select " + i + " + y from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (i + y));
+
+ sql = "select y + " + i + " from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (y + i));
+
+ sql = "select y - " + i + " from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (y - i));
+
+ sql = "select " + i + " - y from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (i - y));
+
+ sql = "select " + i + " * y from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (i * y));
+
+ sql = "select y * " + i + " from "+ tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (y * i));
+
+ sql = "select " + i + " / y from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (i / y));
+
+ sql = "select y / " + i + " from " + tableName;
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ "" + (y / i));
+
+ st.close();
+ }
+
+ /**
+ * Test mixed types.i.e. int and bigint, or int and smallint.
+ * @throws SQLException
+ */
+ public void testMixedType() throws SQLException{
+ String[] types = {"smallint", "bigint",};
+ String[] tableNames = {"smallint_r", "bigint_r"};
+ int[]positiveBoundaries = {65535, 2147483647,};
+
+ for(int i = 0; i < types.length; i++){
+ doMixedTypeImpl(tableNames[i], types[i],
+ positiveBoundaries[i]);
+ dropTable(tableNames[i]);
+ }
+ }
+
+ /**
+ * Test the arithmetic operators on a type we know they don't work on.
+ * @throws SQLException
+ */
+ public void testWrongType() throws SQLException{
+ String sql = "create table s (x char(10), y char(10))";
+ Statement st = createStatement();
+ st.executeUpdate(sql);
+ st.close();
+
+ sql ="select x + y from s";
+ assertCompileError("42Y95", sql);
+
+ sql = "select x - y from s";
+ assertCompileError("42Y95", sql);
+
+ sql = "select x * y from s";
+ assertCompileError("42Y95", sql);
+
+ sql = "select x / y from s";
+ assertCompileError("42Y95", sql);
+
+ sql = "select -x from s";
+ assertCompileError("42X37", sql);
+
+ dropTable("s");
+ }
+
+ /**
+ * Arithmetic on a numeric data type.
+ * @throws SQLException
+ */
+ public void testNumericDataType() throws SQLException{
+ String sql = "create table u (c1 int, c2 char(10))";
+ Statement st = createStatement();
+ st.addBatch(sql);
+ sql = "insert into u (c2) values 'asdf'";
+ st.addBatch(sql);
+ sql = "insert into u (c1) values null";
+ st.addBatch(sql);
+ sql = "insert into u (c1) values 1";
+ st.addBatch(sql);
+ sql = "insert into u (c1) values null";
+ st.addBatch(sql);
+ sql = "insert into u (c1) values 2";
+ st.addBatch(sql);
+ st.executeBatch();
+
+ sql = "select * from u";
+ String[][] result = {
+ {null, "asdf",},
+ {null, null,},
+ {"1", null,},
+ {null, null,},
+ {"2", null,},
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql), result);
+
+ sql = "select c1 + c1 from u";
+ result = new String[][]{
+ {null,}, {null,}, {"2",}, {null,}, {"4",},
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql), result);
+
+ sql = "select c1 / c1 from u";
+ result = new String[][]{
+ {null,}, {null,}, {"1",}, {null,}, {"1",},
+ };
+ JDBC.assertFullResultSet(st.executeQuery(sql), result);
+
+ //arithmetic between a numeric and a string data type fails.
+ sql = "select c1 + c2 from u";
+ assertStatementError("22018", st, sql);
+
+ st.close();
+
+ dropTable("u");
+ }
+
+ public void testPrecedenceAndAssociativity() throws SQLException{
+ String sql = "create table r (x int)";
+ Statement st = createStatement();
+ st.executeUpdate(sql);
+
+ sql = "insert into r values (1)";
+ assertEquals(1, st.executeUpdate(sql));
+
+ sql = "select 2 + 3 * 4 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (2 + 3 * 4) + "");
+
+ sql = "select (2 + 3) * 4 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ((2 + 3) * 4) + "");
+
+ sql = "select 3 * 4 + 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ( 3 * 4 + 2) + "");
+
+ sql = "select 3 * (4 + 2) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ( 3 * (4 + 2)) + "");
+
+ sql = "select 2 - 3 * 4 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (2 - 3 * 4) + "");
+
+ sql = "select (2 - 3) * 4 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ((2 - 3) * 4) + "");
+
+ sql = "select 3 * 4 - 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ( 3 * 4 - 2) + "");
+
+ sql = "select 3 * (4 - 2) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (3 * (4 - 2)) + "");
+
+ sql = "select 4 + 3 / 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (4 + 3 / 2) + "");
+
+ sql = "select (4 + 3) / 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ((4 + 3) / 2) + "");
+
+ sql = "select 3 / 2 + 4 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (3 / 2 + 4) + "");
+
+ sql = "select 3 / (2 + 4) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (3 / (2 + 4)) + "");
+
+ sql = "select 4 - 3 / 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (4 - 3 / 2) + "");
+
+ sql = "select (4 - 3) / 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ ((4 - 3) / 2) + "");
+
+ sql = "select 1 + 2147483647 - 2 from r";
+ assertStatementError("22003", st, sql);
+
+ sql = "select 1 + (2147483647 - 2) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (1 + (2147483647 - 2)) + "");
+
+ sql = "select 4 * 3 / 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (4 * 3 / 2) + "");
+
+ sql = "select 4 * (3 / 2) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (4 * (3 / 2)) + "");
+
+ //Test associativity of unary - versus the binary operators
+ sql = "select -1 + 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (-1 + 2) + "");
+
+ sql = "select -(1 + 2) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (-(1 + 2)) + "");
+
+ sql = "select -1 - 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (-1 - 2) + "");
+
+ sql = "select -(1 - 2) from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (-(1 - 2)) + "");
+
+ //The test the associativity of unary - with respect to binary *,
+ //we must use a trick. The value -1073741824 is the minimum integer
+ //divided by 2. So, 1073741824 * 2 will overflow, but (-1073741824) * 2
+ //will not (because of two's complement arithmetic.
+ sql = "select -1073741824 * 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (-1073741824 * 2) + "");
+
+ sql = "select -(1073741824 * 2) from r";
+ assertStatementError("22003", st, sql);
+
+ //-- This should not get an overflow
+ sql = "select -2147483648 / 2 from r";
+ JDBC.assertSingleValueResultSet(st.executeQuery(sql),
+ (-2147483648 / 2) + "");
+
+ dropTable("r");
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ArithmeticTest.java
------------------------------------------------------------------------------
svn:eol-style = native