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 da...@apache.org on 2010/08/28 00:35:40 UTC
svn commit: r990292 [2/3] - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/suites/
testing/org/apache/derbyTesting/functionTest...
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LojReorderTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LojReorderTest.java?rev=990292&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LojReorderTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LojReorderTest.java Fri Aug 27 22:35:39 2010
@@ -0,0 +1,23877 @@
+/*
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.LojReorderTest
+
+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 junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Semi-automated rewrite of the original test harness/ij based test:
+ * lojreorder.sql
+ */
+public final class LojReorderTest extends BaseJDBCTestCase {
+
+ /**
+ * Public constructor required for running test as standalone JUnit.
+ */
+ public LojReorderTest(String name)
+ {
+ super(name);
+ }
+
+ public static Test suite()
+ {
+ TestSuite suite = new TestSuite("LojReorderTest Test");
+ suite.addTest(TestConfiguration.defaultSuite(LojReorderTest.class));
+ return suite;
+ }
+
+
+ public void test_LojReorderTest_a() throws Exception
+ {
+ ResultSet rs = null;
+ CallableStatement cSt;
+ Statement st = createStatement();
+
+ String [][] expRS;
+ String [] expColNames;
+
+ setAutoCommit(false);
+
+ // This test LOJ reordering. Many testcases are derived from
+ // DB2 UDB FVT testcases. Cliff Leung. 10/2002
+ st.executeUpdate(
+ "CREATE TABLE T (A INT NOT NULL, B DECIMAL(10,3) NOT "
+ + "NULL, C VARCHAR(5) NOT NULL)");
+
+ st.executeUpdate(
+ " INSERT INTO T VALUES (1, 1.0, '1'), (2, 2.0, '2'), "
+ + "(3, 3.0, '3')");
+
+ st.executeUpdate(
+ " CREATE TABLE S (D INT NOT NULL, E DECIMAL(10,3) "
+ + "NOT NULL, F VARCHAR(5) NOT NULL)");
+
+ st.executeUpdate(
+ " INSERT INTO S VALUES (2, 2.0, '2'), (3, 3.0, '3'), "
+ + "(4, 4.0, '4')");
+
+ st.executeUpdate(
+ " CREATE TABLE R (G INT NOT NULL, H DECIMAL(10,3) "
+ + "NOT NULL, I VARCHAR(5) NOT NULL)");
+
+ st.executeUpdate(
+ " INSERT INTO R VALUES (3, 3.0, '3'), (4, 4.0, '4'), "
+ + "(5, 5.0, '5')");
+
+ st.executeUpdate(
+ " CREATE TABLE TT (J INT NOT NULL, K DECIMAL(10,3) "
+ + "NOT NULL, L VARCHAR(5) NOT NULL)");
+
+ st.executeUpdate(
+ " INSERT INTO TT VALUES (2, 2.0, '2'), (3, 3.0, "
+ + "'3'), (4, 4.0, '4')");
+
+ rs = st.executeQuery(
+ " select * from t left outer join s on (b = e)");
+
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\" from t left outer "
+ + "join s on (b = e)");
+
+ expColNames = new String [] {"A", "E", "F", "A+E"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null, null, null},
+ {"2", "2.000", "2", "4.000"},
+ {"3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t right outer join s on (b = e)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3"},
+ {null, null, null, "4", "4.000", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\" from t right outer "
+ + "join s on (b = e)");
+
+ expColNames = new String [] {"A", "E", "F", "A+E"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000"},
+ {"3", "3.000", "3", "6.000"},
+ {null, "4.000", "4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\" from t left outer "
+ + "join s on (b = e) where d > 0");
+
+ expColNames = new String [] {"A", "E", "F", "A+E"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000"},
+ {"3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\" from t right outer "
+ + "join s on (b = e) where d > 0");
+
+ expColNames = new String [] {"A", "E", "F", "A+E"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000"},
+ {"3", "3.000", "3", "6.000"},
+ {null, "4.000", "4", null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // LOJ and LOJ
+
+ rs = st.executeQuery(
+ "select a, e, f, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) left outer join r on (f = i)");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null, null, null, null},
+ {"2", "2.000", "2", "4.000", null},
+ {"3", "3.000", "3", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) left outer join r on (f = "
+ + "i) where a>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000", null},
+ {"3", "3.000", "3", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, d, e, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) left outer join r on (f = "
+ + "i) where d>1");
+
+ expColNames = new String [] {"A", "D", "E", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2", "2.000", "4.000", null},
+ {"3", "3", "3.000", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, d, e, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) left outer join r on (f = "
+ + "i) where h>1");
+
+ expColNames = new String [] {"A", "D", "E", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3", "3.000", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // LOJ and ROJ
+
+ rs = st.executeQuery(
+ "select a, e, f, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) right outer join r on (f = i)");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, null, null, null, "4"},
+ {null, null, null, null, "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) right outer join r on (f = "
+ + "i) where a>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) right outer join r on (f = "
+ + "i) where d>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t left "
+ + "outer join s on (b = e)) right outer join r on (f = "
+ + "i) where h>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, null, null, null, "4"},
+ {null, null, null, null, "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // ROJ and ROJ
+
+ rs = st.executeQuery(
+ "select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) right outer join r on (f = i)");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, "4.000", "4", null, "4"},
+ {null, null, null, null, "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) right outer join r on (f = "
+ + "i) where a>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) right outer join r on (f = "
+ + "i) where d>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, "4.000", "4", null, "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) right outer join r on (f = "
+ + "i) where h>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, "4.000", "4", null, "4"},
+ {null, null, null, null, "5"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // ROJ and LOJ
+
+ rs = st.executeQuery(
+ "select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) left outer join r on (f = i)");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000", null},
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, "4.000", "4", null, "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) left outer join r on (f = "
+ + "i) where a>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000", null},
+ {"3", "3.000", "3", "6.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) left outer join r on (f = "
+ + "i) where d>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "4.000", null},
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, "4.000", "4", null, "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, e, f, a+e as \"A+E\", g from (t right "
+ + "outer join s on (b = e)) left outer join r on (f = "
+ + "i) where h>1");
+
+ expColNames = new String [] {"A", "E", "F", "A+E", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "6.000", "3"},
+ {null, "4.000", "4", null, "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // NO LOJ reordering
+
+ rs = st.executeQuery(
+ "select * from (t left outer join s on (b = e)) left "
+ + "outer join r on (f = i) where a > 0");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from (t left outer join s on (b = e)) "
+ + "inner join r on (f = i)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from (t left outer join s on (b = e)) "
+ + "inner join r on (f = i) where a > 0");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from (t inner join s on (b = e)) inner "
+ + "join r on (f = i) where a > 0");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from (t inner join s on (b = e)) left "
+ + "outer join r on (f = i) where a > 0");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t, s left outer join r on (d = g) where a = e");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join s on (b = e), r where a = g");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // bug 5658 In DB2 UDB, any column referenced in an
+ // expression of the join-condition must be a column of
+ // one of the operand tables of the associated join (in
+ // the scope of the same joined-table clause). DERBY-4380:
+ // This query used to fail because column B was seen as
+ // ambiguous (could belong to both T1 and T2). However, the
+ // scope of the ON clause makes it unabiguous; the first
+ // occurrence of B must be T1.B, and the second one must be
+ // T2.B.
+
+ rs = st.executeQuery(
+ "select t1.*, s2.* from t t1 left outer join s on (b "
+ + "= e), t t2 left outer join s s2 on (b = e)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null},
+ {"1", "1.000", "1", "2", "2.000", "2"},
+ {"1", "1.000", "1", "3", "3.000", "3"},
+ {"2", "2.000", "2", null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2"},
+ {"2", "2.000", "2", "3", "3.000", "3"},
+ {"3", "3.000", "3", null, null, null},
+ {"3", "3.000", "3", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // a view of a regular join
+
+ st.executeUpdate(
+ "create view jv (fv, ev, dv, cv, bv, av) as (select "
+ + "f, e, d, c, b, a from t, s where b = e)");
+
+ rs = st.executeQuery(
+ " select * from jv left outer join r on (fv = i)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from r left outer join jv on (fv = i)");
+
+ expColNames = new String [] {"G", "H", "I", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null},
+ {"5", "5.000", "5", null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // a view of LOJ
+
+ st.executeUpdate(
+ "create view lojv (fv, ev, dv, cv, bv, av) as "
+ + "(select f, e, d, c, b, a from t left outer join s on b = e)");
+
+ rs = st.executeQuery(
+ " select * from r left outer join lojv on (fv = i)");
+
+ expColNames = new String [] {"G", "H", "I", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null},
+ {"5", "5.000", "5", null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from r right outer join lojv on (fv = i)");
+
+ expColNames = new String [] {"G", "H", "I", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, null, null, null, null, "1", "1.000", "1"},
+ {null, null, null, "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // a view on top of a LOJ view
+
+ st.executeUpdate(
+ "create view vv as (select * from lojv)");
+
+ rs = st.executeQuery(
+ " select * from r left outer join vv on (fv = i)");
+
+ expColNames = new String [] {"G", "H", "I", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null},
+ {"5", "5.000", "5", null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from r right outer join vv on (fv = i)");
+
+ expColNames = new String [] {"G", "H", "I", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, null, null, null, null, "1", "1.000", "1"},
+ {null, null, null, "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // check for ON predicate for LOJ reordering: no reordering
+
+ // Update: With DERBY-4471, this query also gets reordered, cf plan
+ // check below.
+
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");assertUpdateCount(cSt, 0);
+
+ rs = st.executeQuery(
+ "select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (b = e and a > b)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", null, null, null, null, null, null},
+ {"3", "3.000", "3", null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ JDBC.checkPlan(st,
+ new String[] {
+ "Hash Left Outer Join ResultSet:",
+ "Left result set:",
+ "_Hash Left Outer Join ResultSet:",
+ "_Left result set:",
+ "__Table Scan ResultSet for T",
+ "_Right result set:",
+ "__Project-Restrict ResultSet (5):",
+ "__Source result set:",
+ "___Hash Scan ResultSet for S",
+ "Right result set:",
+ "_Hash Scan ResultSet for R"});
+
+ // Update: With DERBY-4471, this query also gets reordered, cf plan
+ // check below.
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (b = e and a = b)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ JDBC.checkPlan(st,
+ new String[] {
+ "Hash Left Outer Join ResultSet:",
+ "Left result set:",
+ "_Hash Left Outer Join ResultSet:",
+ "_Left result set:",
+ "__Table Scan ResultSet for T",
+ "_Right result set:",
+ "__Project-Restrict ResultSet (5):",
+ "__Source result set:",
+ "___Hash Scan ResultSet for S",
+ "Right result set:",
+ "_Hash Scan ResultSet for R"});
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (b = e and 1 = 1)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ JDBC.checkPlan(st,
+ new String[] {
+ "Hash Left Outer Join ResultSet:",
+ "Left result set:",
+ "_Hash Left Outer Join ResultSet:",
+ "_Left result set:",
+ "__Table Scan ResultSet for T",
+ "_Right result set:",
+ "__Project-Restrict ResultSet (5):",
+ "__Source result set:",
+ "___Hash Scan ResultSet for S",
+ "Right result set:",
+ "_Hash Scan ResultSet for R"});
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (b > e)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", null, null, null, null, null, null},
+ {"3", "3.000", "3", "2", "2.000", "2", null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ JDBC.checkPlan(st,
+ new String[] {
+ "Hash Left Outer Join ResultSet:",
+ "Left result set:",
+ "_Nested Loop Left Outer Join ResultSet:",
+ "_Left result set:",
+ "__Table Scan ResultSet for T",
+ "_Right result set:",
+ "__Table Scan ResultSet for S",
+ "Right result set:",
+ "_Hash Scan ResultSet for R"});
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (a = a)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "2", "2.000", "2", null, null, null},
+ {"1", "1.000", "1", "3", "3.000", "3", "3", "3.000", "3"},
+ {"1", "1.000", "1", "4", "4.000", "4", "4", "4.000", "4"},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"2", "2.000", "2", "3", "3.000", "3", "3", "3.000", "3"},
+ {"2", "2.000", "2", "4", "4.000", "4", "4", "4.000", "4"},
+ {"3", "3.000", "3", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"3", "3.000", "3", "4", "4.000", "4", "4", "4.000", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (a = 1)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "2", "2.000", "2", null, null, null},
+ {"1", "1.000", "1", "3", "3.000", "3", "3", "3.000", "3"},
+ {"1", "1.000", "1", "4", "4.000", "4", "4", "4.000", "4"},
+ {"2", "2.000", "2", null, null, null, null, null, null},
+ {"3", "3.000", "3", null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (1 = 1)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "2", "2.000", "2", null, null, null},
+ {"1", "1.000", "1", "3", "3.000", "3", "3", "3.000", "3"},
+ {"1", "1.000", "1", "4", "4.000", "4", "4", "4.000", "4"},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"2", "2.000", "2", "3", "3.000", "3", "3", "3.000", "3"},
+ {"2", "2.000", "2", "4", "4.000", "4", "4", "4.000", "4"},
+ {"3", "3.000", "3", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"3", "3.000", "3", "4", "4.000", "4", "4", "4.000", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // check for ON predicate for LOJ reordering: reordering
+ // is done
+
+ rs = st.executeQuery(
+ "select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (a = d and b = e and c = f)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on ((a = d and b = e) and c = f)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (a = d and (b = e and c = f))");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (not(a <> d or b <> e or c <> f))");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // check for LOJ reorder with subquery
+
+ rs = st.executeQuery(
+ "select * from t left outer join (s left outer join "
+ + "r on (f = i)) on (a = d) where a in (select j from tt)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // LOJ involving the same table
+
+ rs = st.executeQuery(
+ "select * from t t1 left outer join (t t2 left outer "
+ + "join t t3 on (t2.a=t3.a)) on (t1.a=t2.a)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "A", "B", "C"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", "1", "1.000", "1"},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t t1 left outer join (t t2 left "
+ + "outer join t t3 on (t2.a=t3.a)) on (t1.b=t2.b)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "A", "B", "C"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", "1", "1.000", "1"},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t t1 left outer join (t t2 left "
+ + "outer join t t3 on (t2.a=t3.b)) on (t1.a=t2.b)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "A", "B", "C"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", "1", "1.000", "1"},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // comparing columns of different types.
+
+ rs = st.executeQuery(
+ "select t.a, s.d, r.g from t left outer join (s left "
+ + "outer join r on (e=g)) on (b=d)");
+
+ expColNames = new String [] {"A", "D", "G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", null, null},
+ {"2", "2", null},
+ {"3", "3", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select r.g from t left outer join (s left outer "
+ + "join r on (e=g)) on (b=d)");
+
+ expColNames = new String [] {"G"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null},
+ {null},
+ {"3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // these seem to work
+
+ rs = st.executeQuery(
+ "select * from t left outer join (s left outer join "
+ + "r on (e=g)) on (b=d)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select t.a from t left outer join (s left outer "
+ + "join r on (e=g)) on (b=d)");
+
+ expColNames = new String [] {"A"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"2"},
+ {"3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select s.f, s.e, s.d, t.c, t.b, t.a from t left "
+ + "outer join (s left outer join r on (e=g)) on (b=d)");
+
+ expColNames = new String [] {"F", "E", "D", "C", "B", "A"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, null, "1", "1.000", "1"},
+ {"2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // need to test more on changes deep inside...
+
+ st.executeUpdate(
+ "create view loj (a, b, c, d, e, f, g, h, i, ae) as "
+ + "(select a, b, c, d, e, f, g, h, i, a+e as \"A+E\" "
+ + "from t left outer join (s left outer join r on (f = "
+ + "i)) on (b = e))");
+
+ rs = st.executeQuery(
+ " select * from loj");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "G", "H", "I", "AE"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null, "4.000"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join loj on (t.a=loj.a)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "D", "E", "F", "G", "H", "I", "AE"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null, "4.000"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join loj on (t.a=loj.d)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "D", "E", "F", "G", "H", "I", "AE"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null, "4.000"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t right outer join loj on (t.a=loj.a)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "D", "E", "F", "G", "H", "I", "AE"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null, "4.000"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t inner join loj on (t.a=loj.a)");
+
+ expColNames = new String [] {"A", "B", "C", "A", "B", "C", "D", "E", "F", "G", "H", "I", "AE"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null, "4.000"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "6.000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // 4 level deep b=e will be pushed first, one level down
+ // j=a will then be pushed next, 2 levels down (after b=e
+ // pushed)
+
+ rs = st.executeQuery(
+ "select * from tt left outer join (t left outer join "
+ + "(s left outer join r on (f = i)) on (b = e)) on (j=a)");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join (s left outer join r on (f = i)) on (b = e)) "
+ + " on (j=a) where j>0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join (s left outer join r on (f = i)) on (b = e)) "
+ + " on (j=a) where j>0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join (s left outer join r on (f = i)) on (b = e)) on (j=d)");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // TT LOJ (T LOJ (S LOJ R))) becomes TT LOJ ((T LOJ S) LOJ R)
+
+ rs = st.executeQuery(
+ "select 1 from tt left outer join (t left outer join "
+ + "(s left outer join r on (f = i)) on (b = e)) on (j=r.g)");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"1"},
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select 1 from tt right outer join (t left outer "
+ + "join (s left outer join r on (f = i)) on (b = e)) on (j=g)");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"1"},
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // okay
+
+ rs = st.executeQuery(
+ "select 1 from tt, (t left outer join (s left outer "
+ + "join r on (f = i)) on (b = e)) where (j=g)");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select 1 from tt right outer join (t left outer "
+ + "join (s inner join r on (f = i)) on (b = e)) on (j=g)");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"},
+ {"1"},
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select 1 from tt inner join (t left outer join (s "
+ + "left outer join r on (f = i)) on (b = e)) on (j=g)");
+
+ expColNames = new String [] {"1"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // LOJ w/ left-deep LOJ: LOJ will be pushed twice
+
+ rs = st.executeQuery(
+ "select * from tt left outer join (t left outer join "
+ + "s on (b=e) left outer join r on (f = i)) on (j=a)");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=a) where j > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=a) where b > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=a) where e > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=a) where g > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select j+1, k+2, L||'s', a+10, b+10, C||'t', d+20, "
+ + "e+20, f||'u', g+30, h+30, i||'v' from tt left outer "
+ + "join (t left outer join s on (b=e) left outer join "
+ + "r on (f = i)) on (j=a)");
+
+ expColNames = new String [] {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "4.000", "2s", "12", "12.000", "2t", "22", "22.000", "2u", null, null, null},
+ {"4", "5.000", "3s", "13", "13.000", "3t", "23", "23.000", "3u", "33", "33.000", "3v"},
+ {"5", "6.000", "4s", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select j, j+1, k, k+2, L, L||'s' from tt left "
+ + "outer join (t left outer join s on (b=e) left outer "
+ + "join r on (f = i)) on (j=a)");
+
+ expColNames = new String [] {"J", "2", "K", "4", "L", "6"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "3", "2.000", "4.000", "2", "2s"},
+ {"3", "4", "3.000", "5.000", "3", "3s"},
+ {"4", "5", "4.000", "6.000", "4", "4s"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select a, a+10, b, b+10, C, C||'t' from tt left "
+ + "outer join (t left outer join s on (b=e) left outer "
+ + "join r on (f = i)) on (j=a)");
+
+ expColNames = new String [] {"A", "2", "B", "4", "C", "6"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "12", "2.000", "12.000", "2", "2t"},
+ {"3", "13", "3.000", "13.000", "3", "3t"},
+ {null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select d, d+20, e, e+20, f, f||'u' from tt left "
+ + "outer join (t left outer join s on (b=e) left outer "
+ + "join r on (f = i)) on (j=a)");
+
+ expColNames = new String [] {"D", "2", "E", "4", "F", "6"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "22", "2.000", "22.000", "2", "2u"},
+ {"3", "23", "3.000", "23.000", "3", "3u"},
+ {null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select g, g+30, h, h+30, i, i||'v' from tt left "
+ + "outer join (t left outer join s on (b=e) left outer "
+ + "join r on (f = i)) on (j=a)");
+
+ expColNames = new String [] {"G", "2", "H", "4", "I", "6"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, null, null, null, null},
+ {"3", "33", "3.000", "33.000", "3", "3v"},
+ {null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on (j=d)");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=d) where j > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=d) where b > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=d) where e > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=d) where g > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on (j=g)");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", null, null, null, null, null, null, null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=g) where j > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", null, null, null, null, null, null, null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=g) where b > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=g) where e > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from tt left outer join (t left outer "
+ + "join s on (b=e) left outer join r on (f = i)) on "
+ + "(j=g) where g > 0");
+
+ expColNames = new String [] {"J", "K", "L", "A", "B", "C", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // Create views of union, regular joins, subquery, group
+ // by, having clause, and use the view in the LOJ operands
+ // and yet the LOJ reordering is applicable. We need to
+ // test the join condition: LHS=RHS.
+
+ st.executeUpdate(
+ "create view v1 (fv, ev, dv, cv, bv, av) as (select "
+ + "f, e, d, c, b, a from t left outer join s on b = e)");
+
+ st.executeUpdate(
+ " create view v2 (fv, ev, dv, cv, bv, av) as (select "
+ + "f, e, d, c, b, a from t inner join s on b = e)");
+
+ st.executeUpdate(
+ " create view v3 (fv, ev, dv, cv, bv, av) as (select "
+ + "f, e, d, c, b, a from t right join s on b = e)");
+
+ st.executeUpdate(
+ " create view v4 (fv, ev, dv, cv, bv, av) as (select "
+ + "f, e, d, c, b, a from t, s where b = e)");
+
+ st.executeUpdate(
+ " create view v5 (cv, bv, cnt) as (select c, b, "
+ + "count(*) from t group by c, b)");
+
+ st.executeUpdate(
+ " create view v6 (cv, bv, cnt) as (select c, b, "
+ + "count(*) from t group by c, b having b > 0)");
+
+ st.executeUpdate(
+ " create view v7 (cv, bv, av) as (select c, b, a "
+ + "from t where b in (select e from s))");
+
+ st.executeUpdate(
+ " create view v8 (cv, bv, av) as (select c, b, a "
+ + "from t union select f, e, d from s)");
+
+ // All these test cases need table references to determine
+ // if the join condition is proper for LOJ reordering: LHS
+ // = RHS
+
+ rs = st.executeQuery(
+ "select * from v1 left outer join (s left outer join "
+ + "r on (f = i)) on (d=v1.av)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, null, "1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v2 left outer join (s left outer "
+ + "join r on (f = i)) on (d=v2.av)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v3 left outer join (s left outer "
+ + "join r on (f = i)) on (d=v3.av)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", null, null, null, null, null, null, null, null, null}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v4 left outer join (s left outer "
+ + "join r on (f = i)) on (d=v4.av)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v5 left outer join (s left outer "
+ + "join r on (f = i)) on (e=v5.bv)");
+
+ expColNames = new String [] {"CV", "BV", "CNT", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "1", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "1", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v6 left outer join (s left outer "
+ + "join r on (f = i)) on (e=v6.bv)");
+
+ expColNames = new String [] {"CV", "BV", "CNT", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "1", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "1", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v7 left outer join (s left outer "
+ + "join r on (f = i)) on (e=v7.bv)");
+
+ expColNames = new String [] {"CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v8 left outer join (s left outer "
+ + "join r on (f = i)) on (e=v8.bv)");
+
+ expColNames = new String [] {"CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"},
+ {"4", "4.000", "4", "4", "4.000", "4", "4", "4.000", "4"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "v1 on (f = cv)) on (d=a)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, "1", "1.000", "1", null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "v8 on (f = cv)) on (d=a)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, "1", "1.000", "1"},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (v1 left outer "
+ + "join s on (f = cv)) on (av=a)");
+
+ expColNames = new String [] {"A", "B", "C", "FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (v8 left outer "
+ + "join s on (f = cv)) on (av=a)");
+
+ expColNames = new String [] {"A", "B", "C", "CV", "BV", "AV", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", "1", "1.000", "1", null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // No LOJ reordering due to join conditions
+
+ rs = st.executeQuery(
+ "select * from v1 left outer join (s left outer join "
+ + "r on (f = i)) on (g=v1.av)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {null, null, null, "1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null, null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from v2 left outer join (s left outer "
+ + "join r on (f = i)) on (g=v2.av)");
+
+ expColNames = new String [] {"FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F", "G", "H", "I"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"2", "2.000", "2", "2", "2.000", "2", null, null, null, null, null, null},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "v1 on (f = cv)) on (av=a)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "FV", "EV", "DV", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (s left outer join "
+ + "v8 on (f = cv)) on (av=a)");
+
+ expColNames = new String [] {"A", "B", "C", "D", "E", "F", "CV", "BV", "AV"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (v1 left outer "
+ + "join s on (f = cv)) on (d=a)");
+
+ expColNames = new String [] {"A", "B", "C", "FV", "EV", "DV", "CV", "BV", "AV", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ rs = st.executeQuery(
+ " select * from t left outer join (v8 left outer "
+ + "join s on (f = cv)) on (d=a)");
+
+ expColNames = new String [] {"A", "B", "C", "CV", "BV", "AV", "D", "E", "F"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "1.000", "1", null, null, null, null, null, null},
+ {"2", "2.000", "2", "2", "2.000", "2", "2", "2.000", "2"},
+ {"3", "3.000", "3", "3", "3.000", "3", "3", "3.000", "3"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ // values clause select * from table (values (1, 2, 3)) as
+ // q(a, b, c)
+
+ st.executeUpdate(
+ "drop view v1");
+
+ st.executeUpdate(
+ " drop view v2");
+
+ st.executeUpdate(
+ " drop view v3");
+
+ st.executeUpdate(
+ " drop view v4");
+
+ st.executeUpdate(
+ " drop view v5");
+
+ st.executeUpdate(
+ " drop view v6");
+
+ st.executeUpdate(
+ " drop view v7");
+
+ st.executeUpdate(
+ " drop view v8");
+
+ st.executeUpdate(
+ " drop view loj");
+
+ st.executeUpdate(
+ " drop view vv");
+
+ st.executeUpdate(
+ " drop view lojv");
+
+ st.executeUpdate(
+ " drop view jv");
+
+ st.executeUpdate(
+ " DROP TABLE T");
+
+ st.executeUpdate(
+ " DROP TABLE S");
+
+ st.executeUpdate(
+ " DROP TABLE R");
+
+ st.executeUpdate(
+ " DROP TABLE TT");
+
+ st.close();
+ rollback();
+ }
+
+
+ public void test_LojReorderTest_b() throws Exception
+ {
+ ResultSet rs = null;
+ CallableStatement cSt;
+ Statement st = createStatement();
+
+ String [][] expRS;
+ String [] expColNames;
+
+ setAutoCommit(false);
+
+ // from the customer
+
+ st.executeUpdate(
+ "CREATE TABLE TNL1 (ID INTEGER NOT NULL UNIQUE, "
+ + "col_char CHAR(20), col_decimal DECIMAL(12,5))");
+
+ st.executeUpdate(
+ " insert into TNL1 values (1, 'abc', 1.1)");
+
+ st.executeUpdate(
+ " insert into TNL1 values (2, 'bcd', 2.2)");
+
+ st.executeUpdate(
+ " insert into TNL1 values (3, 'cde', 3.3)");
+
+ st.executeUpdate(
+ " insert into TNL1 values (4, 'ABC', 1.1)");
+
+ st.executeUpdate(
+ " insert into TNL1 values (5, 'BCD', 2.2)");
+
+ st.executeUpdate(
+ " insert into TNL1 values (6, 'CDE', 3.3)");
+
+ st.executeUpdate(
+ " CREATE TABLE TNL1_1 (ID INTEGER NOT NULL UNIQUE, "
+ + "col_char CHAR(20), col_decimal DECIMAL(12,5), ID2 INTEGER)");
+
+ st.executeUpdate(
+ " CREATE INDEX I_TNL11 ON TNL1_1 (ID2 ASC)");
+
+ st.executeUpdate(
+ " insert into TNL1_1 values (3, 'cde', 3.3, 30)");
+
+ st.executeUpdate(
+ " insert into TNL1_1 values (4, 'xyz', 4.4, 40)");
+
+ st.executeUpdate(
+ " insert into TNL1_1 values (5, 'lmn', 5.5, 50)");
+
+ st.executeUpdate(
+ " insert into TNL1_1 values (6, 'CDE', 3.3, 30)");
+
+ st.executeUpdate(
+ " insert into TNL1_1 values (7, 'XYZ', 4.4, 40)");
+
+ st.executeUpdate(
+ " insert into TNL1_1 values (8, 'LMN', 5.5, 50)");
+
+ st.executeUpdate(
+ " CREATE TABLE TNL1_1_1 (ID INTEGER NOT NULL UNIQUE, "
+ + "col_char CHAR(20), col_decimal DECIMAL(12,5))");
+
+ st.executeUpdate(
+ " insert into TNL1_1_1 values (2, 'bcd', 2.2)");
+
+ st.executeUpdate(
+ " insert into TNL1_1_1 values (3, 'cde', 3.3)");
+
+ st.executeUpdate(
+ " insert into TNL1_1_1 values (4, 'xyz', 4.4)");
+
+ st.executeUpdate(
+ " insert into TNL1_1_1 values (5, 'BCD', 2.2)");
+
+ st.executeUpdate(
+ " insert into TNL1_1_1 values (6, 'CDE', 3.3)");
+
+ st.executeUpdate(
+ " insert into TNL1_1_1 values (7, 'XYZ', 4.4)");
+
+ st.executeUpdate(
+ " CREATE TABLE TNL1_2 (ID INTEGER NOT NULL UNIQUE, "
+ + "col_char CHAR(20), col_decimal DECIMAL(12,5))");
+
+ st.executeUpdate(
+ " insert into TNL1_2 values (4, 'xyz', 4.4)");
+
+ st.executeUpdate(
+ " insert into TNL1_2 values (5, 'lmn', 5.5)");
+
+ st.executeUpdate(
+ " insert into TNL1_2 values (6, 'stu', 6.6)");
+
+ st.executeUpdate(
+ " insert into TNL1_2 values (7, 'XYZ', 4.4)");
+
+ st.executeUpdate(
+ " insert into TNL1_2 values (8, 'LMN', 5.5)");
+
+ st.executeUpdate(
+ " insert into TNL1_2 values (9, 'STU', 6.6)");
+
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");assertUpdateCount(cSt, 0);
+
+
+ rs = st.executeQuery(
+ " SELECT * FROM TNL1 A LEFT OUTER JOIN TNL1_1 B ON "
+ + "A.ID = B.ID LEFT OUTER JOIN TNL1_1_1 C ON "
+ + "B.ID2=C.ID LEFT OUTER JOIN TNL1_2 D ON A.ID=D.ID ORDER BY 1");
+
+ expColNames = new String [] {"ID", "COL_CHAR", "COL_DECIMAL", "ID", "COL_CHAR", "COL_DECIMAL", "ID2", "ID", "COL_CHAR", "COL_DECIMAL", "ID", "COL_CHAR", "COL_DECIMAL"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "abc", "1.10000", null, null, null, null, null, null, null, null, null, null},
+ {"2", "bcd", "2.20000", null, null, null, null, null, null, null, null, null, null},
+ {"3", "cde", "3.30000", "3", "cde", "3.30000", "30", null, null, null, null, null, null},
+ {"4", "ABC", "1.10000", "4", "xyz", "4.40000", "40", null, null, null, "4", "xyz", "4.40000"},
+ {"5", "BCD", "2.20000", "5", "lmn", "5.50000", "50", null, null, null, "5", "lmn", "5.50000"},
+ {"6", "CDE", "3.30000", "6", "CDE", "3.30000", "30", null, null, null, "6", "stu", "6.60000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ JDBC.checkPlan(st,
+ new String[] {
+ "Sort ResultSet:",
+ "In sorted order = false",
+ "Source result set:",
+ "_Hash Left Outer Join ResultSet:",
+ "_Left result set:",
+ "__Hash Left Outer Join ResultSet:",
+ "__Left result set:",
+ "___Hash Left Outer Join ResultSet:",
+ "___Left result set:",
+ "____Table Scan ResultSet for TNL1",
+ "___Right result set:",
+ "____Hash Scan ResultSet for TNL1_1",
+ "__Right result set:",
+ "___Hash Scan ResultSet for TNL1_1_1",
+ "_Right result set:",
+ "__Hash Scan ResultSet for TNL1_2"});
+
+ rs = st.executeQuery(
+ " SELECT * FROM TNL1 A LEFT OUTER JOIN (TNL1_1 B "
+ + "LEFT OUTER JOIN TNL1_1_1 C ON B.ID2=C.ID) ON "
+ + "A.ID=B.ID LEFT OUTER JOIN TNL1_2 D ON A.ID=D.ID ORDER BY 1");
+
+ expColNames = new String [] {"ID", "COL_CHAR", "COL_DECIMAL", "ID", "COL_CHAR", "COL_DECIMAL", "ID2", "ID", "COL_CHAR", "COL_DECIMAL", "ID", "COL_CHAR", "COL_DECIMAL"};
+ JDBC.assertColumnNames(rs, expColNames);
+
+ expRS = new String [][]
+ {
+ {"1", "abc", "1.10000", null, null, null, null, null, null, null, null, null, null},
+ {"2", "bcd", "2.20000", null, null, null, null, null, null, null, null, null, null},
+ {"3", "cde", "3.30000", "3", "cde", "3.30000", "30", null, null, null, null, null, null},
+ {"4", "ABC", "1.10000", "4", "xyz", "4.40000", "40", null, null, null, "4", "xyz", "4.40000"},
+ {"5", "BCD", "2.20000", "5", "lmn", "5.50000", "50", null, null, null, "5", "lmn", "5.50000"},
+ {"6", "CDE", "3.30000", "6", "CDE", "3.30000", "30", null, null, null, "6", "stu", "6.60000"}
+ };
+
+ JDBC.assertFullResultSet(rs, expRS);
+
+ JDBC.checkPlan(
+ st,
+ new String[] {
+ "Sort ResultSet:",
+ "Number of opens = 1",
+ "Rows input = 6",
+ "Rows returned = 6",
+ "Source result set:",
+ "_Hash Left Outer Join ResultSet:",
+ "_Left result set:",
+ "__Hash Left Outer Join ResultSet:",
+ "__Left result set:",
+ "___Hash Left Outer Join ResultSet:",
+ "___Left result set:",
+ "____Table Scan ResultSet for TNL1",
+ "___Right result set:",
+ "____Hash Scan ResultSet for TNL1_1",
+ "__Right result set:",
+ "___Hash Scan ResultSet for TNL1_1_1",
+ "_Right result set:",
+ "__Hash Scan ResultSet for TNL1_2"});
+
+ cSt = prepareCall(
+ " call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");assertUpdateCount(cSt, 0);
+
+ st.executeUpdate(
+ " DROP TABLE TNL1");
+
+ st.executeUpdate(
+ " DROP TABLE TNL1_1");
+
+ st.executeUpdate(
+ " DROP TABLE TNL1_1_1");
+
+ st.executeUpdate(
+ " DROP TABLE TNL1_2");
+ st.close();
+ rollback();
+ }
+
+
+ public void test_LojReorderTest_c() throws SQLException
+ {
+ Statement st = createStatement();
+
+ setAutoCommit(false);
+
+ part_i(st);
+ part_ii(st);
+ part_iii(st);
+ part_iv(st);
+ part_v(st);
+
+ st.close();
+ rollback();
+
+ }
+
+
+ public void part_i(Statement st) throws SQLException
+ {
+ ResultSet rs = null;
+
+ String [][] expRS;
+ String [] expColNames;
+
+ //- outerjn.db
+
+ st.executeUpdate(
+ "CREATE SCHEMA K55ADMIN");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PARTS"
+ + " (PART CHAR(10),"
+ + " NUM SMALLINT,"
+ + " SUPPLIER CHAR(20))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PARTS_T"
+ + " (PART CHAR(10),"
+ + " NUM1 SMALLINT,"
+ + " NUM2 SMALLINT,"
+ + " SUPPLIER CHAR(20))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PARTS_NOTNULL"
+ + " (PART CHAR(10) NOT NULL,"
+ + " NUM SMALLINT NOT NULL,"
+ + " SUPPLIER CHAR(20) NOT NULL)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PARTS_ALLNULL"
+ + " (PART CHAR(10),"
+ + " NUM SMALLINT,"
+ + " SUPPLIER CHAR(20))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PARTS_EMPTY"
+ + " (PART CHAR(10),"
+ + " NUM SMALLINT,"
+ + " SUPPLIER CHAR(20))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PARTS_EMPTY_NN"
+ + " (PART CHAR(10) NOT NULL,"
+ + " NUM SMALLINT NOT NULL,"
+ + " SUPPLIER CHAR(20) NOT NULL)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PRODUCTS"
+ + " (NUM SMALLINT,"
+ + " PRODUCT CHAR(15),"
+ + " PRICE DECIMAL(7,2))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PRODUCTS_T"
+ + " (NUM1 SMALLINT,"
+ + " NUM2 SMALLINT,"
+ + " PRODUCT CHAR(15),"
+ + " PRICE DECIMAL(7,2))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PRODUCTS_NOTNULL"
+ + " (NUM SMALLINT NOT NULL,"
+ + " PRODUCT CHAR(15) NOT NULL,"
+ + " PRICE DECIMAL(7,2) NOT NULL)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PRODUCTS_ALLNULL"
+ + " (NUM SMALLINT,"
+ + " PRODUCT CHAR(15),"
+ + " PRICE DECIMAL(7,2))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PRODUCTS_EMPTY"
+ + " (NUM SMALLINT,"
+ + " PRODUCT CHAR(15),"
+ + " PRICE DECIMAL(7,2))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.PRODUCTS_EMPTY_NN"
+ + " (NUM SMALLINT NOT NULL,"
+ + " PRODUCT CHAR(15) NOT NULL,"
+ + " PRICE DECIMAL(7,2) NOT NULL)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.S90"
+ + " (DEPT CHAR(3) NOT NULL,"
+ + " SALES SMALLINT)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.S91"
+ + " (DEPT CHAR(3),"
+ + " SALES SMALLINT)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.S92"
+ + " (DEPT CHAR(3) NOT NULL,"
+ + " SALES SMALLINT)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.EMPLOYEES"
+ + " (EMP_ID CHAR(6) NOT NULL,"
+ + " EMP_NAME VARCHAR(25),"
+ + " SALARY INTEGER,"
+ + " COMM SMALLINT)");
+
+ st.executeUpdate(
+ " CREATE UNIQUE INDEX K55ADMIN.EMPLOYIX"
+ + " ON K55ADMIN.EMPLOYEES(EMP_ID)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.OLD_OFFICES"
+ + " (OLD_OFFICE CHAR(4) NOT NULL,"
+ + " EMP_ID CHAR(6))");
+
+ st.executeUpdate(
+ " CREATE UNIQUE INDEX k55ADMIN.OLD_OFFIX"
+ + " ON K55ADMIN.OLD_OFFICES(OLD_OFFICE)");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.NEW_OFFICES"
+ + " (NEW_OFFICE CHAR(4) NOT NULL,"
+ + " EMP_ID CHAR(6))");
+
+ st.executeUpdate(
+ " CREATE UNIQUE INDEX k55ADMIN.NEW_OFFIX"
+ + " ON K55ADMIN.NEW_OFFICES(NEW_OFFICE)");
+
+ //*********************************************************
+ // **************** This last group is for general usage
+ // throughout the tests
+ // *********************************************************
+ // ****************
+
+ st.executeUpdate(
+ "CREATE TABLE K55ADMIN.MANYTYPES"
+ + " (INTCOL INTEGER,"
+ + " SMINTCOL SMALLINT,"
+ + " DEC62COL DECIMAL(6,2),"
+ + " DEC72COL DECIMAL(7,2),"
+ + " FLOATCOL FLOAT,"
+ + " CHARCOL CHAR(10),"
+ + " LCHARCOL CHAR(250),"
+ + " VCHARCOL VARCHAR(100))");
+
+ st.executeUpdate(
+ " CREATE TABLE K55ADMIN.MANYTYPES_NOTNULL"
+ + " (INTCOL INTEGER NOT NULL,"
+ + " SMINTCOL SMALLINT NOT NULL,"
+ + " DEC62COL DECIMAL(6,2) NOT NULL,"
+ + " DEC72COL DECIMAL(7,2) NOT NULL,"
+ + " FLOATCOL FLOAT NOT NULL,"
+ + " CHARCOL CHAR(15) NOT NULL,"
+ + " LCHARCOL CHAR(250) NOT NULL,"
+ + " VCHARCOL VARCHAR(100) NOT NULL)");
+
+ //* CREATE TABLE K55ADMIN.MANYTYPES_CTRL LIKE
+ // MANYTYPES_NOTNULL
+
+ st.executeUpdate(
+ "CREATE TABLE K55ADMIN.MANYTYPES_CTRL"
+ + " (INTCOL INTEGER NOT NULL,"
+ + " SMINTCOL SMALLINT NOT NULL,"
+ + " DEC62COL DECIMAL(6,2) NOT NULL,"
+ + " DEC72COL DECIMAL(7,2) NOT NULL,"
+ + " FLOATCOL FLOAT NOT NULL,"
+ + " CHARCOL CHAR(15) NOT NULL,"
+ + " LCHARCOL CHAR(250) NOT NULL,"
[... 21433 lines stripped ...]