db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject 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
Date Fri, 05 Mar 2010 00:49:29 GMT
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());



Mime
View raw message