/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ViewsTest

   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.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

public final class ViewsTest extends BaseJDBCTestCase {

    /**
     * Public constructor required for running test as standalone JUnit.
     */
    public ViewsTest(String name)
    {
        super(name);
    }

    public static Test suite()
    {
        BaseTestSuite suite = new BaseTestSuite("views Test");
        suite.addTest(TestConfiguration.embeddedSuite(ViewsTest.class));
        return new CleanDatabaseTestSetup(suite);
    }

    //DERBY-6185(Query against view  with 
    // "where name LIKE 'Col1' ESCAPE '\' " failed)
    //Problem was that we clone binary comparison operator node during the
    // optimize phase but we did not copy the entire state of the original
    // node
    public void test_DERBY6185() throws Exception
    {
        ResultSet rs = null;
        
        Statement st = createStatement();
        PreparedStatement ps;

        // create 2 tables and a view
        st.executeUpdate(
            "create table mytbl1 (name clob(10K))");
        st.executeUpdate("insert into mytbl1 "+
            "values ('Col1'),('Col2')");
        st.executeUpdate(
            "create table mytbl2 (name clob(10K))");
        st.executeUpdate("insert into mytbl2 "+
                "values ('Col1'),('Col2')");
        st.executeUpdate(
            "create view myview (name) as select t1.name from " +
            "mytbl1 t1 union all select t2.name from mytbl2 t2");

        //test base table's CLOB and LIKE clause with Statement
        rs = st.executeQuery("select name from mytbl1 " +
                "where name LIKE 'Col1'");
        JDBC.assertFullResultSet(rs, new String[][]{
                {"Col1"}
        });

        //test base table's CLOB and LIKE clause with PreparedStatement
        ps = prepareStatement("select name from mytbl1 " +
                "where name LIKE ?");
        ps.setString(1, "Col1");
        rs = ps.executeQuery();
        JDBC.assertFullResultSet(rs, new String[][]{
                {"Col1"}
        });

        //test view's CLOB and LIKE clause with Statement
        rs = st.executeQuery("select name from myview " +
            "where name LIKE 'Col1'");
        JDBC.assertFullResultSet(rs, new String[][]{
                {"Col1"},
                {"Col1"}
        });

        //test view's CLOB and LIKE clause with PreparedStatement
        ps = prepareStatement("select name from myview " +
                "where name LIKE ?");
        ps.setString(1, "Col1");
        rs = ps.executeQuery();
        JDBC.assertFullResultSet(rs, new String[][]{
                {"Col1"},
                {"Col1"}
        });

        //test UNION's CLOB and LIKE clause with Statement
        rs = st.executeQuery("select name from " +
             "(select name from mytbl1 t1 union all " +
             "select t2.name from mytbl2 t2) " +
             "as s where name like 'Col1'");
        JDBC.assertFullResultSet(rs, new String[][]{
                {"Col1"},
                {"Col1"}
        });

        //test UNION's CLOB and LIKE clause with PreparedStatement
        ps = prepareStatement("select name from " +
             "(select name from mytbl1 t1 union all " +
             "select t2.name from mytbl2 t2) " +
             "as s where name like ?");
        ps.setString(1, "Col1");
        rs = ps.executeQuery();
        JDBC.assertFullResultSet(rs, new String[][]{
                {"Col1"},
                {"Col1"}
        });
    }

    public void test_views() throws Exception
    {
        ResultSet rs = null;
   
        Statement st = createStatement();

        String [][] expRS;
        String [] expColNames;


        //   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. tests for views set 
        // autocommit off
        
        getConnection().setAutoCommit(false);
        
        // create some tables
        
        st.executeUpdate(
            "create table t1(i int, s smallint, f float, dp "
            + "double precision)");
        
        st.executeUpdate(
            " create table t2(i int, s smallint, f float, dp "
            + "double precision)");
        
        st.executeUpdate(
            " create table insert_test (f float)");
        
        // create some views
        
        st.executeUpdate(
            "create view sv1 (s_was_i, dp_was_s, i_was_f, "
            + "f_was_dp) as select * from t1");
        
        st.executeUpdate(
            " create view sv2 as select * from t1");
        
        st.executeUpdate(
            " create view sv3 as select dp, f from t1 where i = s");
        
        st.executeUpdate(
            " create view sv4(i) as values 1, 2, 3");
        
        st.executeUpdate(
            " create view sv5 (c1) as select * from sv4");
        
        st.executeUpdate(
            " create view cv1 (t1_i, t2_s, t1_f, t2_dp) as "
            + "select t1.i, t2.s, t1.f, t2.dp from t1, t2 where "
            + "t1.i between t2.s and t2.i");
        
        st.executeUpdate(
            " create view cv2 as select * from sv1, sv3 where dp "
            + "= f_was_dp");
        
        st.executeUpdate(
            " create view cv3(i,s,f,dp) as select i, s, f, dp "
            + "from sv2 union select dp_was_s, s_was_i, f_was_dp, "
            + "i_was_f from sv1");
        
        st.executeUpdate(
            " create view cv4 (distinct_i) as select distinct i from t1");
        
        st.executeUpdate(
            " create view cv5(i,s) as select * from (select i, s "
            + "from cv3 where i = s) xyz");
        
        st.executeUpdate(
            " create view cv6 (c1, c2) as select a.c1 as x, b.c1 "
            + "as y from sv5 a, sv5 b where a.c1 <> b.c1");
        
        st.executeUpdate(
            " create view cv7 as select t.i, v.c1 from t1 t, cv6 "
            + "v where t.i = v.c1");
        
        st.executeUpdate(
            " create view cv8(col1, col2) as select 'Column 1',  "
            + "'Value = ' || cast(c1 as char(5)) from cv7 where 1 "
            + "in (select i from sv5)");
        
        // populate the tables
        
        st.executeUpdate(
            "insert into t1 values (1, 1, 1.0, 1.0)");
        
        st.executeUpdate(
            " insert into t1 values (1, 2, 3.0, 4.0)");
        
        st.executeUpdate(
            " insert into t1 values (8, 7, 6.0, 5.0)");
        
        st.executeUpdate(
            " insert into t2 values (1, 1, 1.0, 1.0)");
        
        st.executeUpdate(
            " insert into t2 values (1, 2, 3.0, 4.0)");
        
        st.executeUpdate(
            " insert into t2 values (8, 7, 6.0, 5.0)");
        
        // negative tests view with a parameter
        
        assertStatementError("42X98", st,
            "create view vneg as select * from t1 where i = ?");
        
        // drop view on table
        
        assertStatementError("X0Y16", st,
            "drop view t1");
        
        // drop table on view
        
        assertStatementError("42Y62", st,
            "drop table sv1");
        
        // views and tables share same name space
        
        assertStatementError("X0Y32", st,
            "create view sv1(i) as values 1");
        
        assertStatementError("X0Y32", st,
            " create table sv1 (c1 int)");
        
        assertStatementError("X0Y32", st,
            " create view t1(i) as values 1");
        
        // drop non-existant view
        
        assertStatementError("X0X05", st,
            "drop view notexists");
        
        // duplicate column name in view's column list
        
        assertStatementError("42Y13", st,
            "create view shouldntwork (c1, c2, c1) as select i, "
            + "s, f from t1");
        
        // # of columns in view's column list does not match that 
        // in view definition
        
        assertStatementError("42X56", st,
            "create view shouldntwork (c1, c2, c3) as select i, s from t1");
        
        assertStatementError("42X56", st,
            " create view shouldntwork (c1, c2, c3) as select i, "
            + "s, f, dp from t1");
        
        // try to drop a table out from under a view
        
        assertStatementError(new String[] {"X0Y23","X0Y23","X0Y23","X0Y23","X0Y23","X0Y23",
            "X0Y23","X0Y23","X0Y23","X0Y23"},st,
            "drop table t1");
        
        assertStatementError("X0Y23", st,
            " drop table t2");
        
        // try to drop a view out from under another view
        
        assertStatementError(new String[] {"X0Y23","X0Y23","X0Y23"}, st,
            "drop view sv1");
        
        assertStatementError("X0Y23", st,
            " drop view sv3");
        
        // try to drop a view out from under a cursor
        
        PreparedStatement ps_c1 = prepareStatement(
            "select * from cv8");
        
        ResultSet c1 = ps_c1.executeQuery();
        
        assertStatementError("X0X95", st,
            " drop view cv8");

        assertStatementErrorUnordered(
            new String[] {"X0Y23","X0Y23","X0Y23","X0X95"}, st,
            " drop view sv5");
        
        assertStatementErrorUnordered(
            new String[] {"X0Y23","X0Y23","X0Y23","X0Y23","X0X95"}, st,
            " drop view sv4");
        
        c1.close();
        ps_c1.close();
        
        // view updateability (No views are currently updateable)
        
        assertStatementError("42Y24", st,
            "insert into sv1 values 1");
        
        assertStatementError("42Y24", st,
            " delete from sv1");
        
        assertStatementError("42Y24", st,
            " update sv1 set s_was_i = 0");
        
        try {
        prepareStatement(
                "select * from sv1 for update of s_was_i");
            fail("statement ps_c2 should not have succeeded");
        } catch (SQLException se) {
            assertSQLState("42Y90",se);
        }
        
        // create index on a view
        
        assertStatementError("42Y62", st,
            "create index i1 on sv2(i)");
        
        // positive tests
        
        rs = st.executeQuery(
            "select * from sv1");
        
        expColNames = new String [] {"S_WAS_I", "DP_WAS_S", "I_WAS_F", "F_WAS_DP"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1", "1.0", "1.0"},
            {"1", "2", "3.0", "4.0"},
            {"8", "7", "6.0", "5.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from sv2");
        
        expColNames = new String [] {"I", "S", "F", "DP"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1", "1.0", "1.0"},
            {"1", "2", "3.0", "4.0"},
            {"8", "7", "6.0", "5.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from sv3");
        
        expColNames = new String [] {"DP", "F"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1.0", "1.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from sv4");
        
        expColNames = new String [] {"I"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1"},
            {"2"},
            {"3"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from sv5");
        
        expColNames = new String [] {"C1"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1"},
            {"2"},
            {"3"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv1");
        
        expColNames = new String [] {"T1_I", "T2_S", "T1_F", "T2_DP"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1", "1.0", "1.0"},
            {"1", "1", "3.0", "1.0"},
            {"8", "7", "6.0", "5.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv2");
        
        expColNames = new String [] {"S_WAS_I", "DP_WAS_S", "I_WAS_F", "F_WAS_DP", "DP", "F"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1", "1.0", "1.0", "1.0", "1.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv3");
        
        expColNames = new String [] {"I", "S", "F", "DP"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1", "1.0", "1.0"},
            {"1", "2", "3.0", "4.0"},
            {"2", "1", "4.0", "3.0"},
            {"7", "8", "5.0", "6.0"},
            {"8", "7", "6.0", "5.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv4 order by 1");
        
        expColNames = new String [] {"DISTINCT_I"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1"},
            {"8"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv5");
        
        expColNames = new String [] {"I", "S"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv6");
        
        expColNames = new String [] {"C1", "C2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "2"},
            {"1", "3"},
            {"2", "1"},
            {"2", "3"},
            {"3", "1"},
            {"3", "2"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv7");
        
        expColNames = new String [] {"I", "C1"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1"},
            {"1", "1"},
            {"1", "1"},
            {"1", "1"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from cv8");
        
        expColNames = new String [] {"COL1", "COL2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"Column 1", "Value = 1"},
            {"Column 1", "Value = 1"},
            {"Column 1", "Value = 1"},
            {"Column 1", "Value = 1"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from (select * from cv3) x order by 1,2");
        
        expColNames = new String [] {"I", "S", "F", "DP"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1", "1.0", "1.0"},
            {"1", "2", "3.0", "4.0"},
            {"2", "1", "4.0", "3.0"},
            {"7", "8", "5.0", "6.0"},
            {"8", "7", "6.0", "5.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from (select * from cv4) x order by 1");
        
        expColNames = new String [] {"DISTINCT_I"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1"},
            {"8"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from (select * from cv5) x");
        
        expColNames = new String [] {"I", "S"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "1"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // verify that we can create and drop indexes on 
        // underlying tables
        
        st.executeUpdate(
            "create index i on t1(i)");
        
        st.executeUpdate(
            " drop index i");
        
        // verify the consistency of the indexes on the system 
        // catalogs
        
        rs = st.executeQuery(
            "select tablename, "
            + "SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from "
            + "sys.systables where CAST(tabletype AS CHAR(1)) = "
            + "'S' and CAST(tablename AS VARCHAR(128)) != 'SYSDUMMY1' order by tablename");
        
        expColNames = new String [] {"TABLENAME", "2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"SYSALIASES", "1"},
            {"SYSCHECKS", "1"},
            {"SYSCOLPERMS", "1"},
            {"SYSCOLUMNS", "1"},
            {"SYSCONGLOMERATES", "1"},
            {"SYSCONSTRAINTS", "1"},
            {"SYSDEPENDS", "1"},
            {"SYSFILES", "1"},
            {"SYSFOREIGNKEYS", "1"},
            {"SYSKEYS", "1"},
            {"SYSPERMS", "1"},   
            {"SYSROLES", "1"},
            {"SYSROUTINEPERMS", "1"},
            {"SYSSCHEMAS", "1"},
            {"SYSSEQUENCES", "1"},
            {"SYSSTATEMENTS", "1"},
            {"SYSSTATISTICS", "1"},
            {"SYSTABLEPERMS", "1"},
            {"SYSTABLES", "1"},
            {"SYSTRIGGERS", "1"},
            {"SYSUSERS", "1"},
            {"SYSVIEWS", "1"},
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // test inserts from a view
        
        st.executeUpdate(
            "insert into insert_test select * from sv5");
        
        rs = st.executeQuery(
            " select * from insert_test");
        
        expColNames = new String [] {"F"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1.0"},
            {"2.0"},
            {"3.0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // drop the views
        
        st.executeUpdate(
            "drop view cv8");
        
        st.executeUpdate(
            " drop view cv7");
        
        st.executeUpdate(
            " drop view cv6");
        
        st.executeUpdate(
            " drop view cv5");
        
        st.executeUpdate(
            " drop view cv4");
        
        st.executeUpdate(
            " drop view cv3");
        
        st.executeUpdate(
            " drop view cv2");
        
        st.executeUpdate(
            " drop view cv1");
        
        st.executeUpdate(
            " drop view sv5");
        
        st.executeUpdate(
            " drop view sv4");
        
        st.executeUpdate(
            " drop view sv3");
        
        st.executeUpdate(
            " drop view sv2");
        
        st.executeUpdate(
            " drop view sv1");
        
        // drop the tables
        
        st.executeUpdate(
            "drop table t1");
        
        st.executeUpdate(
            " drop table t2");
        
        st.executeUpdate(
            " drop table insert_test");
        
        // verify the consistency of the indexes on the system 
        // catalogs
        
        rs = st.executeQuery(
            "select tablename, "
            + "SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from "
            + "sys.systables where CAST(tabletype as CHAR(1)) = "
            + "'S' and CAST(tablename  as VARCHAR(128)) != 'SYSDUMMY1' order by tablename");
        
        expColNames = new String [] {"TABLENAME", "2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"SYSALIASES", "1"},
            {"SYSCHECKS", "1"},
            {"SYSCOLPERMS", "1"},
            {"SYSCOLUMNS", "1"},
            {"SYSCONGLOMERATES", "1"},
            {"SYSCONSTRAINTS", "1"},
            {"SYSDEPENDS", "1"},
            {"SYSFILES", "1"},
            {"SYSFOREIGNKEYS", "1"},
            {"SYSKEYS", "1"},
            {"SYSPERMS", "1"},   
            {"SYSROLES", "1"},
            {"SYSROUTINEPERMS", "1"},
            {"SYSSCHEMAS", "1"},
            {"SYSSEQUENCES", "1"},
            {"SYSSTATEMENTS", "1"},
            {"SYSSTATISTICS", "1"},
            {"SYSTABLEPERMS", "1"},
            {"SYSTABLES", "1"},
            {"SYSTRIGGERS", "1"},
            {"SYSUSERS", "1"},
            {"SYSVIEWS", "1"},
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // bug 2745
        
        st.executeUpdate(
            "CREATE TABLE orgtable ( name VARCHAR(255), "
            + "supervisorname VARCHAR(255), jobtitle VARCHAR(255) )");
        
        st.executeUpdate(
            " CREATE VIEW orgview AS SELECT name, "
            + "supervisorname, jobtitle FROM orgtable");
        
        rs = st.executeQuery(
            " SELECT name,jobtitle FROM orgview WHERE name IN "
            + "(SELECT supervisorname FROM orgview WHERE name LIKE "
            + "'WYATT%')");
        
        expColNames = new String [] {"NAME", "JOBTITLE"};
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        st.executeUpdate(
            " drop view orgview");
        
        st.executeUpdate(
            " drop table orgtable");
        
        // reset autocommit
        
        getConnection().setAutoCommit(true);
        
        // DERBY-1304 view not getting dropped The second drop 
        // view statement fails before the patch
        
        st.executeUpdate(
            "CREATE SCHEMA TEST_SCHEMA");
        
        st.executeUpdate(
            " CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR)");
        
        st.executeUpdate(
            " CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN "
            + "AS VIEW_COLUMN FROM TEST_SCHEMA.T1");
        
        st.executeUpdate(
            " DROP VIEW TEST_SCHEMA.V1");
        
        st.executeUpdate(
            " DROP TABLE TEST_SCHEMA.T1");
        
        st.executeUpdate(
            " DROP SCHEMA TEST_SCHEMA RESTRICT");
        
        // reiterate
        
        st.executeUpdate(
            "CREATE SCHEMA TEST_SCHEMA");
        
        st.executeUpdate(
            " CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR)");
        
        st.executeUpdate(
            " CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN "
            + "AS VIEW_COLUMN FROM TEST_SCHEMA.T1");
        
        st.executeUpdate(
            " DROP VIEW TEST_SCHEMA.V1");
        
        st.executeUpdate(
            " DROP TABLE TEST_SCHEMA.T1");
        
        st.executeUpdate(
            " DROP SCHEMA TEST_SCHEMA RESTRICT");
        
        // DERBY-2018 expect error
        
        assertStatementError("42X07", st,
            "CREATE VIEW v1(c1) AS VALUES NULL");
        
        // DERBY-681
        
        st.executeUpdate(
            "create table o (name varchar(20), ord int)");
        
        st.executeUpdate(
            " create table a (ord int, amount int)");
        
        st.executeUpdate(
            " create view v1 (vx, vy) as select name, sum(ord) "
            + "from o where ord > 0 group by name, ord");
        
        st.executeUpdate(
            " create view v2 (vx, vy) as select name, sum(ord) "
            + "from o where ord > 0 group by name, ord having ord "
            + "<= ANY (select ord from a)");
        
        st.executeUpdate(
            " drop view v2");
        
        st.executeUpdate(
            " drop view v1");
        
        st.executeUpdate(
            " drop table a");
        
        st.executeUpdate(
            " drop table o");
        
        
        
        getConnection().rollback();
        st.close();
    }

   /**
    * DERBY-3270 Test that we can select from a view in another schema if the
    * default schema does not exist.
    *
    * @throws SQLException
    */
    public void testSelectViewFromOtherSchemaWithNoDefaultSchema()
            throws SQLException {
        Connection conn = openDefaultConnection("joe","joepass");
        Statement st = conn.createStatement();
        st.execute("create table mytable(a int)");
        st.execute("insert into mytable values (99)");
        st.execute("create view myview as select * from mytable");
        st.close();
        conn.close();
        Connection conn2 = openDefaultConnection("bill","billpass");
        Statement st2 = conn2.createStatement();
        ResultSet rs = st2.executeQuery("SELECT * FROM JOE.MYVIEW");
        JDBC.assertFullResultSet(rs,new String[][] {{"99"}});
        st2.executeUpdate("drop view joe.myview");
        st2.executeUpdate("drop table joe.mytable");
        st2.close();
        conn2.close();
   }

    /**
     * Make sure DatabaseMetaData.getColumns is correct when we have a view
     * created when there is an expression in the select list.
     * Also check the ResultSetMetaData
     * @throws SQLException
     */
    public void testViewMetaDataWithGeneratedColumnsDerby4230() throws SQLException {
        Statement s = createStatement();
        s.executeUpdate("create table A (id integer, data varchar(20), data2 integer)");
        s.executeUpdate("insert into A values (3, 'G', 5), (23, 'G', 4), (5, 'F', 1), (2, 'H', 4), (1, 'F', 5)");
        //DERBY-4230. Make sure DatabaseMetaData.getColumns does not include generated columns.
        // You need an expression in the select list.
        s.executeUpdate("create view V (data, num) as select data, data2 + 2 from A group by data, data2");
        DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet columns = dmd.getColumns(null, null, "V", null);
        String[][] expectedDBMetaRows = new String[][] {{"","APP","V","DATA","12","VARCHAR","20",null,null,null,"1","",null,null,null
            ,"40","1","YES",null,null,null,null,"NO","NO",null},
            {"","APP","V","NUM","4","INTEGER","10",null,"0","10","1","",null,null,null,null,"2","YES",null,null,null,null,"NO","NO",null}};  
        JDBC.assertFullResultSet(columns,expectedDBMetaRows);
        // Make sure ResultSetMetaData is right when selecting from view.
        // This wasn't a problem with DERBY-4230, but checking for good measure.
        ResultSet rs = s.executeQuery("SELECT * FROM V");        
        JDBC.assertColumnNames(rs, new String[] {"DATA","NUM"});
        JDBC.assertColumnTypes(rs, new int[] {java.sql.Types.VARCHAR, java.sql.Types.INTEGER});
        JDBC.assertNullability(rs,new boolean[] {true,true});
        // Finally check the results.
        String [][] expectedRows = new String[][] {{"F","3"},
            {"F","7"},
            {"G","6"},
            {"G","7"},
            {"H","6"}};
        JDBC.assertFullResultSet(rs, expectedRows);
        s.executeUpdate("DROP VIEW V");
        s.executeUpdate("DROP TABLE A");        
    }


    /**
     * DERBY-3478
     * Make sure column names are correct when we select from a view and also
     * give a table correation name with derived column list.
     *
     * E.g. SELECT * FROM V1 X(A,B)
     *
     * @throws SQLException
     */
    public void testViewMetaDataWithCorrelationNameAndDerivedColumnList_3478()
            throws SQLException
    {
        Statement s = createStatement();
        s.executeUpdate("create table t1 (i int, j int)");
        s.executeUpdate("insert into t1 values (1, 1), (1, -1), " +
                        "                      (2, 2), (3, -3), (4, 4)");

        s.executeUpdate("create view v1 as select j, i from t1");
        s.executeUpdate("create view v2 (x,y,z) as select j, i, i+j from t1");

        DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet columns = dmd.getColumns(null, null, "V1", null);

        String[][] expectedDBMetaRows = new String[][]
            {{"","APP","V1","J","4","INTEGER","10",null,"0","10","1","",
              null,null,null,null,"1","YES",null,null,null,null,"NO","NO",null},
             {"","APP","V1","I","4","INTEGER","10",null,"0","10","1","",
              null,null,null,null,"2","YES",null,null,null,null,"NO","NO",null}};

        JDBC.assertFullResultSet(columns,expectedDBMetaRows);

        expectedDBMetaRows = new String[][]
            {{"","APP","V2","X","4","INTEGER","10",null,"0","10","1","",
              null,null,null,null,"1","YES",null,null,null,null,"NO","NO",null},
             {"","APP","V2","Y","4","INTEGER","10",null,"0","10","1","",
              null,null,null,null,"2","YES",null,null,null,null,"NO","NO",null},
             {"","APP","V2","Z","4","INTEGER","10",null,"0","10","1","",
              null,null,null,null,"3","YES",null,null,null,null,"NO","NO",null}};

        columns = dmd.getColumns(null, null, "V2", null);
        JDBC.assertFullResultSet(columns,expectedDBMetaRows);

        // Make sure ResultSetMetaData is right when selecting from view. This
        // exposes DERBY-3478 if not fixed.

        ResultSet rs = s.executeQuery("select * from v1 x(a,b)");
        JDBC.assertColumnNames(rs, new String[] {"A","B"});
        JDBC.assertColumnTypes(rs, new int[] {java.sql.Types.INTEGER,
                                              java.sql.Types.INTEGER});
        JDBC.assertNullability(rs,new boolean[] {true,true});

        // Check the results.
        String [][] expectedRows = new String[][]
            {{"1","1"},
             {"-1","1"},
             {"2","2"},
             {"-3","3"},
             {"4","4"}};
        JDBC.assertFullResultSet(rs, expectedRows);

        rs = s.executeQuery("select * from v2 as x(a,b,d)");
        JDBC.assertColumnNames(rs, new String[] {"A","B","D"});
        JDBC.assertColumnTypes(rs, new int[] {java.sql.Types.INTEGER,
                                              java.sql.Types.INTEGER,
                                              java.sql.Types.INTEGER});
        JDBC.assertNullability(rs,new boolean[] {true,true, true});

        // Check the results.
        expectedRows = new String[][]
            {{"1","1","2"},
             {"-1","1","0"},
             {"2","2","4"},
             {"-3","3","0"},
             {"4","4","8"}};
        JDBC.assertFullResultSet(rs, expectedRows);

        s.executeUpdate("drop view v1");
        s.executeUpdate("drop view v2");
        s.executeUpdate("drop table t1");
    }
}