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 bp...@apache.org on 2010/03/05 01:49:29 UTC
svn commit: r919250 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/outerjoin.out suites/derbylang.runall tests/lang/OuterJoinTest.java
tests/lang/_Suite.java tests/lang/outerjoin.sql
Author: bpendleton
Date: Fri Mar 5 00:49:29 2010
New Revision: 919250
URL: http://svn.apache.org/viewvc?rev=919250&view=rev
Log:
DERBY-4424: Convert outerjoin.sql into JUnit
This change was contributed by Eranda Sooriyabandara (070468D at gmail dot com)
This change introduces a new JUnit-based assertion test, OuterJoinTest, which
replaces the previous ij-based outerjoin-sql test. The new test is added to
the lang suite of JUnit tests, and the old test is removed from the
derbylang.runall suite.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java (with props)
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/outerjoin.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/outerjoin.sql
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=919250&r1=919249&r2=919250&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Fri Mar 5 00:49:29 2010
@@ -22,7 +22,6 @@
lang/nestedCommit.sql
lang/openScans.sql
lang/orderbyElimination.sql
-lang/outerjoin.sql
lang/outparams.java
lang/paramij.sql
lang/partdml.sql
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java?rev=919250&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java Fri Mar 5 00:49:29 2010
@@ -0,0 +1,2393 @@
+
+/*
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest
+
+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.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.CallableStatement;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLWarning;
+
+import java.sql.Timestamp;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+public final class OuterJoinTest extends BaseJDBCTestCase
+{
+
+ /**
+ * Public constructor required for running test as standalone JUnit.
+ */
+ public OuterJoinTest(String name)
+ {
+ super(name);
+ }
+
+ public static Test suite()
+ {
+ //Add the test case into the test suite
+ TestSuite suite = new TestSuite("OuterJoinTest Test");
+ suite.addTest(TestConfiguration.defaultSuite(OuterJoinTest.class));
+ return TestConfiguration.defaultSuite(OuterJoinTest.class);
+ }
+
+ private void createTestObjects(Statement st) throws Exception
+ {
+ setAutoCommit(false);
+
+ //clean the database tests in previously created
+ CleanDatabaseTestSetup.cleanDatabase(getConnection(), false);
+
+ // create some tables
+
+ st.executeUpdate("create table t1(c1 int)");
+ st.executeUpdate("create table t2(c1 int)");
+ st.executeUpdate("create table t3(c1 int)");
+ st.executeUpdate("create table tt1(c1 int, c2 int, c3 int)");
+ st.executeUpdate("create table tt2(c1 int, c2 int, c3 int)");
+ st.executeUpdate("create table tt3(c1 int, c2 int, c3 int)");
+ st.executeUpdate("create table empty_table(c1 int)");
+ st.executeUpdate("create table insert_test(c1 int, c2 int, c3 int)");
+ st.executeUpdate("create table x (c1 int, c2 int, c3 int)");
+ st.executeUpdate("create table y (c3 int, c4 int, c5 int)");
+ st.executeUpdate("create table a (c1 int)");
+ st.executeUpdate("create table b (c2 float)");
+ st.executeUpdate("create table c (c3 char(30))");
+ // following is verifying that oj is not a keyword
+ st.executeUpdate("create table oj(oj int)");
+
+ // populate the tables
+
+ st.executeUpdate("insert into t1 values 1, 2, 2, 3, 4");
+ st.executeUpdate("insert into t2 values 1, 3, 3, 5, 6");
+ st.executeUpdate("insert into t3 values 2, 3, 5, 5, 7");
+ st.executeUpdate("insert into tt1 select c1, c1, c1 from t1");
+ st.executeUpdate("insert into tt2 select c1, c1, c1 from t2");
+ st.executeUpdate("insert into tt3 select c1, c1, c1 from t3");
+ st.executeUpdate("insert into x values (1, 2, 3), (4, 5, 6)");
+ st.executeUpdate("insert into y values (3, 4, 5), (666, 7, 8)");
+ st.executeUpdate(
+ "insert into insert_test"
+ + "(select * from t1 a left outer join t2 b on a.c1 = "
+ + "b.c1 left outer join t3 c on a.c1 <> c.c1)");
+
+ st.executeUpdate("insert into a values 1");
+ st.executeUpdate("insert into b values 3.3");
+ st.executeUpdate("insert into c values 'asdf'");
+ // verifying that oj is not a keyword
+ st.executeUpdate("insert into oj(oj) values (1)");
+ }
+
+ // negative tests on outer join
+ public void testNegative() throws Exception
+ {
+ Statement st = createStatement();
+
+ createTestObjects(st);
+
+ assertStatementError("42X01", st,
+ "select * from t1 outer join t2");
+
+ // no join clause
+
+ assertStatementError("42X01", st,
+ "select * from t1 left outer join t2");
+
+ assertStatementError("42X01", st,
+ " select * from t1 right outer join t2");
+ }
+
+ // positive tests on normal forms of outer join
+ public void testPositive() throws Exception
+ {
+ Statement st = createStatement();
+ ResultSet rs = null;
+ ResultSetMetaData rsmd;
+ PreparedStatement pSt;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery(
+ "select t1.c1 from t1 left outer join t2 on t1.c1 = t2.c1");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select t2.c1 from t1 right outer join t2 on t1.c1 = t2.c1");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"3"},
+ {"3"},
+ {"5"},
+ {"6"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select a.x from t1 a (x) left outer join t2 b (x) "
+ + "on a.x = b.x");
+
+ expColNames = new String [] {"X"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // verify that selects from inner table work
+
+ rs = st.executeQuery(
+ "select b.* from (values 9) a left outer join t2 b on 1=1");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"3"},
+ {"3"},
+ {"5"},
+ {"6"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select b.* from (values 9) a left outer join t2 b on 1=0");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select b.* from (values 9) a right outer join t2 b on 1=0");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"3"},
+ {"3"},
+ {"5"},
+ {"6"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select a.* from (values 9) a right outer join t2 b on 1=1");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"9"},
+ {"9"},
+ {"9"},
+ {"9"},
+ {"9"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select a.* from (values 9) a right outer join t2 b on 1=0");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null},
+ {null},
+ {null},
+ {null},
+ {null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select a.* from ((values ('a', 'b')) a inner join "
+ + "(values ('c', 'd')) b on 1=1) left outer join "
+ + "(values ('e', 'f')) c on 1=1");
+
+ expColNames = new String [] {"1", "2"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"a", "b"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select b.* from ((values ('a', 'b')) a inner join "
+ + "(values ('c', 'd')) b on 1=1) left outer join "
+ + "(values ('e', 'f')) c on 1=1");
+
+ expColNames = new String [] {"1", "2"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"c", "d"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select c.* from ((values ('a', 'b')) a inner join "
+ + "(values ('c', 'd')) b on 1=1) left outer join "
+ + "(values ('e', 'f')) c on 1=1");
+
+ expColNames = new String [] {"1", "2"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"e", "f"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // verifying that oj is not a keyword
+
+ rs = st.executeQuery(
+ "select * from oj where oj = 1");
+
+ expColNames = new String [] {"OJ"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ //verifying both regular and {oj } in
+
+ rs = st.executeQuery(
+ "select * from t1 left outer join {oj t2 left outer "
+ + "join t3 on t2.c1=t3.c1} on t1.c1=t3.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null, null},
+ {"2", null, null},
+ {"2", null, null},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // left and right outer join with an empty table
+
+ rs = st.executeQuery(
+ "select t1.c1 from t1 left outer join empty_table et "
+ + "on t1.c1 = et.c1");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select t1.c1 from t1 right outer join empty_table "
+ + "et on t1.c1 = et.c1");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+ JDBC.assertDrainResults(rs, 0);
+
+ rs = st.executeQuery(
+ " select t1.c1 from empty_table et right outer join "
+ + "t1 on et.c1 = t1.c1");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // this query may make no sense at all, but it's just
+ // trying to show that parser works fine with both regular
+ // tableexpression and tableexpression with {oj }
+
+ rs = st.executeQuery(
+ "select * from t1, {oj t2 join t3 on t2.c1=t3.c1}");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "3", "3"},
+ {"1", "3", "3"},
+ {"1", "5", "5"},
+ {"1", "5", "5"},
+ {"2", "3", "3"},
+ {"2", "3", "3"},
+ {"2", "5", "5"},
+ {"2", "5", "5"},
+ {"2", "3", "3"},
+ {"2", "3", "3"},
+ {"2", "5", "5"},
+ {"2", "5", "5"},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"3", "5", "5"},
+ {"3", "5", "5"},
+ {"4", "3", "3"},
+ {"4", "3", "3"},
+ {"4", "5", "5"},
+ {"4", "5", "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // parameters and join clause
+
+ pSt = prepareStatement(
+ "select * from t1 left outer join t2 on 1=? and t1.c1 = t2.c1");
+
+ rs = st.executeQuery("values 1");
+
+ rs.next();
+ rsmd = rs.getMetaData();
+ for (int i = 1; i <= rsmd.getColumnCount(); i++)
+ pSt.setObject(i, rs.getObject(i));
+
+ rs = pSt.executeQuery();
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"2", null},
+ {"2", null},
+ {"3", "3"},
+ {"3", "3"},
+ {"4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ pSt = prepareStatement(
+ "select * from t1 left outer join t2 on t1.c1 = "
+ + "t2.c1 and t1.c1 = ?");
+
+ rs = st.executeQuery("values 1");
+
+ rs.next();
+ rsmd = rs.getMetaData();
+ for (int i = 1; i <= rsmd.getColumnCount(); i++)
+ pSt.setObject(i, rs.getObject(i));
+
+ rs = pSt.executeQuery();
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"2", null},
+ {"2", null},
+ {"3", null},
+ {"4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // additional predicates outside of the join clause egs of
+ // using {oj --} syntax
+
+ rs = st.executeQuery(
+ "select * from t1 left outer join t2 on t1.c1 = "
+ + "t2.c1 where t1.c1 = 1");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from {oj t1 left outer join t2 on t1.c1 = "
+ + "t2.c1} where t1.c1 = 1");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 right outer join t2 on t1.c1 = 1 "
+ + "where t2.c1 = t1.c1");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from {oj t1 right outer join t2 on t1.c1 "
+ + "= 1} where t2.c1 = t1.c1");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testOuterJoinWithSubquery() throws Exception
+ {
+ // subquery in join clause. egs of using {oj --} syntax
+
+ ResultSet rs = null;
+ Statement st = createStatement();
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+
+ rs = st.executeQuery(
+ "select * from t1 a left outer join t2 b "
+ + "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where "
+ + "a.c1 = t1.c1 and a.c1 = 1)");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"2", null},
+ {"2", null},
+ {"3", null},
+ {"4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from {oj t1 a left outer join t2 b "
+ + "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where "
+ + "a.c1 = t1.c1 and a.c1 = 1)}");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"2", null},
+ {"2", null},
+ {"3", null},
+ {"4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 a left outer join t2 b "
+ + "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where "
+ + "a.c1 = t1.c1 and a.c1 <> 2)");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"2", null},
+ {"2", null},
+ {"3", "3"},
+ {"3", "3"},
+ {"4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from {oj t1 a left outer join t2 b "
+ + "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where "
+ + "a.c1 = t1.c1 and a.c1 <> 2)}");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"2", null},
+ {"2", null},
+ {"3", "3"},
+ {"3", "3"},
+ {"4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 a right outer join t2 b "
+ + "on a.c1 = b.c1 and a.c1 in (select c1 from t1 where "
+ + "a.c1 = t1.c1)");
+
+ expColNames = new String [] {"C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1"},
+ {"3", "3"},
+ {"3", "3"},
+ {null, "5"},
+ {null, "6"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testOuterJoinWithinSubquery() throws Exception
+ {
+ //outer join in subquery egs of using {oj --} syntax
+
+ ResultSet rs = null;
+ Statement st = createStatement();
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery(
+ "select * from (t1 a)"
+ + "where exists (select * from t1 left outer join t2 "
+ + "on t1.c1 = t2.c1)");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from (t1 a)"
+ + "where exists (select * from {oj t1 left outer join "
+ + "t2 on t1.c1 = t2.c1})");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from (t1 a)"
+ + "where exists (select * from t1 left outer join t2 on 1=0)");
+
+ expColNames = new String [] {"C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"2"},
+ {"3"},
+ {"4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testNestedJoins() throws Exception
+ {
+ // nested joins egs of using {oj --} syntax
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery(
+ "select * from t1 left outer join t2 on t1.c1 = "
+ + "t2.c1 left outer join t3 on t1.c1 = t3.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"2", null, "2"},
+ {"2", null, "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from {oj t1 left outer join t2 on t1.c1 = "
+ + "t2.c1 left outer join t3 on t1.c1 = "
+ + "t3.c1}");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"2", null, "2"},
+ {"2", null, "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 left outer join t2 on t1.c1 = "
+ + "t2.c1 left outer join t3 on t2.c1 = t3.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"2", null, null},
+ {"2", null, null},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t3 right outer join t2 on t3.c1 = "
+ + "t2.c1 right outer join t1 on t1.c1 = t2.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, "1", "1"},
+ {null, null, "2"},
+ {null, null, "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {null, null, "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // parents
+
+ rs = st.executeQuery(
+ "select * from (t1 left outer join t2 on t1.c1 = "
+ + "t2.c1) left outer join t3 on t1.c1 = t3.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"2", null, "2"},
+ {"2", null, "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 left outer join (t2 left outer "
+ + "join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"2", null, null},
+ {"2", null, null},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testLeftRightOuterJoinCombination() throws Exception
+ {
+ // left/right outer join combinations
+
+ ResultSet rs = null;
+ Statement st = createStatement();
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery(
+ "select * from t1 a right outer join t2 b on a.c1 = "
+ + "b.c1 left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {null, "5", null},
+ {null, "6", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from (t1 a right outer join t2 b on a.c1 "
+ + "= b.c1) left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {null, "5", null},
+ {null, "6", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 a left outer join t2 b on a.c1 = "
+ + "b.c1 right outer join t3 c on c.c1 = a.c1 where "
+ + "a.c1 is not null");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", null, "2"},
+ {"2", null, "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from (t1 a left outer join t2 b on a.c1 = "
+ + "b.c1) right outer join t3 c on c.c1 = a.c1 where "
+ + "a.c1 is not null");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", null, "2"},
+ {"2", null, "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from t1 a left outer join (t2 b right "
+ + "outer join t3 c on c.c1 = b.c1) on a.c1 = c.c1 "
+ + "where c.c1=b.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3", "3"},
+ {"3", "3", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ }
+
+ public void testOuterJoinWithInsertUpdateDelete() throws Exception
+ {
+ // test insert/update/delete
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery("select * from insert_test");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "2"},
+ {"1", "1", "3"},
+ {"1", "1", "5"},
+ {"1", "1", "5"},
+ {"1", "1", "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"4", null, "2"},
+ {"4", null, "3"},
+ {"4", null, "5"},
+ {"4", null, "5"},
+ {"4", null, "7"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ assertUpdateCount(st, 5,
+ " update insert_test"
+ + " set c1 = (select 9 from t1 a left outer join t1 b"
+ + " on a.c1 = b.c1 where a.c1 = 1)"
+ + " where c1 = 1");
+
+ rs = st.executeQuery("select * from insert_test");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"9", "1", "2"},
+ {"9", "1", "3"},
+ {"9", "1", "5"},
+ {"9", "1", "5"},
+ {"9", "1", "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"4", null, "2"},
+ {"4", null, "3"},
+ {"4", null, "5"},
+ {"4", null, "5"},
+ {"4", null, "7"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ assertUpdateCount(st, 5,
+ " delete from insert_test"
+ + " where c1 = (select 9 from t1 a left outer join t1 b"
+ + " on a.c1 = b.c1 where a.c1 = 1)");
+
+ rs = st.executeQuery("select * from insert_test");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"4", null, "2"},
+ {"4", null, "3"},
+ {"4", null, "5"},
+ {"4", null, "5"},
+ {"4", null, "7"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ assertUpdateCount(st, 21, "delete from insert_test");
+
+ st.executeUpdate(
+ " insert into insert_test"
+ + " (select * from (select * from t1 a left outer join"
+ + " t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <>"
+ + " c.c1) d (c1, c2, c3))");
+
+ rs = st.executeQuery("select * from insert_test");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "2"},
+ {"1", "1", "3"},
+ {"1", "1", "5"},
+ {"1", "1", "5"},
+ {"1", "1", "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"2", null, "3"},
+ {"2", null, "5"},
+ {"2", null, "5"},
+ {"2", null, "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"3", "3", "2"},
+ {"3", "3", "5"},
+ {"3", "3", "5"},
+ {"3", "3", "7"},
+ {"4", null, "2"},
+ {"4", null, "3"},
+ {"4", null, "5"},
+ {"4", null, "5"},
+ {"4", null, "7"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ assertUpdateCount(st, 26, "delete from insert_test");
+ }
+
+ public void testMulticolumn() throws Exception
+ {
+ // multicolumn tests c1, c2, and c3 all have the same values
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery(
+ "select tt1.c1, tt1.c2, tt1.c3, tt2.c2, tt2.c3 from "
+ + "tt1 left outer join tt2 on tt1.c1 = tt2.c1");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1", "1"},
+ {"2", "2", "2", null, null},
+ {"2", "2", "2", null, null},
+ {"3", "3", "3", "3", "3"},
+ {"3", "3", "3", "3", "3"},
+ {"4", "4", "4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select tt1.c1, tt1.c2, tt1.c3, tt2.c3 from tt1 "
+ + "left outer join tt2 on tt1.c1 = tt2.c1");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1"},
+ {"2", "2", "2", null},
+ {"2", "2", "2", null},
+ {"3", "3", "3", "3"},
+ {"3", "3", "3", "3"},
+ {"4", "4", "4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select tt1.c1, tt1.c2, tt1.c3 from tt1 left outer "
+ + "join tt2 on tt1.c1 = tt2.c1");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1"},
+ {"2", "2", "2"},
+ {"2", "2", "2"},
+ {"3", "3", "3"},
+ {"3", "3", "3"},
+ {"4", "4", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // nested outer joins
+
+ rs = st.executeQuery(
+ "select tt1.c2, tt1.c1, tt1.c3, tt2.c1, tt2.c3 from "
+ + "t1 left outer join tt1 on t1.c1 = tt1.c1 left outer "
+ + "join tt2 on tt1.c2 = tt2.c2");
+
+ expColNames = new String [] {"C2", "C1", "C3", "C1", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1", "1"},
+ {"2", "2", "2", null, null},
+ {"2", "2", "2", null, null},
+ {"2", "2", "2", null, null},
+ {"2", "2", "2", null, null},
+ {"3", "3", "3", "3", "3"},
+ {"3", "3", "3", "3", "3"},
+ {"4", "4", "4", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testColumnReordering() throws Exception
+ {
+ // make sure that column reordering is working correctly
+ // when there's an ON clause
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ // qualfied * will return all of the columns of the
+ // qualified table including join columns
+
+ rs = st.executeQuery(
+ "select x.* from x join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select x.* from x left outer join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3"},
+ {"4", "5", "6"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select x.* from x right outer join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3"},
+ {null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select y.* from x join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C3", "C4", "C5"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select y.* from x left outer join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C3", "C4", "C5"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5"},
+ {null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select y.* from x right outer join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C3", "C4", "C5"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5"},
+ {"666", "7", "8"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // * will return all of the columns of all joined tables
+
+ rs = st.executeQuery(
+ "select * from x join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C3", "C4", "C5"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3", "3", "4", "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from x left outer join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C3", "C4", "C5"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3", "3", "4", "5"},
+ {"4", "5", "6", null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from x right outer join y on x.c3 = y.c3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C3", "C4", "C5"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3", "3", "4", "5"},
+ {null, null, null, "666", "7", "8"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testRightOuterJoinXform() throws Exception
+ {
+ // verify that right outer join xforms don't get result
+ // columns confused
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ rs = st.executeQuery(
+ " select * from a left outer join b on 1=1 left "
+ + "outer join c on 1=1");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "3.3", "asdf"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from a left outer join b on 1=1 left "
+ + "outer join c on 1=0");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "3.3", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from a left outer join b on 1=0 left "
+ + "outer join c on 1=1");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null, "asdf"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from a left outer join b on 1=0 left "
+ + "outer join c on 1=0");
+
+ expColNames = new String [] {"C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from c right outer join b on 1=1 right "
+ + "outer join a on 1=1");
+
+ expColNames = new String [] {"C3", "C2", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"asdf", "3.3", "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from c right outer join b on 1=1 right "
+ + "outer join a on 1=0");
+
+ expColNames = new String [] {"C3", "C2", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from c right outer join b on 1=0 right "
+ + "outer join a on 1=1");
+
+ expColNames = new String [] {"C3", "C2", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, "3.3", "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select * from c right outer join b on 1=0 right "
+ + "outer join a on 1=0");
+
+ expColNames = new String [] {"C3", "C2", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, "1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testInnerJoinXform() throws Exception
+ {
+ // test outer join -> inner join xform
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ assertUpdateCount(st, 5, "delete from tt1");
+ assertUpdateCount(st, 5, " delete from tt2");
+ assertUpdateCount(st, 5, " delete from tt3");
+
+ st.executeUpdate(
+ " insert into tt1 values (1, 2, 3), (2, 3, 4), (3, 4, 5)");
+
+ st.executeUpdate(
+ " insert into tt2 values (1, 2, 3), (2, 3, 4), (3, 4, 5)");
+
+ st.executeUpdate(
+ " insert into tt3 values (1, 2, 3), (2, 3, 4), (3, 4, 5)");
+
+ CallableStatement cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+ assertUpdateCount(cSt, 0);
+
+ // no xform, predicate on outer table
+
+ rs = st.executeQuery(
+ "select * from tt1 left outer join tt2 on tt1.c1 = "
+ + "tt2.c2 where tt1.c1 = 3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5", "2", "3", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // various predicates on inner table
+
+ rs = st.executeQuery(
+ "select * from tt1 left outer join tt2 on tt1.c1 = "
+ + "tt2.c2 where tt2.c2 = 3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5", "2", "3", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+
+ rs = st.executeQuery(
+ " select * from tt1 left outer join tt2 on tt1.c1 = "
+ + "tt2.c2 where tt2.c1 + 1= tt2.c2");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "3", "4", "1", "2", "3"},
+ {"3", "4", "5", "2", "3", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+
+ rs = st.executeQuery(
+ " select * from tt1 left outer join tt2 on tt1.c1 = "
+ + "tt2.c2 where tt2.c1 + 1= 3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5", "2", "3", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+
+ rs = st.executeQuery(
+ " select * from tt2 right outer join tt1 on tt1.c1 = "
+ + "tt2.c2 where tt2.c1 + 1= 3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "3", "4", "3", "4", "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+
+ rs = st.executeQuery(
+ " select * from tt1 left outer join tt2 on tt1.c1 = "
+ + "tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where "
+ + "tt3.c3 = 3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5", "2", "3", "4", "1", "2", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+
+ rs = st.executeQuery(
+ " select * from tt1 left outer join tt2 on tt1.c1 = "
+ + "tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where "
+ + "tt2.c2 = 3");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4", "5", "2", "3", "4", "1", "2", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+
+ // make sure predicates are null tolerant
+
+ rs = st.executeQuery(
+ "select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 "
+ + "where char(tt2.c2) is null");
+
+ expColNames = new String [] {"C1", "C2", "C3", "C1", "C2", "C3"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "2", "3", null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // where java.lang.Integer::toString(tt2.c2) = '2';
+
+ rs = st.executeQuery(
+ "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+ }
+
+ public void testdDerby2924() throws Exception
+ {
+ // bug 2924, cross join under an outer join
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate(
+ "CREATE TABLE inventory(itemno INT NOT NULL PRIMARY "
+ + "KEY, capacity INT)");
+
+ st.executeUpdate("INSERT INTO inventory VALUES (1, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (2, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (3, 2)");
+
+ st.executeUpdate(
+ " CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY)");
+
+ st.executeUpdate("INSERT INTO timeslots VALUES(1)");
+ st.executeUpdate("INSERT INTO timeslots VALUES(2)");
+
+ st.executeUpdate(
+ " create table reservations(slotno INT CONSTRAINT "
+ + "timeslots_fk REFERENCES timeslots, "
+ + "itemno INT CONSTRAINT inventory_fk REFERENCES inventory, "
+ + "name VARCHAR(100), resdate DATE)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(1, 1, 'Joe', '2000-04-14')");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(1, 1, 'Fred', '2000-04-13')");
+
+ // This query used to cause a null pointer exception
+
+ rs = st.executeQuery(
+ "select name, resdate "
+ + "from (reservations left outer join (inventory join "
+ + "timeslots on inventory.itemno = timeslots.slotno)"
+ + "on inventory.itemno = reservations.itemno and "
+ + "timeslots.slotno = reservations.slotno)"
+ + "where resdate = '2000-04-14'");
+
+ expColNames = new String [] {"NAME", "RESDATE"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"Joe", "2000-04-14"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testdDerby2923() throws Exception
+ {
+ // bug 2923, cross join under an outer join
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate(
+ "create table inventory(itemno INT NOT NULL PRIMARY "
+ + "KEY, capacity INT)");
+
+ st.executeUpdate("INSERT into inventory values (1, 4)");
+ st.executeUpdate("INSERT into inventory values (2, 2)");
+ st.executeUpdate("INSERT into inventory values (3, 2)");
+
+ st.executeUpdate(
+ " CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY)");
+
+ st.executeUpdate("INSERT INTO timeslots VALUES(1)");
+ st.executeUpdate("INSERT INTO timeslots VALUES(2)");
+
+ st.executeUpdate(
+ " create table reservations(slotno INT CONSTRAINT "
+ + "timeslots_fk REFERENCES timeslots,"
+ + "itemno INT CONSTRAINT inventory_fk REFERENCES inventory,"
+ + "name VARCHAR(100))");
+
+ st.executeUpdate("INSERT INTO reservations VALUES(1, 1, 'Joe')");
+ st.executeUpdate("INSERT INTO reservations VALUES(2, 2, 'Fred')");
+
+ // This query used to get incorrect results when name is
+ // null was the 2nd predicate due to a bug in OJ->IJ xform
+ // code.
+
+ rs = st.executeQuery(
+ "select timeslots.slotno, inventory.itemno, capacity, name"
+ + " from inventory left outer join timeslots"
+ + " on inventory.capacity = timeslots.slotno"
+ + " left outer join reservations"
+ + " on timeslots.slotno = reservations.slotno"
+ + " where capacity > 3 and name is null");
+
+ expColNames = new String [] {"SLOTNO", "ITEMNO", "CAPACITY", "NAME"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, "1", "4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select timeslots.slotno, inventory.itemno, capacity, name"
+ + " from inventory left outer join timeslots"
+ + " on inventory.capacity = timeslots.slotno"
+ + " left outer join reservations"
+ + " on timeslots.slotno = reservations.slotno"
+ + " where name is null and capacity > 3");
+
+ expColNames = new String [] {"SLOTNO", "ITEMNO", "CAPACITY", "NAME"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, "1", "4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testdDerby2930() throws Exception
+ {
+
+ // bug 2930, cross join under outer join
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ SQLWarning sqlWarn = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate(
+ "CREATE TABLE properties ("
+ + " name VARCHAR(50),"
+ + " value VARCHAR(200))");
+
+ st.executeUpdate(
+ " INSERT INTO properties VALUES ('businessName', "
+ + "'Cloud 9 Cafe')");
+
+ st.executeUpdate(
+ " INSERT INTO properties VALUES "
+ + "('lastReservationDate', '2001-12-31')");
+
+ st.executeUpdate(
+ " CREATE TABLE inventory ("
+ + " itemno INT NOT NULL PRIMARY KEY,"
+ + " capacity INT"
+ + ")");
+
+ st.executeUpdate("INSERT INTO inventory VALUES (1, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (2, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (3, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (4, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (5, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (6, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (7, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (8, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (9, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (10, 4)");
+
+ st.executeUpdate(
+ " CREATE TABLE timeslots (slot TIME NOT NULL PRIMARY KEY)");
+
+ st.executeUpdate("INSERT INTO timeslots VALUES('17:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('17:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('18:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('18:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('19:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('19:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('20:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('20:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('21:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('21:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('22:00:00')");
+
+ st.executeUpdate(
+ " CREATE TABLE reservations ("
+ + " itemno INT CONSTRAINT inventory_fk REFERENCES inventory,"
+ + " slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots,"
+ + " resdate DATE NOT NULL,"
+ + " name VARCHAR(100) NOT NULL,"
+ + " quantity INT,"
+ + " CONSTRAINT reservations_u UNIQUE(name, resdate))");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(6, '17:00:00', "
+ + "'2000-07-13', 'Williams', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(7, '17:00:00', "
+ + "'2000-07-13', 'Johnson', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(8, '17:00:00', "
+ + "'2000-07-13', 'Allen', 3)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(9, '17:00:00', "
+ + "'2000-07-13', 'Dexmier', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(1, '17:30:00', "
+ + "'2000-07-13', 'Gates', 2)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(2, '17:30:00', "
+ + "'2000-07-13', 'McNealy', 2)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(3, '17:30:00', "
+ + "'2000-07-13', 'Hoffman', 1)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(4, '17:30:00', "
+ + "'2000-07-13', 'Sippl', 2)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(6, '17:30:00', "
+ + "'2000-07-13', 'Yang', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(7, '17:30:00', "
+ + "'2000-07-13', 'Meyers', 4)");
+
+ rs = st.executeQuery(
+ " select max(name), max(resdate) from inventory join "
+ + "timeslots on inventory.capacity is not null "
+ + "left outer join reservations on inventory.itemno = "
+ + "reservations.itemno and reservations.slot = timeslots.slot");
+
+ expColNames = new String [] {"1", "2"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"Yang", "2000-07-13"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select max(name), max(resdate) from inventory join "
+ + "timeslots on inventory.capacity is not null "
+ + "left outer join reservations on inventory.itemno = "
+ + "reservations.itemno and reservations.slot = timeslots.slot");
+
+ rs.next();
+ // This causes the warning to be generated now.
+ if (usingEmbedded())
+ {
+ sqlWarn = rs.getWarnings();
+ assertNotNull("Expected warning but found none", sqlWarn);
+ //Warning 01003:Null values were eliminated from the
+ //argument of a column function
+ assertSQLState("01003", sqlWarn);
+ }
+ assertEquals("Yang", rs.getString(1));
+ assertEquals("2000-07-13", rs.getString(2));
+
+ }
+
+ public void testdDerby2931() throws Exception
+ {
+
+ // bug 2931, cross join under outer join
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate(
+ "CREATE TABLE properties ("
+ + " name VARCHAR(50),"
+ + " value VARCHAR(200))");
+
+ st.executeUpdate(
+ " INSERT INTO properties VALUES ('businessName', "
+ + "'Cloud 9 Cafe')");
+
+ st.executeUpdate(
+ " INSERT INTO properties VALUES "
+ + "('lastReservationDate', '2001-12-31')");
+
+ st.executeUpdate(
+ " CREATE TABLE inventory ("
+ + " itemno INT NOT NULL PRIMARY KEY,"
+ + " capacity INT"
+ + ")");
+
+ st.executeUpdate("INSERT INTO inventory VALUES (1, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (2, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (3, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (4, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (5, 2)");
+ st.executeUpdate("INSERT INTO inventory VALUES (6, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (7, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (8, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (9, 4)");
+ st.executeUpdate("INSERT INTO inventory VALUES (10, 4)");
+
+ st.executeUpdate(
+ " CREATE TABLE timeslots (slot TIME NOT NULL PRIMARY KEY)");
+
+ st.executeUpdate("INSERT INTO timeslots VALUES('17:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('17:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('18:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('18:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('19:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('19:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('20:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('20:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('21:00:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('21:30:00')");
+ st.executeUpdate("INSERT INTO timeslots VALUES('22:00:00')");
+
+ st.executeUpdate(
+ " CREATE TABLE reservations ("
+ + " itemno INT CONSTRAINT inventory_fk REFERENCES inventory,"
+ + " slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots,"
+ + " resdate DATE NOT NULL,"
+ + " name VARCHAR(100) NOT NULL,"
+ + " quantity INT,"
+ + " CONSTRAINT reservations_u UNIQUE(name, resdate))");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(6, '17:00:00', "
+ + "'2000-07-13', 'Williams', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(7, '17:00:00', "
+ + "'2000-07-13', 'Johnson', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(8, '17:00:00', "
+ + "'2000-07-13', 'Allen', 3)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(9, '17:00:00', "
+ + "'2000-07-13', 'Dexmier', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(1, '17:30:00', "
+ + "'2000-07-13', 'Gates', 2)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(2, '17:30:00', "
+ + "'2000-07-13', 'McNealy', 2)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(3, '17:30:00', "
+ + "'2000-07-13', 'Hoffman', 1)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(4, '17:30:00', "
+ + "'2000-07-13', 'Sippl', 2)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(6, '17:30:00', "
+ + "'2000-07-13', 'Yang', 4)");
+
+ st.executeUpdate(
+ " INSERT INTO reservations VALUES(7, '17:30:00', "
+ + "'2000-07-13', 'Meyers', 4)");
+
+ // this query should return values from the 'slot' column
+ // (type date) but it seems to be returning integers!
+
+ rs = st.executeQuery(
+ "select max(timeslots.slot) from inventory inner "
+ + "join timeslots on inventory.capacity is not null "
+ + "left outer join reservations on inventory.capacity "
+ + "= reservations.itemno and reservations.slot = timeslots.slot");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"22:00:00"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testdDerby2897() throws Exception
+ {
+ // bug 2897 Push join predicates from where clause to right
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+ rs = st.executeQuery(
+ "select * from t1 inner join t2 on 1=1 left outer "
+ + "join t3 on t1.c1 = t3.c1 "
+ + "where t1.c1 = t2.c1");
+
+ expColNames = new String [] {"C1", "C1", "C1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", null},
+ {"3", "3", "3"},
+ {"3", "3", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+
+ rs.next();
+
+ if(usingEmbedded()){
+ RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ }
+ rs.close();
+ }
+
+ public void TestdDerby5659() throws SQLException
+ {
+ // Test fix for bug 5659
+
+ ResultSet rs = null;
+ Statement st = createStatement();
+ String [][] expRS;
+ String [] expColNames;
+
+ st.executeUpdate(
+ "create table xxx (a int not null)");
+
+ st.executeUpdate(
+ " create table yyy (a int not null)");
+
+ st.executeUpdate(
+ " insert into xxx values (1)");
+
+ rs = st.executeQuery(
+ " select * from xxx left join yyy on (xxx.a=yyy.a)");
+
+ expColNames = new String [] {"A", "A"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ assertStatementError("23502", st,
+ " insert into xxx values (null)");
+
+ rs = st.executeQuery(
+ " select * from xxx");
+
+ expColNames = new String [] {"A"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testdDerby5658() throws Exception
+ {
+ // Defect 5658. Disable querries with ambiguous references.
+
+ Statement st = createStatement();
+ ResultSet rs = null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate(
+ "create table ttab1 (a int, b int)");
+
+ st.executeUpdate(
+ " insert into ttab1 values (1,1),(2,2)");
+
+ st.executeUpdate(
+ " create table ttab2 (c int, d int)");
+
+ st.executeUpdate(
+ " insert into ttab2 values (1,1),(2,2)");
+
+ // DERBY-4380: These statements used to raise an error
+ // because more than one object table includes column "b".
+ // But the scope of the ON clauses makes it clear which
+ // table they belong to in each case, so they should not fail.
+
+ rs = st.executeQuery(
+ "select cor1.*, cor2.* from ttab1 cor1 left outer "
+ + "join ttab2 on (b = d),"
+ + " ttab1 left outer join ttab2 cor2 on (b = d)");
+
+ expColNames = new String [] {"A", "B", "C", "D"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1"},
+ {"1", "1", "2", "2"},
+ {"2", "2", "1", "1"},
+ {"2", "2", "2", "2"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ rs = st.executeQuery(
+ " select cor1.*, cor2.* from ttab1 cor1 left outer "
+ + "join ttab2 on (b = d),"
+ + " ttab1 left outer join ttab2 cor2 on (b = cor2.d)");
+
+ expColNames = new String [] {"A", "B", "C", "D"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1"},
+ {"1", "1", "2", "2"},
+ {"2", "2", "1", "1"},
+ {"2", "2", "2", "2"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // This should pass
+
+ rs = st.executeQuery(
+ "select cor1.*, cor2.* from ttab1 left outer join "
+ + "ttab2 on (b = d), "
+ + " ttab1 cor1 left outer join ttab2 cor2 on (cor1.b = cor2.d)");
+
+ expColNames = new String [] {"A", "B", "C", "D"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1"},
+ {"2", "2", "2", "2"},
+ {"1", "1", "1", "1"},
+ {"2", "2", "2", "2"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+
+ // These should fail too
+
+ assertStatementError("42X03", st,
+ "select * from ttab1, ttab1 left outer join ttab2 on (a=c)");
+
+ assertStatementError("42X04", st,
+ " select * from ttab1 cor1, ttab1 left outer join "
+ + "ttab2 on (cor1.a=c)");
+
+ // This should pass
+
+ rs = st.executeQuery(
+ "select * from ttab1, ttab1 cor1 left outer join "
+ + "ttab2 on (cor1.a=c)");
+
+ expColNames = new String [] {"A", "B", "A", "B", "C", "D"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1", "1", "1", "1", "1"},
+ {"2", "2", "1", "1", "1", "1"},
+ {"1", "1", "2", "2", "2", "2"},
+ {"2", "2", "2", "2", "2", "2"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS, true);
+ }
+
+ public void testdDerby5164() throws Exception
+ {
+ // Test 5164
+
+ Statement st = createStatement();
+ createTestObjects(st);
+ ResultSet rs=null;
+ String [][] expRS;
+ String [] expColNames;
+
+ createTestObjects(st);
+
+ st.executeUpdate(
+ "CREATE TABLE \"APP\".\"GOVT_AGCY\" (\"GVA_ID\" "
+ + "NUMERIC(20,0) NOT NULL, \"GVA_ORL_ID\" "
+ + "NUMERIC(20,0) NOT NULL, \"GVA_GAC_ID\" NUMERIC(20,0))");
+
+ st.executeUpdate(
+ " CREATE TABLE \"APP\".\"GEO_STRC_ELMT\" (\"GSE_ID\" "
+ + "NUMERIC(20,0) NOT NULL, \"GSE_GSET_ID\" "
+ + "NUMERIC(20,0) NOT NULL, \"GSE_GA_ID_PRNT\" "
+ + "NUMERIC(20,0) NOT NULL, \"GSE_GA_ID_CHLD\" "
+ + "NUMERIC(20,0) NOT NULL)");
+
+ st.executeUpdate(
+ " CREATE TABLE \"APP\".\"GEO_AREA\" (\"GA_ID\" "
+ + "NUMERIC(20,0) NOT NULL, \"GA_GAT_ID\" NUMERIC(20,0) "
+ + "NOT NULL, \"GA_NM\" VARCHAR(30) NOT NULL, "
+ + "\"GA_ABRV_NM\" VARCHAR(5))");
+
+ st.executeUpdate("CREATE TABLE \"APP\".\"REG\" "
+ +"(\"REG_ID\" NUMERIC(20,0) NOT NULL, \"REG_NM\" "
+ +"VARCHAR(60) NOT NULL, \"REG_DESC\" VARCHAR(240), "
+ +"\"REG_ABRV_NM\" VARCHAR(15), \"REG_CD\" "
+ +"NUMERIC(8,0) NOT NULL, \"REG_STRT_DT\" TIMESTAMP NOT NULL, "
+ +"\"REG_END_DT\" TIMESTAMP NOT NULL DEFAULT "
+ + "'"+Timestamp.valueOf("2009-12-05 01:29:59")+"',"
+ +"\"REG_EMPR_LIAB_IND\" CHAR(1) NOT NULL DEFAULT 'N', "
+ +"\"REG_PAYR_TAX_SURG_CRTF_IND\" CHAR(1) NOT NULL DEFAULT 'N', "
+ +"\"REG_PYT_ID\" NUMERIC(20,0), \"REG_GA_ID\" NUMERIC(20,0) NOT NULL, "
+ +"\"REG_GVA_ID\" NUMERIC(20,0) NOT NULL, \"REG_REGT_ID\" NUMERIC(20,0) NOT NULL, "
+ +"\"REG_PRNT_ID\" NUMERIC(20,0))");
+
+ rs=st.executeQuery("SELECT 1 FROM reg "
+ +"JOIN geo_area jrsd ON (jrsd.ga_id = reg.reg_ga_id) "
+ +"LEFT OUTER JOIN geo_strc_elmt gse ON (gse.gse_ga_id_chld =reg.reg_ga_id) "
+ +"LEFT OUTER JOIN geo_area prnt ON (prnt.ga_id =reg.reg_ga_id) "
+ +"JOIN govt_agcy gva ON (reg.reg_gva_id = gva.gva_id)");
+
+ expColNames=new String[]{"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS=new String[][]{};
+ JDBC.assertFullResultSet(rs, expRS,true);
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=919250&r1=919249&r2=919250&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Fri Mar 5 00:49:29 2010
@@ -76,8 +76,9 @@
suite.addTest(InbetweenTest.suite());
suite.addTest(InsertTest.suite());
suite.addTest(JoinTest.suite());
- suite.addTest(LangScripts.suite());
+ suite.addTest(LangScripts.suite());
suite.addTest(MathTrigFunctionsTest.suite());
+ suite.addTest(OuterJoinTest.suite());
suite.addTest(PredicateTest.suite());
suite.addTest(PrepareExecuteDDL.suite());
suite.addTest(ReferentialActionsTest.suite());