db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrn...@apache.org
Subject svn commit: r1242409 [2/3] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/master/ functionTests/suites/ functionTests/tests/lang/ junit/
Date Thu, 09 Feb 2012 17:36:00 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryTest.java?rev=1242409&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SubqueryTest.java Thu Feb  9 17:36:00 2012
@@ -0,0 +1,2125 @@
+/*
+ *
+ * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SubqueryTest
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, 
+ * software distributed under the License is distributed on an 
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
+ * either express or implied. See the License for the specific 
+ * language governing permissions and limitations under the License.
+ */
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
+import org.apache.derbyTesting.junit.Utilities;
+
+/**
+ * Test case for subquery.sql.
+ */
+public class SubqueryTest extends BaseJDBCTestCase {
+
+    public SubqueryTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+
+        Properties props = new Properties();
+
+        props.setProperty("derby.language.statementCacheSize", "0");
+        return new DatabasePropertyTestSetup(
+            new SystemPropertyTestSetup(new CleanDatabaseTestSetup(
+               new TestSuite(SubqueryTest.class, "SubqueryTest")) {
+
+                    /**
+                     * @see org.apache.derbyTesting.junit.CleanDatabaseTestSetup#decorateSQL(java.sql.Statement)
+                     */
+                    protected void decorateSQL(Statement s) throws SQLException {
+                        s.execute(
+                            "CREATE FUNCTION ConsistencyChecker() " +
+                            "RETURNS VARCHAR(128) " +
+                            "EXTERNAL NAME " +
+                            "'org.apache.derbyTesting.functionTests." +
+                            "util.T_ConsistencyChecker.runConsistencyChecker' " +
+                            "LANGUAGE JAVA PARAMETER STYLE JAVA");
+                        s.execute("create table s " +
+                            "(i int, s smallint, c char(30), " +
+                                "vc char(30), b bigint)");
+                        s.execute("create table t " +
+                            "(i int, s smallint, c char(30), " +
+                                "vc char(30), b bigint)");
+                        s.execute("create table tt " +
+                            "(ii int, ss smallint, cc char(30), " +
+                                "vcvc char(30), b bigint)");
+                        s.execute("create table ttt " +
+                            "(iii int, sss smallint, ccc char(30), " +
+                                "vcvcvc char(30))");
+                        // populate the tables
+                        s.execute("insert into s values " +
+                            "(null, null, null, null, null)");
+                        s.execute("insert into s values (0, 0, '0', '0', 0)");
+                        s.execute("insert into s values (1, 1, '1', '1', 1)");
+                        s.execute("insert into t values " +
+                            "(null, null, null, null, null)");
+                        s.execute("insert into t values (0, 0, '0', '0', 0)");
+                        s.execute("insert into t values (1, 1, '1', '1', 1)");
+                        s.execute("insert into t values (1, 1, '1', '1', 1)");
+                        s.execute("insert into t values (2, 2, '2', '2', 1)");
+                        s.execute("insert into tt values " +
+                            "(null, null, null, null, null)");
+                        s.execute("insert into tt values (0, 0, '0', '0', 0)");
+                        s.execute("insert into tt values (1, 1, '1', '1', 1)");
+                        s.execute("insert into tt values (1, 1, '1', '1', 1)");
+                        s.execute("insert into tt values (2, 2, '2', '2', 1)");
+                        s.execute("insert into ttt values (null, null, null, null)");
+                        s.execute("insert into ttt values (11, 11, '11', '11')");
+                        s.execute("insert into ttt values (11, 11, '11', '11')");
+                        s.execute("insert into ttt values (22, 22, '22', '22')");
+                    }
+
+                }, props), props, true);
+    }
+
+    /**
+     * exists non-correlated negative tests "mis"qualified all
+     * 
+     * @throws Exception
+     */
+    public void testExistsNonCorrelated() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+        assertStatementError("42X10", st,
+        "select * from s where exists (select tt.* from t)");
+
+        assertStatementError("42X10", st,
+            "select * from s where exists (select t.* from t tt)");
+
+        // invalid column reference in select list
+        assertStatementError("42X04", st,
+            "select * from s where exists (select nosuchcolumn from t)");
+
+        // multiple matches at subquery level
+        assertStatementError("42X03", st,
+            "select * from s where exists (select i from s, t)");
+
+        // ? parameter in select list of exists subquery
+        assertStatementError("42X34", st,
+            "select * from s where exists (select ? from s)");
+
+        // positive tests
+        // qualified *
+        rs = st.executeQuery(
+            "select * from s where exists (select s.* from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s t where exists (select t.* from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s u where exists (select u.* from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // column reference in select list
+        rs = st.executeQuery("select * from s where exists (select i from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where exists (select t.i from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where exists (select i, s from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // subquery returns empty result set
+        rs = st.executeQuery(
+            "select * from s where exists (select * from t where i = -1)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        // test semantics of AnyResultSet
+        rs = st.executeQuery(
+            "select * from s where exists (select t.* from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where exists (select 0 from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // subquery in derived table
+        rs = st.executeQuery(
+            "select * from (select * from s where exists " +
+                "(select * from t) and i = 0) a");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "0", "0", "0", "0", "0" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // exists under an OR
+        rs = st.executeQuery(
+            "select * from s where 0=1 or exists (select * from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where 1=1 or exists " +
+                "(select * from t where 0=1)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where exists (select * from t "
+                + "where 0=1) or exists (select * from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(" select * from s where exists (select * from t "
+                + "where exists (select * from t where 0=1) or exists "
+                + "(select * from t))");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // (exists empty set) is null
+        rs = st.executeQuery("select * from s where (exists (select * from t "
+                + "where 0=1)) is null");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // Not exists
+        rs = st.executeQuery(
+            "select * from s where not exists (select * from t)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select * from s where not exists " +
+                "(select * from t where i = -1)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs.close();
+        st.close();
+    }
+
+    /**
+     * expression subqueries non-correlated negative tests all node
+     * 
+     * @throws Exception
+     */
+    public void testExpressionNonCorrelated() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        assertStatementError("42X38", st,
+            "select * from s where i = (select * from t)");
+
+        // too many columns in select list
+        assertStatementError("42X39", st,
+            "select * from s where i = (select i, s from t)");
+
+        // no conversions
+        assertStatementError("21000", st,
+            "select * from s where i = (select 1 from t)");
+
+        assertStatementError("21000", st,
+            "select * from s where i = (select b from t)");
+
+        // ? parameter in select list of expression subquery
+        assertStatementError("42X34", st,
+            "select * from s where i = (select ? from t)");
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+
+        expColNames = new String[] { "1" };
+        JDBC.assertColumnNames(rs, expColNames);
+
+        // cardinality violation
+        assertStatementError("21000", st,
+            "select * from s where i = (select i from t)");
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+        expColNames = new String[] { "1" };
+        JDBC.assertColumnNames(rs, expColNames);
+
+        assertStatementError("21000", st,
+            "select * from s where s = (select s from t where s = 1)");
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+        expColNames = new String[] { "1" };
+        JDBC.assertColumnNames(rs, expColNames);
+        if (usingEmbedded()) {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        } else {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        }
+
+        assertStatementError("21000", st,
+            "update s set b = (select max(b) from t) where vc " +
+                "<> (select vc from t where vc = '1')");
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+        expColNames = new String[] { "1" };
+        JDBC.assertColumnNames(rs, expColNames);
+        if (usingEmbedded()) {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        } else {
+            expRS = new String[][] 
+                {  { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        }
+
+        assertStatementError("21000", st,
+            "delete from s where c = (select c from t where c = '1')");
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+        expColNames = new String[] { "1" };
+        JDBC.assertColumnNames(rs, expColNames);
+        if (usingEmbedded()) {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        } else {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        }
+
+        // positive tests
+
+        rs = st.executeQuery("select * from s");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(" select * from t");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { null, null, null, null, null },
+                { "0", "0", "0", "0", "0" }, { "1", "1", "1", "1", "1" },
+                { "1", "1", "1", "1", "1" }, { "2", "2", "2", "2", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Testing simple subquery for each data type
+     * 
+     * @throws Exception
+     */
+    public void testSimpleSubquery() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select * from s where i = (select i from t where i = 0)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "0", "0", "0", "0", "0" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where s = (select s from t where s = 0)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "0", "0", "0", "0", "0" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where c = (select c from t where c = '0')");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "0", "0", "0", "0", "0" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where vc = " +
+                "(select vc from t where vc = '0')");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "0", "0", "0", "0", "0" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where b = " +
+                "(select max(b) from t where b = 0)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "0", "0", "0", "0", "0" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where b = " +
+                "(select max(b) from t where i = 2)");
+        expColNames = new String[] { "I", "S", "C", "VC", "B" };
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String[][] { { "1", "1", "1", "1", "1" } };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * ? parameter on left hand side of expression subquery
+     * @throws Exception
+     */
+    public void testParameterOnLeft()throws Exception {
+        Statement st = createStatement();
+        PreparedStatement pSt;
+        ResultSetMetaData rsmd;
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        pSt = prepareStatement(
+            "select * from s where ? = (select i from t where i = 0)");
+
+        rs = st.executeQuery("values (0)");
+        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 [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+        };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Testing conversions
+     * @throws Exception
+     */
+    public void testConversions()throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select * from s where i = (select s from t where s = 0)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where s = (select i from t where i = 0)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where c = (select vc from t where vc = '0')");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where vc = (select c from t where c = '0')");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * (select nullable_column ...) is null On of each data 
+     * type to test clone()
+     * @throws Exception
+     */
+    public void testClone() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+                "select * from s where (select s from s where i is "
+                + "null) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                                };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where " +
+                "(select i from s where i is null) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                                };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where " +
+            "(select c from s where i is null) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where " +
+            "(select vc from s where i is null) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where " +
+            "(select b from s where i is null) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from s where " +
+                "(select 1 from t where exists " +
+                    "(select * from t where 1 = 0) and s = -1) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Test subquery in subqueries
+     * @throws Exception
+     */
+    public void testSubqueryInSubquery() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+        
+        rs = st.executeQuery("select * from s where " +
+            "(select i from t where i = 0) = (select s from t where s = 0)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // multiple subqueries at the same level
+        rs = st.executeQuery("select * from s where i = " +
+            "(select s from t where s = 0) " +
+            "and s = (select i from t where i = 2)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery("select * from s where i = " +
+            "(select s from t where s = 0) " +
+            "and s = (select i from t where i = 0)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // nested subqueries
+
+        rs = st.executeQuery(
+            "select * from s where i = " +
+                "(select i from t where s = " +
+                "(select i from t where s = 2))");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select * from s where i = " +
+                "(select i - 1 from t where s = " +
+                "(select i from t where s = 2))");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1", "1", "1", "1", "1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Test expression subqueries in select list
+     * @throws Exception
+     */
+    public void testSubqueriesInSelect() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+        
+        rs = st.executeQuery("select (select i from t where 0=1) from s");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {null}, {null}, {null} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select " +
+            "(select i from t where i = 2) * " +
+            "(select s from t where i = 2) from s " +
+            "where i > " +
+                "(select i from t where i = 0) - " +
+                "(select i from t where i = 0)");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"4"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // in subqueries negative tests select * subquery
+
+        assertStatementError("42X38", st,
+            "select * from s where s in (select * from s)");
+        
+        // incompatable types
+        rs = st.executeQuery(
+            "select * from s where s in (select b from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Test constants in left, right and both sides of the subquery
+     * @throws Exception
+     */
+    public void testConstants() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        // positive tests constants on left side of subquery
+
+        rs = st.executeQuery(
+            "select * from s where 1 in (select s from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where -1 in (select i from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select * from s where '1' in (select vc from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where 0 in (select b from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // constants in subquery select list
+        rs = st.executeQuery(
+            "select * from s where i in (select 1 from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where i in (select -1 from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select * from s where c in (select '1' from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1", "1", "1", "1", "1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where b in (select 0 from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // constants on both sides
+        rs = st.executeQuery(
+            "select * from s where 0 in (select 0 from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // compatable types
+
+        rs = st.executeQuery(
+            "select * from s where c in (select vc from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where vc in (select c from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where i in (select s from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where s in (select i from t)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * empty subquery result set
+     * @throws Exception
+     */
+    public void testEmptyResultSet() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select * from s where i in (select i from t where 1 = 0)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select * from s where (i in " +
+                "(select i from t where i = 0)) is null");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+    }
+
+    /**
+     * Test subqueries in select list
+     */
+    public void testSubqueriesInSelectList() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select ( i in (select i from t) ) a from s order by a");            
+        expColNames = new String [] {"A"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {{"false"}, {"true"}, {"true"}};
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select " +
+            "( i in (select i from t where 1 = 0) ) a from s order by a");            
+        expColNames = new String [] {"A"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {{"false"}, {"false"}, {"false"}};
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select " +
+            "( (i in " +
+                "(select i from t where 1 = 0)) is null ) a " +
+                "from s order by a");
+        expColNames = new String [] {"A"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {{"false"}, {"false"}, {"false"}};
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * subquery under an or
+     * @throws Exception
+     */
+    public void testSubqueryUnderOR() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+        
+        rs = st.executeQuery(
+            "select i from s where i = -1 or i in (select i from t)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"}, {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select i from s where i = 0 or i in " +
+                "(select i from t where i = -1)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select i from s where i = -1 or i in " +
+                "(select i from t where i = -1 or i = 1)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * distinct elimination
+     * @throws Exception
+     */
+    public void testDistinct() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+        
+        rs = st.executeQuery(
+            "select i from s where i in (select i from s)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"}, {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select i from s where i in (select distinct i from s)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"}, {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select i from s ss where i in " +
+                "(select i from s where s.i = ss.i)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"}, {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select i from s ss where i in " +
+                "(select distinct i from s where s.i = ss.i)");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"}, {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        // Utilities.showResultSet(rs);
+        if (usingEmbedded()) {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        } else {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        }
+    }
+
+    /**
+     * Test Matches
+     * 
+     */
+    public void testMatches() throws Exception {
+        Statement st = createStatement();
+        // correlated subqueries negative tests multiple matches 
+        // at parent level
+
+        assertStatementError("42X03", st,
+        "select * from s, t where exists (select i from tt)");
+
+        // match is against base table, but not derived column list
+
+        assertStatementError("42X04", st,
+                "select * from s ss (c1, c2, c3, c4, c5) where "
+                + "exists (select i from tt)");
+
+        assertStatementError("42X04", st,
+                " select * from s ss (c1, c2, c3, c4, c5) where "
+                + "exists (select ss.i from tt)");
+
+        // correlation name exists at both levels, but only column 
+        // match is at parent level
+
+        assertStatementError("42X04", st,
+        "select * from s where exists (select s.i from tt s)");
+
+        // only match is at peer level
+
+        assertStatementError("42X04", st,
+                "select * from s where exists (select * from tt) and "
+                + "exists (select ii from t)");
+
+        assertStatementError("42X04", st,
+                " select * from s where exists (select * from tt) "
+                + "and exists (select tt.ii from t)");
+
+        // correlated column in a derived table
+
+        assertStatementError("42X04", st,
+        "select * from s, (select * from tt where i = ii) a");
+
+        assertStatementError("42X04", st,
+        " select * from s, (select * from tt where s.i = ii) a");
+    }
+
+    /**
+     * Test Simple correlated subqueries
+     */
+    public void testSimpleCorrelated() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+        
+        // positive tests simple correlated subqueries
+
+        rs = st.executeQuery(
+            "select (select i from tt where ii = i and ii <> 1) from s");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {null}, {"0"}, {null} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select " +
+            "(select s.i from tt where ii = s.i and ii <> 1) from s");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {null}, {"0"}, {null} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select (select s.i from ttt where iii = i) from s");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {null}, {null}, {null} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where exists " +
+                "(select * from tt where i = ii and ii <> 1)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where exists " +
+                "(select * from tt where s.i = ii and ii <> 1)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where exists " +
+                "(select * from ttt where i = iii)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        // 1 case where we get a cardinality violation after a few 
+        // rows
+        try{
+            rs = st.executeQuery(
+            "select (select i from tt where ii = i) from s");
+        }catch(SQLException sqle){
+            BaseJDBCTestCase.assertSQLState(
+                "Scalar subquery is only allowed to return a single row.","21000",sqle);
+        }
+
+        // skip levels to find match
+        rs = st.executeQuery(
+            "select * from s where exists (select * from ttt "
+                + "where iii = (select 11 from tt where ii = i and ii <> 1))");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * join in subquery
+     * @throws Exception
+     */
+    public void testJoinInSubqueries() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select * from s where i in " +
+                "(select i from t, tt where s.i <> i and i = ii)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select * from s where i in " +
+                "(select i from t, ttt where s.i < iii and s.i = t.i)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // join in outer query block
+        rs = st.executeQuery(
+            "select s.i, t.i from s, t where exists " +
+                "(select * from ttt where iii = 1)");
+        expColNames = new String [] {"I", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select s.i, t.i from s, t where exists " +
+                "(select * from ttt where iii = 11)");
+        expColNames = new String [] {"I", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null},
+                {null, "0"},
+                {null, "1"},
+                {null, "1"},
+                {null, "2"},
+                {"0", null},
+                {"0", "0"},
+                {"0", "1"},
+                {"0", "1"},
+                {"0", "2"},
+                {"1", null},
+                {"1", "0"},
+                {"1", "1"},
+                {"1", "1"},
+                {"1", "2"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // joins in both query blocks
+        rs = st.executeQuery(
+            "select s.i, t.i from s, t where t.i = " +
+                "(select iii from ttt, tt where iii = t.i)");
+        expColNames = new String [] {"I", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+            "select s.i, t.i from s, t " +
+                "where t.i = (select ii from ttt, tt " +
+                    "where s.i = t.i and t.i = tt.ii " +
+                    "and iii = 22 and ii <> 1)");
+        expColNames = new String [] {"I", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Test proper caching of subqueries in prepared statements
+     * This section (old Cloudscape reference 'Beetle 5382') tests the fix for
+     * a problem where sub-queries were executed not once per execution of the
+     * statement, but only once, when the statement was first executed. 
+     * If the parameter changed between executions or if the data changed 
+     * between executions then the top level select returned the wrong results.
+
+     * @throws Exception
+     */
+    public void testSubqueriesInPS() throws Exception {
+        Statement st = createStatement();
+        PreparedStatement pSt;
+        ResultSetMetaData rsmd;
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        pSt = prepareStatement(
+            "select s.i from s where s.i in " +
+                "(select s.i from s, t where s.i = t.i and t.s = ?)");
+        rs = st.executeQuery("values(0)");
+        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 [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        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 [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        setAutoCommit(false);
+        
+        pSt = prepareStatement(
+            "select s.i from s where s.i in " +
+                "(select s.i from s, t where s.i = t.i and t.s = 3)");
+
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        setAutoCommit(false);
+        
+        st.executeUpdate("insert into t(i,s) values(1,3)");
+
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rollback();
+    }
+
+    /**
+     * correlated subquery in select list of a derived table
+     * @throws Exception
+     */
+    public void testSubuqeryInSelectListOfDerivedTable() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select * from " +
+                "(select (select iii from ttt " +
+                    "where  sss > i and " +
+                    "sss = iii and iii <> 11) " +
+                "from s) a");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {null}, {"22"}, {"22"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // bigint and subqueries
+
+        st.executeUpdate("create table li(i int, s smallint, l bigint)");
+        st.executeUpdate("insert into li values (null, null, null)");
+        st.executeUpdate("insert into li values (1, 1, 1)");
+        st.executeUpdate("insert into li values (2, 2, 2)");
+
+        rs = st.executeQuery(
+            "select l from li o where l = " +
+                "(select i from li i where o.l = i.i)");
+        expColNames = new String [] {"L"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select l from li o where l = " +
+                "(select s from li i where o.l = i.s)");
+        expColNames = new String [] {"L"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select l from li o where l = " +
+                "(select l from li i where o.l = i.l)");
+        expColNames = new String [] {"L"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select l from li where l in (select i from li)");
+        expColNames = new String [] {"L"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select l from li where l in (select s from li)");
+        expColNames = new String [] {"L"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select l from li where l in (select l from li)");
+        expColNames = new String [] {"L"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * Some extra tests for subquery flattening on table expressions 
+     * (remapColumnReferencesToExpressions() binary list node
+     * @throws Exception
+     */
+    public void testSubqueryFlattening() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select i in (1,2) from (select i from s) as tmp(i)");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] {{null}, {"false"}, {"true"}};
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // conditional expression
+        assertStatementError("42X01", st,
+            "select i = 1 ? 1 : i from (select i from s) as tmp(i)");
+
+        // more tests for correlated column resolution
+
+        rs = st.executeQuery(
+            "select * from s where i = (values i)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select t.* from s, t where t.i = (values s.i)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from s where i in (values i)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select t.* from s, t where t.i in (values s.i)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * tests for not needing to do cardinality check
+     * @throws Exception
+     */
+    public void testNoNeedForCardinalityCheck() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select * from s where i = " +
+                "(select min(i) from s where i is not null)");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"0", "0", "0", "0", "0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertStatementError("21000", st,
+            "select * from s where i = (select min(i) from s group by i)");
+
+        // tests for distinct expression subquery
+
+        st.executeUpdate("create table dist1 (c1 int)");
+        st.executeUpdate("create table dist2 (c1 int)");
+        st.executeUpdate("insert into dist1 values null, 1, 2");
+        st.executeUpdate("insert into dist2 values null, null");
+
+        // no match, no violation
+        rs = st.executeQuery(
+            "select * from dist1 where c1 = " +
+                "(select distinct c1 from dist2)");
+        expColNames = new String [] {"C1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        // violation
+
+        st.executeUpdate("insert into dist2 values 1");
+
+        assertStatementError("21000", st,
+            "select * from dist1 where c1 = " +
+                "(select distinct c1 from dist2)");
+
+        // match, no violation
+
+        assertUpdateCount(st, 3, "update dist2 set c1 = 2");
+
+        rs = st.executeQuery(
+            "select * from dist1 where c1 = " +
+                "(select distinct c1 from dist2)");
+        expColNames = new String [] {"C1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table dist1");
+        st.executeUpdate("drop table dist2");
+
+        // update
+
+        st.executeUpdate("create table u " +
+            "(i int, s smallint, c char(30), vc char(30), b bigint)");
+        st.executeUpdate("insert into u select * from s");
+
+        rs = st.executeQuery("select * from u");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertStatementError("42821", st,
+            "update u set b = exists " +
+                "(select b from t) where " +
+                    "vc <> (select vc from s where vc = '1')");
+
+        rs = st.executeQuery("select * from u");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        assertUpdateCount(st, 3,"delete from u");
+
+        st.executeUpdate("insert into u select * from s");
+
+        // delete
+
+        assertUpdateCount(st, 2,
+            "delete from u where c < (select c from t where c = '2')");
+
+        rs = st.executeQuery("select * from u");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // restore u
+
+        assertUpdateCount(st, 1, "delete from u");
+
+        st.executeUpdate("insert into u select * from s");
+    }
+
+    /**
+     * check clean up when errors occur in subqueries insert
+     * @throws Exception
+     */
+    public void testErrorsInNestedSubqueries() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        assertStatementError("22012", st,
+            "insert into u select * from s s_outer where i = " +
+                "(select s_inner.i/(s_inner.i-1) from s s_inner " +
+                    "where s_outer.i = s_inner.i)");
+
+        rs = st.executeQuery("select * from u");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // delete
+
+        assertStatementError("22012", st,
+            "delete from u " +
+                "where i = (select i/(i-1) from s where u.i = s.i)");
+        
+        rs = st.executeQuery("select * from u");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // update
+
+        assertStatementError("22012", st,
+            "update u  set i = (select i from s where u.i = s.i) " +
+                "where i = (select i/(i-1) from s where u.i = s.i)");
+
+        assertStatementError("22012", st,
+            "update u  set i = (select i/i-1 from s where u.i = s.i) " +
+                "where i = (select i from s where u.i = s.i)");
+
+        rs = st.executeQuery("select * from u");
+        expColNames = new String [] {"I", "S", "C", "VC", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {null, null, null, null, null},
+                {"0", "0", "0", "0", "0"},
+                {"1", "1", "1", "1", "1"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // error in nested subquery
+        assertStatementError("21000", st,
+            "select (select (select (select i from s) from s) from s) from s");
+
+        // do consistency check on scans, etc.
+        rs = st.executeQuery("values ConsistencyChecker()");
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        if (usingEmbedded()) {
+            expRS = new String[][] 
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        } else {
+            expRS = new String[][]
+                { { "No open scans, etc.\n16 dependencies found" } };
+            JDBC.assertFullResultSet(rs, expRS, true);
+        }
+
+        // reset autocommit
+        setAutoCommit(true);
+    }
+
+    /**
+     * subquery with groupby and having clause
+     */
+    public void testSubqueryWithClause() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        rs = st.executeQuery(
+            "select distinct vc, i from t as myt1 " +
+                "where s <= (select max(myt1.s) from t as myt2 " +
+                    "where myt1.vc = myt2.vc " +
+                    "and myt1.s <= myt2.s group by s " +
+                    "having count(distinct s) <= 3)");
+        expColNames = new String [] {"VC", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0"},
+                {"1", "1"},
+                {"2", "2"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // subquery with having clause but no groupby
+        rs = st.executeQuery(
+            "select distinct vc, i from t as myt1 " +
+                "where s <= (select max(myt1.s) from t as myt2 " +
+                    "where myt1.vc = myt2.vc and myt1.s <= myt2.s " +
+                    "having count(distinct s) <= 3)");
+        expColNames = new String [] {"VC", "I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"0", "0"},
+                {"1", "1"},
+                {"2", "2"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+    }
+
+    /**
+     * DERBY-1007: Optimizer for subqueries can return 
+     * incorrect cost estimates leading to sub-optimal join 
+     * orders for the outer query.  Before the patch for that 
+     * issue, the following query plan will show T3 first and 
+     * then T1-- but that's determined by the optimizer to be 
+     * the "bad" join order.  After the fix, the join order 
+     * will show T1 first, then T3, which is correct (based on 
+     * the optimizer's estimates).
+     * @throws Exception
+     */
+    public void testDERBY1007() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        st.executeUpdate("create table t_1 (i int, j int)");
+        st.executeUpdate
+            ("insert into T_1 values (1,1), (2,2), (3,3), (4,4), (5,5)");
+        st.executeUpdate("create table t_3 (a int, b int)");
+        st.executeUpdate(
+            "insert into T_3 values (1,1), (2,2), (3,3), (4,4)");
+        st.executeUpdate("insert into t_3 values " +
+            "(6, 24), (7, 28), (8, 32), (9, 36), (10, 40)");
+        st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+        rs = st.executeQuery(
+            "select x1.j, x2.b from (select distinct i,j from t_1) x1, " +
+                "(select distinct a,b from t_3) x2 " +
+                "where x1.i = x2.a order by x1.j, x2.b");
+        expColNames = new String [] {"J", "B"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][]
+                              {
+                {"1", "1"},
+                {"2", "2"},
+                {"3", "3"},
+                {"4", "4"}
+                              };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+        rs.next();
+        String rts = rs.getString(1);
+
+        // Now verify the correct runtimeStatistics output
+        RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rts);
+        // print out the full stats if derby.tests.debug is true
+        println("full stats: \n" + rtsp.toString());
+        // Checking only on the sequence of T3 and T1 scans.
+        // If further checking is needed, uncomment more lines.
+        rtsp.assertSequence( 
+             new String[] {
+                        "Source result set:",
+                        "_Project-Restrict ResultSet (5):",
+                        "_Source result set:",
+                        "__Hash Join ResultSet:",
+                        "__Left result set:",
+                        "___Distinct Scan ResultSet for T_1 at read committed isolation level using instantaneous share row locking:",
+                        "____Bit set of columns fetched=All",
+                        "____Scan type=heap",
+                        "__Right result set:",
+                        "___Hash Table ResultSet (4):",
+                        "___Source result set:",
+                        "____Distinct Scan ResultSet for T_3 at read committed isolation level using instantaneous share row locking:",
+                        "_____Bit set of columns fetched=All",
+                        "_____Scan type=heap"
+                              });
+
+        st.executeUpdate("drop table t_1");
+        st.executeUpdate("drop table t_3");
+    }
+
+    /**
+     * DERBY-781: Materialize subqueries where possible to avoid creating 
+     * invariant result sets many times.  This test case executes a query that
+     * that has subqueries twice: the first time the tables have only a few
+     * rows in them; the second time they have hundreds of rows in them.
+     */
+    public void testDERBY781() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        st.executeUpdate("create table t1 (i int, j int)");
+        st.executeUpdate("create table t2 (i int, j int)");
+        st.executeUpdate
+            ("insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5)");
+        st.executeUpdate
+            ("insert into t2 values (1,1), (2,2), (3,3), (4,4), (5,5)");
+        st.executeUpdate("create table t3 (a int, b int)");
+        st.executeUpdate("create table t4 (a int, b int)");
+        st.executeUpdate
+            ("insert into t3 values (2,2), (4,4), (5,5)");
+        st.executeUpdate
+            ("insert into t4 values (2,2), (4,4), (5,5)");
+    
+        // Use of the term "DISTINCT" makes it so that we don't flatten 
+        // the subqueries.
+        st.executeUpdate("create view V1 as " +
+            "select distinct T1.i, T2.j from T1, T2 where T1.i = T2.i");
+        st.executeUpdate("create view V2 as " +
+            "select distinct T3.a, T4.b from T3, T4 where T3.a = T4.a");
+        st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+
+        /* Run the test query the first time, with only a small number
+         * of rows in each table. Before the patch for DERBY-781
+         * the optimizer would have chosen a nested loop join, which 
+         * means that we would generate the result set for the inner 
+         * view multiple times.  After DERBY-781 the optimizer will 
+         * choose to do a hash join and thereby materialize the inner 
+         * result set, thus improving performance.  Should see a Hash join 
+         * as the top-level join with a HashTableResult as the right child 
+         * of the outermost join.
+         */  
+        rs = st.executeQuery(
+            "select * from V1, V2 where V1.j = V2.b and V1.i in (1,2,3,4,5)");
+            expColNames = new String [] {"I", "J", "A", "B"};
+            JDBC.assertColumnNames(rs, expColNames);
+            expRS = new String [][]
+                                  {
+                    {"2", "2", "2", "2"},
+                    {"4", "4", "4", "4"},
+                    {"5", "5", "5", "5"}
+                                  };
+            JDBC.assertFullResultSet(rs, expRS, true);
+
+            rs = st.executeQuery(
+                "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+            rs.next();
+            String rts = rs.getString(1);
+
+            // Now verify the correct runtimeStatistics output
+            RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rts);
+            // print out the full stats if derby.tests.debug is true
+            println("full stats: \n" + rtsp.toString());
+            // the essentials are getting checked as per the comments
+            // above. If further checking is needed, uncomment more lines.
+            rtsp.assertSequence( 
+                    new String[] {
+                        "Hash Join ResultSet:",
+                        //"Left result set:",
+                        //"_Sort ResultSet:",
+                        //"_Source result set:",
+                        //"__Project-Restrict ResultSet (7):",
+                        //"__Source result set:",
+                        //"___Nested Loop Join ResultSet:",
+                        //"___Left result set:",
+                        //"____Project-Restrict ResultSet (5):",
+                        //"____Source result set:",
+                        //"_____Table Scan ResultSet for T1 at read committed " +
+                        //    "isolation level using share row locking chosen " +
+                        //    "by the optimizer",
+                        //"______Bit set of columns fetched={0}",
+                        //"______Scan type=heap",
+                        //"___Right result set:",
+                        //"____Table Scan ResultSet for T2 at read committed " +
+                        //    "isolation level using share row locking chosen " +
+                        //    "by the optimizer",
+                        //"_____Bit set of columns fetched=All",
+                        //"_____Scan type=heap",
+                        //"______Operator: =",                        
+                        "Right result set:",
+                        "_Hash Table ResultSet (13):"
+                        //"_Source result set:",
+                        //"__Sort ResultSet:",
+                        //"__Source result set:",
+                        //"___Project-Restrict ResultSet (12):",
+                        //"___Source result set:",
+                        //"____Hash Join ResultSet:",
+                        //"____Left result set:",
+                        //"_____Table Scan ResultSet for T3 at read committed " +
+                        //    "isolation level using share row locking chosen " +
+                        //    "by the optimizer",
+                        //"______Bit set of columns fetched=All",
+                        //"______Scan type=heap"
+                        //,
+           // after this, there's something peculiar with the 
+           // 'Right result set' line output, and this RuntimeStatisticsParser
+           // method cannot find any further matches...
+                        //"___Right result set:",
+                        //"_____Hash Scan ResultSet for T4 at read committed " +
+                        //    "isolation level using instantaneous share row" + 
+                        //    "locking: ",
+                        //"______Bit set of columns fetched=All",
+                        //"______Scan type=heap",
+                        //"_______Operator: ="                        
+                            });
+            // ...so checking on the remaining output another way. 
+            assertTrue(rtsp.findString("Right result set:",3));        
+            assertTrue(rtsp.findString("Hash Scan ResultSet for T4 at read " +
+                "committed isolation level using instantaneous share row " +
+                "locking: ",1));        
+            //assertTrue(rtsp.findString("Bit set of columns fetched=All",2));        
+            //assertTrue(rtsp.findString("Scan type=heap",4));        
+            
+            // Now add more data to the tables.
+            st.executeUpdate("insert into t1 select * from t2");
+            st.executeUpdate("insert into t2 select * from t1");
+            st.executeUpdate("insert into t2 select * from t1");
+            st.executeUpdate("insert into t1 select * from t2");
+            st.executeUpdate("insert into t2 select * from t1");
+            st.executeUpdate("insert into t1 select * from t2");
+            st.executeUpdate("insert into t2 select * from t1");
+            st.executeUpdate("insert into t1 select * from t2");
+            st.executeUpdate("insert into t2 select * from t1");
+            st.executeUpdate("insert into t1 select * from t2");
+            st.executeUpdate("insert into t3 select * from t4");
+            st.executeUpdate("insert into t4 select * from t3");
+            st.executeUpdate("insert into t3 select * from t4");
+            st.executeUpdate("insert into t4 select * from t3");
+            st.executeUpdate("insert into t3 select * from t4");
+            st.executeUpdate("insert into t4 select * from t3");
+            st.executeUpdate("insert into t3 select * from t4");
+            st.executeUpdate("insert into t4 select * from t3");
+            st.executeUpdate("insert into t3 select * from t4");
+            st.executeUpdate("insert into t4 select * from t3");
+            st.executeUpdate("insert into t3 select * from t4");
+
+            /* Drop the views and recreate them with slightly different
+             * names.  The reason we use different names is to ensure that
+             * the query will be "different" from the last time and thus we'll 
+             * we'll go through optimization again (instead of just using 
+             * the cached plan from last time). 
+             */
+
+            st.executeUpdate("drop view v1");
+            st.executeUpdate("drop view v2");
+
+            // Use of the term "DISTINCT" makes it so that we don't flatten
+            // the subqueries.
+            st.executeUpdate("create view VV1 as " +
+                "select distinct T1.i, T2.j from T1, T2 where T1.i = T2.i");
+            st.executeUpdate("create view VV2 as " +
+                "select distinct T3.a, T4.b from T3, T4 where T3.a = T4.a");
+            // Now execute the query again using the larger tables.
+            rs = st.executeQuery(
+                "select * from VV1, VV2 " +
+                "where VV1.j = VV2.b and VV1.i in (1,2,3,4,5)");
+            expColNames = new String [] {"I", "J", "A", "B"};
+            JDBC.assertColumnNames(rs, expColNames);
+            expRS = new String [][]
+                                  {
+                    {"2", "2", "2", "2"},
+                    {"4", "4", "4", "4"},
+                    {"5", "5", "5", "5"}
+                                  };
+            JDBC.assertFullResultSet(rs, expRS, true);
+
+            rs = st.executeQuery(
+                "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+            rs.next();
+            rts = rs.getString(1);
+
+            // Now verify the correct runtimeStatistics output
+            rtsp = new RuntimeStatisticsParser(rts);
+            // print out the full stats if derby.tests.debug is true
+            println("full stats: \n" + rtsp.toString());
+            // the essentials are getting checked as per the comments
+            // above. If more detailed checking is needed, uncomment lines.
+            rtsp.assertSequence( 
+                    new String[] {
+                        "Hash Join ResultSet:",
+                        //"Left result set:",
+                        //"_Sort ResultSet:",
+                        "_Rows input = 53055",
+                        //"_Source result set:",
+                        //"__Project-Restrict ResultSet (7):",
+                        //"__Source result set:",
+                        //"___Hash Join ResultSet:",
+                        //"___Left result set:",
+                        //"____Project-Restrict ResultSet (5):",
+                        //"____Source result set:",
+                        //"_____Table Scan ResultSet for T1 at read committed " +
+                        //    "isolation level using share row locking chosen " +
+                        //    "by the optimizer",
+                        //"______Bit set of columns fetched={0}",
+                        //"______Scan type=heap",
+                        //"___Right result set:",
+                        // with fewer roles, the optimizer chose a Table Scan 
+                        //"____Hash Scan ResultSet for T2 at read committed " +
+                        //    "isolation level using instantaneous share row " +
+                        //    "locking: ",
+                        //"_____Bit set of columns fetched=All",
+                        //"_____Scan type=heap",
+                        //"______Operator: =",                        
+                        //"Right result set:",
+                        //"_Hash Table ResultSet (13):",
+                        //"_Source result set:",
+                        //"__Sort ResultSet:",
+                        //"__Source result set:",
+                        //"___Project-Restrict ResultSet (12):",
+                        //"___Source result set:",
+                        //"____Hash Join ResultSet:",
+                        //"____Left result set:",
+                        // with 4 rows, the optimizer used a Table Scan on T3 
+                        // for left node and a Hash Scan on T4 for the right.
+                        //"_____Table Scan ResultSet for T4 at read committed " +
+                        //    "isolation level using share row locking chosen " +
+                        //    "by the optimizer",
+                        //"______Bit set of columns fetched=All",
+                        //"______Scan type=heap",
+                        "____Right result set:",
+                        "_____Hash Scan ResultSet for T3 at read committed " +
+                            "isolation level using instantaneous share row " +
+                            "locking: "
+                            //,
+                        //"______Bit set of columns fetched={0}",
+                        //"______Scan type=heap"
+                            });
+
+        // clean up.
+        st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+
+        st.executeUpdate("drop view vv1");
+        st.executeUpdate("drop view vv2");
+        st.executeUpdate("drop table t1");
+        st.executeUpdate("drop table t2");
+        st.executeUpdate("drop table t3");
+        st.executeUpdate("drop table t4");
+    }
+
+    /**
+     * DERBY-1574: Subquery in COALESCE gives NPE due to 
+     * preprocess not implemented for that node type
+     */
+    public void testSubqueryInCOALESCE() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        st.executeUpdate("create table t1 (id int)");
+        st.executeUpdate("create table t2 (i integer primary key, j int)");
+        st.executeUpdate("insert into t1 values 1,2,3,4,5");
+        st.executeUpdate("insert into t2 values (1,1),(2,4),(3,9),(4,16)");
+
+        assertUpdateCount(st, 5,
+            "update t1 set id = coalesce((select j from t2 " +
+                "where t2.i=t1.id), 0)");
+
+        rs = st.executeQuery("select * from t1");
+        expColNames = new String [] {"ID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"4"}, {"9"}, {"16"}, {"0"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("drop table t1");
+        st.executeUpdate("drop table t2");
+    }
+
+    /**
+     * Test the fix for DERBY-2218
+     * @throws Exception
+     */
+    public void testDERBY_2218() throws Exception {
+        Statement st = createStatement();
+        ResultSet rs = null;
+        String[] expColNames;
+        
+        st.executeUpdate("create table t1 (i int)");
+
+        rs = st.executeQuery(
+            "select * from t1 " +
+                "where i in (1, 2, (values cast(null as integer)))");
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        // expect error, this used to throw NPE
+        assertStatementError("42X07", st,
+            "select * from t1 where i in (1, 2, (values null))");
+
+        assertStatementError("42X07", st,
+            "select * from t1 where i in " +
+                "(select i from t1 where i in (1, 2, (values null)))");
+
+        // expect error
+        assertStatementError("42X07", st,
+            "select * from t1 where exists (values null)");
+
+        assertStatementError("42X07", st,
+            "select * from t1 where exists " +
+                "(select * from t1 where exists(values null))");
+
+        assertStatementError("42X07", st,
+            "select i from t1 where exists " +
+                "(select i from t1 where exists(values null))");
+
+        assertStatementError("42X07", st,
+            "select * from (values null) as t2");
+
+        assertStatementError("42X07", st,
+            "select * from t1 where exists " +
+                "(select 1 from (values null) as t2)");
+
+        assertStatementError("42X07", st,
+            "select * from t1 where exists " +
+                "(select * from (values null) as t2)");
+
+        st.executeUpdate("drop table t1");
+        st.close();
+    }
+    
+    /**
+     * DERBY-4549: NPE in JBitSet
+     */
+    public void testDERBY_4549() throws Exception {
+        Statement st = createStatement();
+        PreparedStatement pSt;
+        ResultSet rs = null;
+        String[][] expRS;
+        String[] expColNames;
+
+        st.executeUpdate("create table ABC (ID int)");
+        st.executeUpdate("create table DEF (ID int)");
+
+        //compilation of the statement used to fail with NPE
+        pSt = prepareStatement(
+            "select * from ABC t1 " +
+                "where (select distinct t2.ID from DEF t2) in " +
+                    "(select t3.ID from DEF t3)");
+        
+        // empty tables, should give empty result
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"ID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        // now, test with data in the tables
+        st.executeUpdate("insert into ABC values 1, 2");
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"ID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+
+        st.executeUpdate("insert into DEF values 2");
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"ID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("insert into DEF values 2");
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"ID"};
+        JDBC.assertColumnNames(rs, expColNames);
+        expRS = new String [][] { {"1"}, {"2"} };
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate("insert into DEF values 3");
+        // will fail because left operand of IN is no longer scalar
+        // expect ERROR 21000: 
+        //     Scalar subquery is only allowed to return a single row
+        assertStatementError("21000", pSt);
+
+        st.executeUpdate("drop table ABC");
+        st.executeUpdate("drop table DEF");
+    }
+}



Mime
View raw message