/*
 *
 * 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 org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
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;

/**
 * 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 BaseTestSuite(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);
        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");
    }
}