| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.InsertTest |
| |
| 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.PreparedStatement; |
| 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; |
| |
| /** |
| * This class contains test cases for the INSERT statement. |
| */ |
| public class InsertTest extends BaseJDBCTestCase { |
| |
| private static final String PARAMETER_IN_SELECT_LIST = "42X34"; |
| private static final String TOO_MANY_RESULT_COLUMNS = "42X06"; |
| |
| public InsertTest(String name) { |
| super(name); |
| } |
| |
| public static Test suite() { |
| return TestConfiguration.defaultSuite(InsertTest.class); |
| } |
| |
| //DERBY-6788(Wrong value inserted by INSERT INTO with multiple subselects) |
| //Following test case has zz against its name and hence it won't run. |
| // This is because one INSERT with JOIN in the test can cause data . |
| // corruption. Once DERBY-6788 is fixed, following test should be enabled |
| // by removing zz. This bug might be related to |
| // DERBY-6786(NullPointerException in INSERT INTO statement with multiple |
| // subselects) |
| public void zztestDerby6788() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("CREATE TABLE M1 (K varchar(64), S decimal)"); |
| s.execute("CREATE TABLE M2 (K varchar(64), S decimal)"); |
| s.execute("CREATE TABLE V (S DECIMAL)"); |
| s.execute("INSERT INTO M1 VALUES ('Bug', 2015)"); |
| s.execute("INSERT INTO M2 VALUES ('Bug', 1957)"); |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "SELECT res.* FROM (SELECT d2.s FROM m1 "+ |
| "LEFT JOIN " + |
| "(SELECT k,s FROM m2) AS d2 ON m1.k=d2.k) AS res"), |
| new String[][]{{"1957"}}); |
| //The INSERT below will insert incorrect value into V because of |
| // DERBY-6788. This bug might be related to |
| // DERBY-6786(NullPointerException in INSERT INTO statement with |
| // multiple subselects) |
| s.execute("INSERT INTO V "+ |
| "(SELECT res.* FROM (SELECT d2.s FROM m1 " + |
| "LEFT JOIN " + |
| "(SELECT k,s FROM m2) AS d2 ON m1.k=d2.k) AS res)"); |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "SELECT * FROM V"), |
| new String[][]{{"1957"}}); |
| s.execute("INSERT INTO V "+ |
| "(SELECT res.* FROM (SELECT d2.s*1 FROM m1 " + |
| "LEFT JOIN " + |
| "(SELECT k,s FROM m2) AS d2 ON m1.k=d2.k) AS res)"); |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "SELECT * FROM V"), |
| new String[][]{{"1957"},{"1957"}}); |
| s.execute("DROP TABLE M1"); |
| s.execute("DROP TABLE M2"); |
| s.execute("DROP TABLE V"); |
| } |
| |
| public void testDerby6786Case1() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("CREATE TABLE K1 (K VARCHAR(64), S DECIMAL)"); |
| s.execute("CREATE TABLE K2 (K VARCHAR(64), S DECIMAL)"); |
| s.execute("CREATE TABLE T (S DECIMAL)"); |
| s.execute("INSERT INTO K1 VALUES ('110007', 224)"); |
| s.execute("INSERT INTO K2 VALUES ('110007', 361)"); |
| //Run just plain select |
| derby6786QueriesToRun(s, ""); |
| //Run insert into ... select |
| derby6786QueriesToRun(s, "insert into t "); |
| s.execute("DROP TABLE K1"); |
| s.execute("DROP TABLE K2"); |
| s.execute("DROP TABLE T"); |
| } |
| |
| public void testDerby6786InsertIntoSelectCase2() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("CREATE TABLE K1 (K VARCHAR(64), S DECIMAL)"); |
| s.execute("CREATE TABLE K2 (K VARCHAR(64), S DECIMAL)"); |
| s.execute("CREATE TABLE T (S DECIMAL)"); |
| s.execute("INSERT INTO K1 VALUES ('110007', 224)"); |
| s.execute("INSERT INTO K2 VALUES ('110007', null)"); |
| //Run just plain select |
| derby6786QueriesToRun(s, ""); |
| //Run insert into ... select |
| derby6786QueriesToRun(s, "insert into t "); |
| s.execute("DROP TABLE K1"); |
| s.execute("DROP TABLE K2"); |
| s.execute("DROP TABLE T"); |
| } |
| |
| //DERBY-6786(NullPointerException in INSERT INTO statement with multiple subselects) |
| //Following test case has zz against its name and hence it won't run. |
| // This is because some queries in the test can cause NPE. Once |
| // DERBY-6786 is fixed, following test should be enabled by removing |
| // zz. |
| public void zztestDerby6786InsertIntoSelectCase3() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("CREATE TABLE K1 (K VARCHAR(64), S DECIMAL)"); |
| s.execute("CREATE TABLE K2 (K VARCHAR(64), S DECIMAL)"); |
| s.execute("CREATE TABLE T (S DECIMAL)"); |
| s.execute("INSERT INTO K1 VALUES ('110007', 224)"); |
| s.execute("INSERT INTO K2 VALUES ('110019', null)"); |
| //Run just plain select |
| derby6786QueriesToRun(s, ""); |
| //Run insert into ... select. Running insert will into can result into |
| // NPE for some of the queries until DERBY-6786 is fixed. |
| derby6786QueriesToRun(s, "insert into t "); |
| s.execute("DROP TABLE K1"); |
| s.execute("DROP TABLE K2"); |
| s.execute("DROP TABLE T"); |
| } |
| |
| private void derby6786QueriesToRun(Statement s, String insertInto) throws SQLException { |
| //following left join works |
| s.execute(insertInto + |
| "select erg.* from ( " + |
| "select d1.s from (select k,s from k1) as d1 " + |
| "left join "+ |
| "(select k,s from k2) as d2 on d1.k=d2.k" + |
| ") as erg " + |
| "where s > 10"); |
| //DERBY-6786 : following left join can fail if the right table |
| // does not have a matching row |
| s.execute(insertInto + |
| "select erg.* from ( " + |
| "select d2.s from (select k,s from k1) as d1 " + |
| "left join "+ |
| "(select k,s from k2) as d2 on d1.k=d2.k" + |
| ") as erg " + |
| "where s > 10"); |
| //DERBY-6786 : following is another example of left join that can fail |
| // if the right table does not have a matching row |
| s.execute(insertInto + |
| "select erg.* from ( " + |
| "select d2.s from k1 " + |
| "left join "+ |
| "(select k,s from k2) as d2 on k1.k=d2.k" + |
| ") as erg " + |
| "where s > 10"); |
| //DERBY-6786 : following right join can fail if the left table |
| // does not have a matching row |
| s.execute(insertInto + |
| "select erg.* from ( " + |
| "select d1.s from (select k,s from k1) as d1 " + |
| "right join "+ |
| "(select k,s from k2) as d2 on d1.k=d2.k" + |
| ") as erg " + |
| "where s > 10"); |
| //following right join works |
| s.execute(insertInto + |
| "select erg.* from ( " + |
| "select d2.s from (select k,s from k1) as d1 " + |
| "right join "+ |
| "(select k,s from k2) as d2 on d1.k=d2.k" + |
| ") as erg " + |
| "where s > 10"); |
| |
| } |
| |
| /** |
| * Regression test case for DERBY-4348 where an INSERT INTO .. SELECT FROM |
| * statement would result in a LONG VARCHAR column becoming populated with |
| * the wrong values. |
| */ |
| public void testInsertIntoSelectFromWithLongVarchar() throws SQLException { |
| // Generate the data that we want table T2 to hold when the test |
| // completes. |
| String[][] data = new String[100][2]; |
| for (int i = 0; i < data.length; i++) { |
| // first column should have integers 0,1,...,99 |
| data[i][0] = Integer.toString(i); |
| // second column should always be -1 |
| data[i][1] = "-1"; |
| } |
| |
| // Turn off auto-commit so that the tables used in the test are |
| // automatically cleaned up in tearDown(). |
| setAutoCommit(false); |
| |
| Statement s = createStatement(); |
| s.execute("create table t1(a long varchar)"); |
| |
| // Fill table T1 with the values we want to see in T2's first column. |
| PreparedStatement insT1 = prepareStatement("insert into t1 values ?"); |
| for (int i = 0; i < data.length; i++) { |
| insT1.setString(1, data[i][0]); |
| insT1.executeUpdate(); |
| } |
| |
| // Create table T2 and insert the contents of T1. Column B must have |
| // a default value and a NOT NULL constraint in order to expose |
| // DERBY-4348. The presence of NOT NULL makes the INSERT statement use |
| // a NormalizeResultSet, and the bug was caused by a bug in the |
| // normalization. |
| s.execute("create table t2(a long varchar, b int default -1 not null)"); |
| s.execute("insert into t2(a) select * from t1"); |
| |
| // Verify that T1 contains the expected values. Use an ORDER BY to |
| // guarantee the same ordering as in data[][]. |
| JDBC.assertFullResultSet(s.executeQuery( |
| "select * from t2 order by int(cast (a as varchar(10)))"), |
| data); |
| } |
| |
| /** |
| * INSERT used to fail with a NullPointerException if the source was an |
| * EXCEPT operation or an INTERSECT operation. DERBY-4420. |
| */ |
| public void testInsertFromExceptOrIntersect() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| // Create tables to fetch data from |
| s.execute("create table t1(x int)"); |
| s.execute("insert into t1 values 1,2,3"); |
| s.execute("create table t2(x int)"); |
| s.execute("insert into t2 values 2,3,4"); |
| |
| // Create table to insert into |
| s.execute("create table t3(x int)"); |
| |
| // INTERSECT (used to cause NullPointerException) |
| s.execute("insert into t3 select * from t1 intersect select * from t2"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t3 order by x"), |
| new String[][]{{"2"}, {"3"}}); |
| s.execute("delete from t3"); |
| |
| // INTERSECT ALL (used to cause NullPointerException) |
| s.execute("insert into t3 select * from t1 " + |
| "intersect all select * from t2"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t3 order by x"), |
| new String[][]{{"2"}, {"3"}}); |
| s.execute("delete from t3"); |
| |
| // EXCEPT (used to cause NullPointerException) |
| s.execute("insert into t3 select * from t1 except select * from t2"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from t3 order by x"), |
| "1"); |
| s.execute("delete from t3"); |
| |
| // EXCEPT ALL (used to cause NullPointerException) |
| s.execute("insert into t3 select * from t1 " + |
| "except all select * from t2"); |
| JDBC.assertSingleValueResultSet( |
| s.executeQuery("select * from t3 order by x"), |
| "1"); |
| s.execute("delete from t3"); |
| } |
| |
| /** |
| * Regression test for DERBY-4671. Verify that dynamic parameters can be |
| * used in the select list in an INSERT INTO ... SELECT FROM statement. |
| * This used to work, but the fix for DERBY-4420 made it throw a |
| * NullPointerException. |
| */ |
| public void testInsertFromSelectWithParameters() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("create table derby4671(x int)"); |
| s.execute("insert into derby4671 values (1), (2)"); |
| |
| // This call failed with a NullPointerException |
| PreparedStatement ins1 = prepareStatement( |
| "insert into derby4671 select ? from derby4671"); |
| |
| ins1.setInt(1, 7); |
| assertUpdateCount(ins1, 2); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from derby4671 order by x"), |
| new String[][] {{"1"}, {"2"}, {"7"}, {"7"}}); |
| |
| // Also verify that it works when the ? is in an expression |
| PreparedStatement ins2 = prepareStatement( |
| "insert into derby4671 select (x+?)*10 from derby4671"); |
| |
| ins2.setInt(1, 77); |
| assertUpdateCount(ins2, 4); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from derby4671 order by x"), |
| new String[][] { |
| {"1"}, {"2"}, {"7"}, {"7"}, |
| {"780"}, {"790"}, {"840"}, {"840"}}); |
| |
| // We only accept ? in the top level select list, so these should |
| // still fail |
| assertCompileError( |
| PARAMETER_IN_SELECT_LIST, |
| "insert into derby4671 select ? from derby4671 " |
| + "union select ? from derby4671"); |
| assertCompileError( |
| PARAMETER_IN_SELECT_LIST, |
| "insert into derby4671 select ? from derby4671 " |
| + "except select ? from derby4671"); |
| assertCompileError( |
| PARAMETER_IN_SELECT_LIST, |
| "insert into derby4671 select ? from derby4671 " |
| + "intersect select ? from derby4671"); |
| } |
| |
| /** |
| * Regression test case for DERBY-4449. INSERT statements with an explicit |
| * target column list used to fail with ArrayIndexOutOfBoundsException if |
| * the table constructor had more columns than the target column list and |
| * one of the extra columns was specified as DEFAULT. |
| */ |
| public void testInsertTooManyDefaultColumns() throws SQLException { |
| createStatement().execute("create table derby4449(x int)"); |
| // This statement has always failed gracefully (no explicit target |
| // column list) |
| assertCompileError( |
| TOO_MANY_RESULT_COLUMNS, |
| "insert into derby4449 values (default, default)"); |
| // This statement used to fail with ArrayIndexOutOfBoundsException |
| assertCompileError( |
| TOO_MANY_RESULT_COLUMNS, |
| "insert into derby4449 (x) values (default, default)"); |
| } |
| |
| /** |
| * Regression test case for DERBY-6443. INSERT statements bind the |
| * source SELECT statement twice, and the second time it would miss |
| * aggregates and subqueries if they were wrapped in a function call. |
| * This led to inconsistencies in the query tree that caused errors |
| * during execution (or assertion failures during compilation in sane |
| * builds). |
| */ |
| public void testDerby6443() throws SQLException { |
| Statement s = createStatement(); |
| |
| // Disable auto-commit for easy cleanup of test tables (automatically |
| // rolled back in tearDown()), and create a separate schema to avoid |
| // name conflicts with other test cases. |
| setAutoCommit(false); |
| s.execute("CREATE SCHEMA d6443"); |
| s.execute("SET SCHEMA d6443"); |
| |
| // This is the original test case provided in the bug report. It |
| // used to fail with an assert failure when compiling the trigger |
| // (in sane builds), or with an ArrayIndexOutOfBoundsException when |
| // the trigger fired (in insane builds). |
| s.execute("CREATE TABLE foo (name VARCHAR(20), val DOUBLE)"); |
| s.execute("CREATE TABLE summary " |
| + "(name VARCHAR(20), aver DOUBLE, size INT)"); |
| s.execute("CREATE TRIGGER trg_foo AFTER INSERT ON foo " |
| + "REFERENCING NEW TABLE AS changed FOR EACH STATEMENT " |
| + "INSERT INTO summary (name, aver, size) " |
| + "SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*) " |
| + "FROM changed " |
| + "GROUP BY name"); |
| s.execute("INSERT INTO foo (name, val) " |
| + "VALUES ('A', 10), ('A', 20), ('B', 30), ('C', 40)"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from foo order by val"), |
| new String[][] { |
| { "A", "10.0" }, |
| { "A", "20.0" }, |
| { "B", "30.0" }, |
| { "C", "40.0" }, |
| }); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from summary order by name"), |
| new String[][] { |
| { "A", "1.0", "2" }, |
| { "B", "1.0", "1" }, |
| { "C", "1.0", "1" }, |
| }); |
| |
| // Some other problematic queries... |
| |
| s.execute("create table t1(x int)"); |
| s.execute("insert into t1 values 1"); |
| s.execute("create table t2(x int)"); |
| |
| // Used to fail with assert or ArrayIndexOutOfBoundsException. |
| s.execute("insert into t2 select floor(avg(x)) from t1"); |
| |
| // Same here... |
| s.execute("create function f(x int) returns int language java " |
| + "parameter style java external name 'java.lang.Math.abs'"); |
| s.execute("insert into t2 select f(avg(x)) from t1"); |
| |
| // This query used to fail with a NullPointerException. |
| s.execute("insert into t2 select f((select x from t1)) from t1"); |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t2"), |
| new String[][] {{"1"}, {"1"}, {"1"}}); |
| } |
| } |
