/**
 *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.CollationTest
 *  
 * 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.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Collator;
import java.util.Locale;
import junit.framework.Test;
import junit.framework.TestCase;
import org.apache.derby.iapi.types.HarmonySerialClob;
import org.apache.derbyTesting.functionTests.tests.jdbcapi.BatchUpdateTest;
import org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest;
import org.apache.derbyTesting.functionTests.util.TestUtil;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.Decorator;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.XML;

public class CollationTest extends BaseJDBCTestCase {

    public CollationTest(String name) {
        super(name);
    }
    
    private static final String[] NAMES =
    {
            // Just Smith, Zebra, Acorn with alternate A,S and Z
            "Smith",
            "Zebra",
            "\u0104corn",
            "\u017Bebra",
            "Acorn",
            "\u015Amith",
            "aacorn",
    };

    /** Test cases to run with English case-sensitive collation. */
    private final static String[] ENGLISH_CASE_SENSITIVE = {
        "testEnglishCollation",
        "testUsingClauseAndNaturalJoin",
        "testNullColumnInInsert",
        "test_5951",
    };

    /** Test cases to run with English case-insensitive collation. */
    private final static String[] ENGLISH_CASE_INSENSITIVE = {
        "testUsingClauseAndNaturalJoin",
        "testNullColumnInInsert",
        "testDerby6227",
        "testDerby6890",
    };

    /** Test cases to run with Norwegian case-sensitive collation. */
    private final static String[] NORWEGIAN_CASE_SENSITIVE = {
        "testNorwayCollation",
        "testInListNorwayCollation",
        "testLikeEscapeClauseLengthRestriction",
    };

    /** Test cases to run with Polish case-sensitive collation. */
    private final static String[] POLISH_CASE_SENSITIVE = {
        "testPolishCollation",
    };

    /** Test cases to run with French case-sensitive collation. */
    private final static String[] FRENCH_CASE_SENSITIVE = {
        "testFrenchCollation",
    };

    /** Test cases to run with Swedish case-insensitive collation. */
    private final static String[] SWEDISH_CASE_INSENSITIVE = {
        "testSwedishCaseInsensitiveCollation",
    };

    /** SQL state that signals invalid escape sequence in LIKE expressions. */
    private final static String INVALID_ESCAPE = "22019";

  /**
   * Test order by with default collation
   * 
   * @throws SQLException
   */
public void testDefaultCollation() throws SQLException {

      setAutoCommit(false);
      Statement s = createStatement();
      PreparedStatement ps;
      ResultSet rs;
      
      setUpTable(s);

      //The collation should be UCS_BASIC for this database
      checkLangBasedQuery(s, 
      		"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')",
			new String[][] {{"UCS_BASIC"}});

      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
      		new String[][] {{"4","Acorn"},{"0","Smith"},{"1","Zebra"},
      		{"6","aacorn"}, {"2","\u0104corn"},{"5","\u015Amith"},{"3","\u017Bebra"} });   

      // Order by expresssion
      s.executeUpdate("CREATE FUNCTION mimic(val VARCHAR(32000)) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.mimic' LANGUAGE JAVA PARAMETER STYLE JAVA");
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY MIMIC(NAME)",
                new String[][] {{"4","Acorn"},{"0","Smith"},{"1","Zebra"},
                {"6","aacorn"}, {"2","\u0104corn"},{"5","\u015Amith"},{"3","\u017Bebra"} });   

      s.executeUpdate("DROP FUNCTION mimic");
      //COMPARISONS INVOLVING CONSTANTS
      //In default JVM territory, 'aacorn' is != 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
      		null);
      //In default JVM territory, 'aacorn' is not < 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < 'Acorn' ",
      		null);

      //COMPARISONS INVOLVING CONSTANT and PERSISTENT COLUMN
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME <= 'Smith' ",
      		new String[][] {{"0","Smith"}, {"4","Acorn"} });   
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME between 'Acorn' and 'Zebra' ",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"4","Acorn"} });
      //After index creation, the query above will return same data but in 
      //different order
      /*s.executeUpdate("CREATE INDEX CUSTOMER_INDEX1 ON CUSTOMER(NAME)");
      s.executeUpdate("INSERT INTO CUSTOMER VALUES (NULL, NULL)");
      checkLangBasedQuery(s, 
      		"SELECT ID, NAME FROM CUSTOMER WHERE NAME between 'Acorn' and " +
			" 'Zebra' ORDER BY NAME",
      		new String[][] {{"4","Acorn"}, {"0","Smith"}, {"1","Zebra"} });
*/
      //For non-collated databases, COMPARISONS OF USER PERSISTENT CHARACTER 
      //COLUMN AND CHARACTER CONSTANT WILL not FAIL IN SYSTEM SCHEMA.
      s.executeUpdate("set schema SYS");
      checkLangBasedQuery(s, "SELECT ID, NAME FROM APP.CUSTOMER WHERE NAME <= 'Smith' ",
      		new String[][] {{"0","Smith"}, {"4","Acorn"} });   

      s.executeUpdate("set schema APP");
      //Following sql will not fail in a database which uses UCS_BASIC for
      //user schemas. Since the collation of user schemas match that of system
      //schema, the following comparison will not fail. It will fail in a 
      //database with territory based collation for user schemas. 
      checkLangBasedQuery(s, "SELECT 1 FROM SYS.SYSTABLES WHERE " +
      		" TABLENAME = 'CUSTOMER' ",
      		new String[][] {{"1"} });    
      //Using cast for persistent character column from system table in the
      //query above won't affect the above sql in any ways. 
      checkLangBasedQuery(s, "SELECT 1 FROM SYS.SYSTABLES WHERE CAST " +
      		" (TABLENAME AS CHAR(15)) = 'CUSTOMER' ",
      		new String[][] {{"1"} });   

      //Do some testing using CASE WHEN THEN ELSE
      //following will work with no problem for a database with UCS_BASIC
      //collation for system and user schemas
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CASE " +
      		" WHEN 1=1 THEN TABLENAME ELSE 'c' END = 'SYSCOLUMNS'",
      		new String[][] {{"SYSCOLUMNS"} });   
      //Using cast for result of CASE expression in the query above would not
      //affect the sql in any ways. 
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CAST " +
      		" ((CASE WHEN 1=1 THEN TABLENAME ELSE 'c' END) AS CHAR(12)) = " +
			" 'SYSCOLUMNS'",
      		new String[][] {{"SYSCOLUMNS"} });   

      //Do some testing using CONCATENATION
      //following will work with no problem for a database with UCS_BASIC
      //collation for system and user schemas
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
      		" TABLENAME || ' ' = 'SYSCOLUMNS '",
      		new String[][] {{"SYSCOLUMNS"} });   
      //Using cast for result of CAST expression in the query above would not
      //affect the sql in any ways. 
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
      		" CAST (TABLENAME || ' ' AS CHAR(12)) = " +
			" 'SYSCOLUMNS '",
      		new String[][] {{"SYSCOLUMNS"} });   

      //Do some testing using COALESCE
      //following will work with no problem for a database with UCS_BASIC
      //collation for system and user schemas
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
      		" COALESCE(TABLENAME, 'c') = 'SYSCOLUMNS'",
      		new String[][] {{"SYSCOLUMNS"} });   
      //Using cast for result of COALESCE expression in the query above would not
      //affect the sql in any ways. 
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
      		" CAST (COALESCE (TABLENAME, 'c') AS CHAR(12)) = " +
			" 'SYSCOLUMNS'",
      		new String[][] {{"SYSCOLUMNS"} });   

      //Do some testing using NULLIF
      //following will work with no problem for a database with UCS_BASIC
      //collation for system and user schemas
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" NULLIF(TABLENAME, 'c') = 'SYSCOLUMNS'",
  		new String[][] {{"SYSCOLUMNS"} });   
      //Using cast for result of NULLIF expression in the query above would not
      //affect the sql in any ways. 
      checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
      		" CAST (NULLIF (TABLENAME, 'c') AS CHAR(12)) = " +
			" 'SYSCOLUMNS'",
      		new String[][] {{"SYSCOLUMNS"} });   

      //Test USER/CURRENT_USER/SESSION_USER
      checkLangBasedQuery(s, "SELECT count(*) FROM CUSTOMER WHERE "+ 
      		"CURRENT_USER = 'APP'",
      		new String[][] {{"7"}});   
      
      //Do some testing with MAX/MIN operators
      checkLangBasedQuery(s, "SELECT MAX(NAME) maxName FROM CUSTOMER ORDER BY maxName ",
      		new String[][] {{"\u017Bebra"}});   
      checkLangBasedQuery(s, "SELECT MIN(NAME) minName FROM CUSTOMER ORDER BY minName ",
      		new String[][] {{"Acorn"}});   

      //Do some testing with CHAR/VARCHAR functions
      s.executeUpdate("set schema SYS");
      checkLangBasedQuery(s, "SELECT CHAR(ID) FROM APP.CUSTOMER WHERE " +
      		" CHAR(ID)='0'", new String[] [] {{"0"}});
      
      s.executeUpdate("set schema APP");
      if (XML.classpathMeetsXMLReqs())
      	checkLangBasedQuery(s, "SELECT XMLSERIALIZE(x as CHAR(10)) " +
      			" FROM xmlTable, SYS.SYSTABLES WHERE " +
				" XMLSERIALIZE(x as CHAR(10)) = TABLENAME",
				null);
      //Start of parameter testing
      //Start with simple ? param in a string comparison
      //Since all schemas (ie user and system) have the same collation, the 
      //following test won't fail.
      s.executeUpdate("set schema APP");
      ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
      		" ? = TABLENAME");
      ps.setString(1, "SYSCOLUMNS");
      rs = ps.executeQuery();
      JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

      //Since all schemas (ie user and system) have the same collation, the 
      //following test won't fail.
      ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" SUBSTR(?,2) = TABLENAME");
      ps.setString(1, " SYSCOLUMNS");
      rs = ps.executeQuery();
      JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

      //Since all schemas (ie user and system) have the same collation, the 
      //following test won't fail.
      ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" LTRIM(?) = TABLENAME");
      ps.setString(1, " SYSCOLUMNS");
      rs = ps.executeQuery();
      JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});
      ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" RTRIM(?) = TABLENAME");
      ps.setString(1, "SYSCOLUMNS  ");
      rs = ps.executeQuery();
      JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

      //Since all schemas (ie user and system) have the same collation, the 
      //following test won't fail.
      ps = prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE " + 
      		" ? IN (SELECT TABLENAME FROM SYS.SYSTABLES)");
      ps.setString(1, "SYSCOLUMNS");
      rs = ps.executeQuery();
      JDBC.assertFullResultSet(rs,new String[][] {{"7"}});
      //End of parameter testing
      
      s.close();
      compareAgrave(1, 1, 2);
      }
      

public void testFrenchCollation() throws SQLException {
    compareAgrave(2, 1, 1);
}



/**
 * For a TERRITORY_BASED collation french database, differences between
 * pre-composed accents such as "\u00C0" (A-grave) and combining accents
 * such as "A\u0300" (A, combining-grave) should match for {@code =}, but
 * not for {@code LIKE}. They should never match for UCS_BASIC. We insert
 * both into a table and search based on {@code =} and {@code LIKE}.
 *  
 * @param expectedMatchCountForEqual  number of rows we expect back for =. 
 * 	2 for French, 1 for English 
 * @param expectedMatchCountForLike  number of rows we expect back for LIKE. 
 * 	1 for French and English 
 * @param expectedDistinctRows number of rows expected from SELECT DISTINCT
 * @throws SQLException
 */
private void compareAgrave(int expectedMatchCountForEqual,
        int expectedMatchCountForLike, int expectedDistinctRows)
            throws SQLException {
    String[] dataTypes = {"VARCHAR(5)", "CHAR(5)"};
    for (int i = 0; i < dataTypes.length; i++) {
        compareAgrave(dataTypes[i], expectedMatchCountForEqual,
                expectedMatchCountForLike, expectedDistinctRows);
    }
}

/**
 * Helper for {@link #compareAgrave(int, int, int)} which performs the test
 * for one data type.
 */
private void compareAgrave(String dataType, int expectedMatchCountForEqual,
		int expectedMatchCountForLike, int expectedDistinctRows)
            throws SQLException {

      // Create the two strings that are supposed to be equal in French locale.
      String agrave = "\u00C0";
      String agraveCombined ="A\u0300";

      Statement s = createStatement();
      
      try {
          s.executeUpdate("DROP TABLE T");
      }catch (SQLException se) {}
      s.executeUpdate("CREATE TABLE T (vc " + dataType + ")");
      PreparedStatement ps = prepareStatement("INSERT INTO T VALUES (?)");
      ps.setString(1,agrave);
      ps.executeUpdate();
      ps.setString(1,agraveCombined);
      ps.executeUpdate();
        
      ps = prepareStatement("SELECT COUNT(*) FROM T WHERE VC = ?");
      ps.setString(1, agrave);
      ResultSet rs = ps.executeQuery();
      JDBC.assertSingleValueResultSet(rs, Integer.toString(expectedMatchCountForEqual));
      // Use '%' at the end of the pattern so that we also match the trailing
      // blanks if the data type is CHAR instead of VARCHAR.
      ps = prepareStatement("SELECT COUNT(*) FROM T WHERE VC LIKE ? || '%'");
      ps.setString(1, agrave);
      rs = ps.executeQuery();
      JDBC.assertSingleValueResultSet(rs, Integer.toString(expectedMatchCountForLike));

      // DERBY-3975: They should match for distinct, the same way as for =
      int distinctRows = JDBC.assertDrainResults(
              s.executeQuery("SELECT DISTINCT VC FROM T"));
      assertEquals("Unexpected number of distinct rows",
              expectedDistinctRows, distinctRows);
  }


/**
   * Test order by with polish collation
   * @throws SQLException
   */
public void testPolishCollation() throws SQLException {

      setAutoCommit(false);
      Statement s = createStatement();
      
      setUpTable(s);

      //The collation should be TERRITORY_BASED for this database
      checkLangBasedQuery(s, 
      		"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')",
			new String[][] {{"TERRITORY_BASED"}});

      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
      		new String[][] {{"6","aacorn"}, {"4","Acorn"}, {"2","\u0104corn"},
      		{"0","Smith"},{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"} });
      
      // Order by expresssion
      s.executeUpdate("CREATE FUNCTION mimic(val VARCHAR(32000)) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.mimic' LANGUAGE JAVA PARAMETER STYLE JAVA");
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY MIMIC(NAME)",
              new String[][] {{"6","aacorn"}, {"4","Acorn"}, {"2","\u0104corn"},
                {"0","Smith"},{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"} });
                
      s.executeUpdate("DROP FUNCTION mimic");
      
      //COMPARISONS INVOLVING CONSTANTS
      //In Polish, 'aacorn' is != 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
      		null);
      //In Polish, 'aacorn' is < 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < 'Acorn'",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"2","\u0104corn"},
      		{"3","\u017Bebra"}, {"4","Acorn"}, {"5","\u015Amith"}, 
			{"6","aacorn"} });

      //COMPARISONS INVOLVING CONSTANT and PERSISTENT COLUMN
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME <= 'Smith' ",
      		new String[][] {{"0","Smith"}, {"2","\u0104corn"}, {"4","Acorn"}, 
      		{"6","aacorn"} });
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME between 'Acorn' and 'Zebra' ",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"2","\u0104corn"}, 
      		{"4","Acorn"}, {"5","\u015Amith"} });
      //After index creation, the query above will return same data but in 
      //different order
      /*s.executeUpdate("CREATE INDEX CUSTOMER_INDEX1 ON CUSTOMER(NAME)");
      s.executeUpdate("INSERT INTO CUSTOMER VALUES (NULL, NULL)");
      checkLangBasedQuery(s, 
      		"SELECT ID, NAME FROM CUSTOMER -- derby-properties index=customer_index1 \r WHERE NAME between 'Acorn' and " +
			" 'Zebra'", //ORDER BY NAME",
      		new String[][] {{"4","Acorn"}, {"2","\u0104corn"}, {"0","Smith"}, 
		      		{"5","\u015Amith"}, {"1","Zebra"} });
      */
      //For collated databases, COMPARISONS OF USER PERSISTENT CHARACTER 
      //COLUMN AND CHARACTER CONSTANT WILL FAIL IN SYSTEM SCHEMA.
      s.executeUpdate("set schema SYS");
      assertStatementError("42818", s, "SELECT ID, NAME FROM APP.CUSTOMER WHERE NAME <= 'Smith' ");

      //Do some testing with MAX/MIN operators
      s.executeUpdate("set schema APP");
      checkLangBasedQuery(s, "SELECT MAX(NAME) maxName FROM CUSTOMER ORDER BY maxName ",
      		new String[][] {{"\u017Bebra"}});   
      checkLangBasedQuery(s, "SELECT MIN(NAME) minName FROM CUSTOMER ORDER BY minName ",
      		new String[][] {{"aacorn"}});   

      commonTestingForTerritoryBasedDB(s);
    
      }    
  
/**
 * Test in list with constant and non constant elements and Norwegian collation
 * DERBY-6025(Wrong results with IN lists and indexes in territory based 
 *   collation)
 */
public void testInListNorwayCollation() throws SQLException {
    Statement s = createStatement();
    s.execute("CREATE TABLE derby6025_T1( c1 varchar(40) )");
    s.executeUpdate("INSERT INTO derby6025_T1 VALUES" +
    		"'Stranda Idrottslag', 'Aalesunds Fotballklubb'");
    ResultSet rs = s.executeQuery("select * from derby6025_T1 where C1 in "+
    		"('Aalesunds Fotballklubb', cast('xyz' as char(3)))");
    JDBC.assertFullResultSet(rs,
      		new String[][] {{"Aalesunds Fotballklubb"}});
    
    s.executeUpdate("create index i1 on derby6025_T1(c1)");
    //After an index is created on column c1, following query returned 
    // 0 rows without the fix for DERBY-6025. After DERBY-6025 is fixed, 
    // it correctly returns 1 row.
    rs = s.executeQuery("select * from derby6025_T1 where C1 in "+
    		"('Aalesunds Fotballklubb', cast('xyz' as char(3)))");
    JDBC.assertFullResultSet(rs,
      		new String[][] {{"Aalesunds Fotballklubb"}});
    s.execute("DROP TABLE derby6025_T1");	
    }

  /**
   * Test order by with Norwegian collation
   * 
   * @throws SQLException
   */
public void testNorwayCollation() throws SQLException {

      setAutoCommit(false);
      Statement s = createStatement();

      setUpTable(s);

      //The collation should be TERRITORY_BASED for this database
      checkLangBasedQuery(s, 
      		"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')",
			new String[][] {{"TERRITORY_BASED"}});

      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
      		new String[][] {{"4","Acorn"}, {"2","\u0104corn"},{"0","Smith"},
      		{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"}, {"6","aacorn"} });
      
      // Order by expresssion
      s.executeUpdate("CREATE FUNCTION mimic(val VARCHAR(32000)) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.mimic' LANGUAGE JAVA PARAMETER STYLE JAVA");
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY MIMIC(NAME)",
                new String[][] {{"4","Acorn"}, {"2","\u0104corn"},{"0","Smith"},
                {"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"}, {"6","aacorn"} });
              
      s.executeUpdate("DROP FUNCTION mimic");
  
      //COMPARISONS INVOLVING CONSTANTS
      //In Norway, 'aacorn' is != 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
      		null);
      //In Norway, 'aacorn' is not < 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < 'Acorn' ",
      		null);

      //COMPARISONS INVOLVING CONSTANT and PERSISTENT COLUMN
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME <= 'Smith' ",
      		new String[][] {{"0","Smith"}, {"2","\u0104corn"}, {"4","Acorn"} });
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME between 'Acorn' and 'Zebra' ",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"2","\u0104corn"}, 
      		{"4","Acorn"}, {"5","\u015Amith"} });
      //After index creation, the query above will return same data but in 
      //different order
      /*s.executeUpdate("CREATE INDEX CUSTOMER_INDEX1 ON CUSTOMER(NAME)");
      s.executeUpdate("INSERT INTO CUSTOMER VALUES (NULL, NULL)");
      checkLangBasedQuery(s, 
      		"SELECT ID, NAME FROM CUSTOMER  -- derby-properties index=customer_index1 \r WHERE NAME between 'Acorn' and " +
			" 'Zebra'", //ORDER BY NAME",
      		new String[][] {{"4","Acorn"}, {"2","\u0104corn"}, {"0","Smith"}, 
		      		{"5","\u015Amith"}, {"1","Zebra"} });
      */
      //For collated databases, COMPARISONS OF USER PERSISTENT CHARACTER 
      //COLUMN AND CHARACTER CONSTANT WILL FAIL IN SYSTEM SCHEMA.
      s.executeUpdate("set schema SYS");
      assertStatementError("42818", s, "SELECT ID, NAME FROM APP.CUSTOMER WHERE NAME <= 'Smith' ");

      //Do some testing with MAX/MIN operators
      s.executeUpdate("set schema APP");
      checkLangBasedQuery(s, "SELECT MAX(NAME) maxName FROM CUSTOMER ORDER BY maxName ",
      		new String[][] {{"aacorn"}});   
      checkLangBasedQuery(s, "SELECT MIN(NAME) minName FROM CUSTOMER ORDER BY minName ",
      		new String[][] {{"Acorn"}});   

      commonTestingForTerritoryBasedDB(s);

      s.close();

      }

/**
 * DERBY-5531 Assert failure when inserting NULL into indexed column with 
 *  territory-based collation
 * Make sure that when the row being inserted has all nulls, we do not run
 *  into assert failure 
 * @throws SQLException
 */
public void testNullColumnInInsert() throws SQLException {
    Statement s = createStatement();
    String collation; 

    ResultSet rs = null;
    s.executeUpdate("CREATE TABLE derby5531_t1(x varchar(10) unique, y varchar(10))");
    s.executeUpdate("INSERT INTO derby5531_t1 VALUES(NULL, 'a')");
    s.executeUpdate("INSERT INTO derby5531_t1 VALUES('1', 'a')");
    s.executeUpdate("INSERT INTO derby5531_t1 VALUES('2', NULL)");
    s.executeUpdate("INSERT INTO derby5531_t1 VALUES(NULL, NULL)");
    s.executeUpdate("INSERT INTO derby5531_t1(x) VALUES(NULL)");
    s.executeUpdate("INSERT INTO derby5531_t1 VALUES('3', 'b')");
    rs = s.executeQuery("select * from derby5531_t1 order by x");
    JDBC.assertFullResultSet(rs,
      		new String[][] {{"1","a"}, {"2",null}, {"3","b"}, {null,null}, {null,null}, {null,"a"}});
    s.executeUpdate("DROP TABLE derby5531_t1");

    s.executeUpdate("CREATE TABLE derby5531_t2(x varchar(10) unique)");
    s.executeUpdate("INSERT INTO derby5531_t2 VALUES(NULL)");
    s.executeUpdate("INSERT INTO derby5531_t2(x) VALUES(NULL)");
    s.executeUpdate("INSERT INTO derby5531_t2 VALUES('a')");
    rs = s.executeQuery("select * from derby5531_t2 order by x");
    JDBC.assertFullResultSet(rs,
      		new String[][] {{"a"}, {null}, {null}});
    s.executeUpdate("DROP TABLE derby5531_t2");
}

  /**
   * Test USING clause and NATURAL JOIN for case insensitive and
   * 	case sensitive collations.
   * As per the SQL spec, "the join columns in a natural join or in a named 
   *    columns join should be added to the select list by coalescing the 
   *    column from the left table with the column from the right table. "
   * DERBY-4631 - Derby did not coalesce as suggested by SQL spec, instead
   * 	it picked up join column's value from the left table when working with 
   *    natural left outer join and it picked up the join column's value from 
   *    the right table when working with natural right outer join. This worked
   *    ok with non-territory based databases. For territory based databases,
   *    it worked ok for natural left outer join but depending on the data 
   *    value, it did not always work for natural right outer join in a 
   *    territory based database. DERBY-4631 fixes that by using following 
   *    logic to pick up the correct value in case of RIGHT OUTER JOIN with 
   *    USING/NATURAL clause
   *    1)if the left table's column value is null then pick up the
   *      right table's column's value.
   *    2)If the left table's column value is non-null, then pick up
   *      that value
   * @throws SQLException
  */
public void testUsingClauseAndNaturalJoin() throws SQLException {
      setAutoCommit(false);
      Statement s = createStatement();
      String collation; 

      ResultSet rs = null;
      rs = s.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')");
      rs.next();
      collation = rs.getString(1); 

      //Test arithmetic operation on join columns
      s.executeUpdate("CREATE TABLE derby4631_t1(x int)");
      s.executeUpdate("CREATE TABLE derby4631_t2(x int)");
      s.executeUpdate("INSERT INTO derby4631_t1 VALUES 1,2");
      s.executeUpdate("INSERT INTO derby4631_t2 VALUES 2,3");
      checkLangBasedQuery(s, "SELECT x+2, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x)+2 cx " +
    		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
      		new String[][] {{"4","4"},{"5","5"}});
      checkLangBasedQuery(s, "SELECT x+2, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x)+2 cx " +
    		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
      		"USING(x)",
      		new String[][] {{"4","4"},{"5","5"}});
      checkLangBasedQuery(s, "SELECT x*2, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x)*2 cx " +
    		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
      		new String[][] {{"2","2"},{"4","4"}});
      checkLangBasedQuery(s, "SELECT x*2, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x)*2 cx " +
    		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
      		"USING(x)",
      		new String[][] {{"2","2"},{"4","4"}});
   	  s.executeUpdate("DROP TABLE derby4631_t1");
      s.executeUpdate("DROP TABLE derby4631_t2");

      //Do the testing with one join column but with various combination
      // of left or right table being empty
      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5))");
      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5))");
      s.executeUpdate("INSERT INTO derby4631_t2 VALUES 'A','B'");
      checkLangBasedQuery(s, "SELECT x, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
    		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
      		new String[][] {{"A","A"},{"B","B"}});
      checkLangBasedQuery(s, "SELECT x, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
      		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
      		"USING(x)",
      		new String[][] {{"A","A"},{"B","B"}});
      checkLangBasedQuery(s, "SELECT x, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
    		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
      		null);
      checkLangBasedQuery(s, "SELECT x, " +
    		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
    		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
      		"USING(x)",
    		null);
   	  s.executeUpdate("DROP TABLE derby4631_t1");
      s.executeUpdate("DROP TABLE derby4631_t2");

      //Do the testing with one join column
      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5))");
      s.executeUpdate("INSERT INTO derby4631_t1 VALUES 'A','B'");
      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5))");
      s.executeUpdate("INSERT INTO derby4631_t2 VALUES 'b','c'");
      //Temp table for testing
      s.executeUpdate("CREATE TABLE derby4631_t3" +
    	      "(x1 varchar(5), x2 varchar(5), x3 int default 11)");
      
      //Derby always picks up the join column's value from the left table
      // when working with LEFT OUTER JOIN. This logic does not cause any
      // issue with territory or non-territory based databases. We get 
      // correct results even though Derby is not doing a coalesce on left 
      // table's column value and right table's column value as specified
      // by SQL spec. This is because, in case of LEFT OUTER JOIN, if the
      // left table's column value is null THEN right table's column value 
      // will also be null and hence it is ok for Derby to always pick up  
      // left table's column value for join columns in case of LEFT OUTER
      // JOIN.
      //
      //Test NATURAL LEFT OUTER JOIN
      checkLangBasedQuery(s, "SELECT x x1, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
    		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
      		new String[][] {{"b","b"},{"c","c"}});
      //Do the same test as above, but this time using the USING clause
      // rather the NATURAL join
      checkLangBasedQuery(s, "SELECT x x1, " +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
        		"USING(x)",
        		new String[][] {{"b","b"},{"c","c"}});
      //Test insert into a table with data from NATURAL LEFT OUTER JOIN
      s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
        		"SELECT x, " +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
          		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1");
      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
      		new String[][] {{"b","b","11"},{"c","c","11"}});
      s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
        		"(SELECT " +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1)");
      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
      		new String[][] {{"b","b","22"},{"c","c","22"}});
      s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
        		"(SELECT " +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1)");
        checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
          		null);
      //Do the same test as above, but this time using the USING clause
      // rather the NATURAL join
        s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
      		"SELECT x, " +
      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
      		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x)");
      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
      		new String[][] {{"b","b","11"},{"c","c","11"}});
      s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
        		"(SELECT " +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x))");
      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
      		new String[][] {{"b","b","22"},{"c","c","22"}});
      s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
        		"(SELECT " +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x))");
        checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
          		null);

        //Test create view with insert from join
        s.executeUpdate("create view derby4631_v2 as " +
        		"(SELECT x," +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1)");
        checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
        		new String[][] {{"b","b"},{"c","c"}});
        s.executeUpdate("drop view derby4631_v2 ");
        //Do the same test as above, but this time using the USING clause
        // rather the NATURAL join
        s.executeUpdate("create view derby4631_v2 as " +
        		"(SELECT x," +
        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x))");
        checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
        		new String[][] {{"b","b"},{"c","c"}});
        s.executeUpdate("drop view derby4631_v2 ");

        //Test nested NATURAL LEFT OUTER JOIN. They will return correct data
        // with both territory and non-territory based dbs.
        checkLangBasedQuery(s, "SELECT x " +
          		"FROM (values ('b')) v2(x) " +
        		"NATURAL LEFT OUTER JOIN " +
        		"derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1 ",
          		new String[][] {{"b"}});
      //Test nested LEFT OUTER JOIN with USING clause They will return correct
      // data with both territory and non-territory based dbs.
      checkLangBasedQuery(s, "SELECT x " +
        		"FROM (values ('b')) v2(x) " +
          		"LEFT OUTER JOIN " +
          		"derby4631_t2 USING(x) " +
          		"LEFT OUTER JOIN derby4631_t1 USING(x) ",
        		new String[][] {{"b"}});
      
      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
    	  //We are working with a database with case-insensitive collation.
    	  // Hence row 'b' in derby4631_t2 will find a match in derby4631_t1
    	  // with row 'B'.
    	  
    	  //Derby used to always pick up the join column's value from the right
    	  // table when working with RIGHT OUTER JOIN. This could cause issues 
    	  // with case-sensitive collation databases and it would give wrong
    	  // results for join columns for RIGHT OUTER JOIN with USING/NATURAL.
    	  //After DERBY-4631 got fixed, now a query like following returns the
    	  // correct results. As per the SQL spec, the join column's value 
    	  // should always be the value resulting from coalescing the left 
    	  // table's column value with the right table's column value.
          checkLangBasedQuery(s, "SELECT x x1, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
          		new String[][] {{"A","A"},{"b","b"}});
          //Do the same test as above, but this time using the USING clause
          // rather the NATURAL join
          checkLangBasedQuery(s, "SELECT x x1, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
            		"USING(x)",
            		new String[][] {{"A","A"},{"b","b"}});
          
          //Test insert into a table with data from NATURAL RIGHT OUTER JOIN
          s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
            		"SELECT x xx, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
              		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1");
          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
          		new String[][] {{"A","A","11"},{"b","b","11"}});
          s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
          		"(SELECT " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1)");
          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
        		new String[][] {{"A","A","22"},{"b","b","22"}});
          s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
            		"(SELECT " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
            		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1)");
          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
            		null);
          //Do the same test as above, but this time using the USING clause
          // rather the NATURAL join
          s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
          		"SELECT x, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x)");
          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
          		new String[][] {{"A","A","11"},{"b","b","11"}});
          s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
            		"(SELECT " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
            		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x))");
          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
          		new String[][] {{"A","A","22"},{"b","b","22"}});
          s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
            		"(SELECT " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
            		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x))");
            checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
              		null);

            //Test create view with insert from join
            s.executeUpdate("create view derby4631_v2 as " +
            		"(SELECT x," +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
            		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1)");
            checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
            		new String[][] {{"A","A"},{"b","b"}});
            s.executeUpdate("drop view derby4631_v2 ");
            //Do the same test as above, but this time using the USING clause
            // rather the NATURAL join
            s.executeUpdate("create view derby4631_v2 as " +
            		"(SELECT x," +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
            		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x))");
            checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
            		new String[][] {{"A","A"},{"b","b"}});
            s.executeUpdate("drop view derby4631_v2 ");

          //Test nested NATURAL RIGHT OUTER JOIN
          checkLangBasedQuery(s, "SELECT x " +
            		"FROM (values ('b')) v2(x) " +
              		"NATURAL RIGHT OUTER JOIN " +
              		"derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1 ",
              		new String[][] {{"A"},{"b"}});
          //Test nested LEFT OUTER JOIN with USING clause
          checkLangBasedQuery(s, "SELECT x " +
            		"FROM (values ('b')) v2(x) " +
              		"RIGHT OUTER JOIN " +
              		"derby4631_t2 USING(x) " +
              		"RIGHT OUTER JOIN derby4631_t1 USING(x) ",
            		new String[][] {{"A"},{"b"}});
      } else {
    	  //Case-sensitive collation will not run into any problems for the
    	  // given data set and hence following is returning correct results.
          checkLangBasedQuery(s, "SELECT x x1, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
        		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
          		new String[][] {{"A","A"},{"B","B"}});
          //Do the same test as above, but this time using the USING clause
          // rather the NATURAL join
    	  //
    	  //Case-sensitive collation will not run into any problems for the
    	  // given data set and hence following is returning correct results.
          checkLangBasedQuery(s, "SELECT x x1, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
            		"USING(x)",
            		new String[][] {{"A","A"},{"B","B"}});
      }
   	  s.executeUpdate("DROP TABLE derby4631_t1");
      s.executeUpdate("DROP TABLE derby4631_t2");
      
      //Now do the testing with 3 join columns
      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5), "+
      	      "y varchar(2), z int, a int)");
      s.executeUpdate("INSERT INTO derby4631_t1 VALUES " +
      	      "('A','z',1,11),('B','y',2,22)");
      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5), "+
      	      "y varchar(2), z int)");
      s.executeUpdate("INSERT INTO derby4631_t2 VALUES  " +
      	      "('b','Y',2),('c','Y',2)");
      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
    	  //For case-insensitive-territory based db, there will be a match for
    	  // one of the rows in derby4631_t2 with derby4631_t1 and that is why 
    	  // column a from derby4631_t1 will be non-null for that row for the 
    	  // LEFT OUTER JOIN query
          checkLangBasedQuery(s, "SELECT x, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
            		"a " +
            		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
            		new String[][] {{"b","b","Y","Y","2","2","22"},
          		  {"c","c","Y","Y","2","2",null}});
          //test with USING clause
          checkLangBasedQuery(s, "SELECT x, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
          		"USING(x,y,z)",
          		new String[][] {{"b","b","Y","Y","2","2","22"},
          		  {"c","c","Y","Y","2","2",null}});
          //Test joining on only 2 of the 3 columns
          checkLangBasedQuery(s, "SELECT " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
            		"a " +
            		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
            		"USING(y,z)",
            		new String[][] {{"b","Y","Y","2","2","22"},
          		  {"c","Y","Y","2","2","22"}});
          //Test RIGHT OUTER JOIN using only 2 of the 3 columns
          checkLangBasedQuery(s, "SELECT " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
          		"USING(y,z)",
          		new String[][] {{"A","z","z","1","1","11"},
        		  {"b","Y","Y","2","2","22"},
          		  {"c","Y","Y","2","2","22"}});
          //Test NATURAL RIGHT OUTER JOIN
          checkLangBasedQuery(s, "SELECT x, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
          		new String[][] {{"A","A","z","z","1","1","11"},
        		  {"b","b","Y","Y","2","2","22"}});
        //test with USING clause
          checkLangBasedQuery(s, "SELECT x, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
            		"a " +
              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
              		"USING(x,y,z)",
              		new String[][] {{"A","A","z","z","1","1","11"},
                  		  {"b","b","Y","Y","2","2","22"}});
      } else {
    	  //For non-territory based db, there will be no match for both the
    	  // rows in derby4631_t2 with derby4631_t1 and that is why column
    	  // a from derby4631_t1 will be null for the LEFT OUTER JOIN
    	  // query
          checkLangBasedQuery(s, "SELECT x, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
            		"a " +
            		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
            		new String[][] {{"b","b","Y","Y","2","2",null},
          		  {"c","c","Y","Y","2","2",null}});
          checkLangBasedQuery(s, "SELECT x, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
          		"USING(x,y,z)",
          		new String[][] {{"b","b","Y","Y","2","2",null},
          		  {"c","c","Y","Y","2","2",null}});
          //Test LEFT OUTER JOIN using only 2 of the 3 columns
          checkLangBasedQuery(s, "SELECT " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
          		"USING(y,z)",
          		new String[][] {{"b","Y","Y","2","2",null},
        		  {"c","Y","Y","2","2",null}});

          //Test NATURAL RIGHT OUTER JOIN
          checkLangBasedQuery(s, "SELECT x, " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
          		new String[][] {{"A","A","z","z","1","1","11"},
        		  {"B","B","y","y","2","2","22"}});
        //test with USING clause
          checkLangBasedQuery(s, "SELECT x, " +
            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
            		"a " +
              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
              		"USING(x,y,z)",
              		new String[][] {{"A","A","z","z","1","1","11"},
                  		  {"B","B","y","y","2","2","22"}});
          //Test RIGHT OUTER JOIN using only 2 of the 3 columns
          checkLangBasedQuery(s, "SELECT " +
          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
          		"a " +
          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
          		"USING(y,z)",
          		new String[][] {{"A","z","z","1","1","11"},
        		  {"B","y","y","2","2","22"}});
      }
      s.executeUpdate("DROP TABLE derby4631_t1");
      s.executeUpdate("DROP TABLE derby4631_t2");
      
      //Now do the testing with 2 join columns
      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5), y varchar(2))");
      s.executeUpdate("INSERT INTO derby4631_t1 VALUES ('A','z'),('B','y')");
      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5), y varchar(2))");
      s.executeUpdate("INSERT INTO derby4631_t2 VALUES ('b','Y'),('c','x')");
      //Test with views too
      s.executeUpdate("create view derby4631_v1 as select * from derby4631_t1");
      s.executeUpdate("create view derby4631_v2 as select * from derby4631_t2");
      //Test with global temporary tables too
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE gt1 " +
    	      "(x varchar(5), y varchar(2)) " +
    	      "on commit delete rows not logged");
      s.executeUpdate("INSERT INTO session.gt1 VALUES ('A','z'),('B','y')");
      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE gt2" +
    	      "(x varchar(5), y varchar(2)) " +
    	      "on commit delete rows not logged");
      s.executeUpdate("INSERT INTO session.gt2 VALUES ('b','Y'),('c','x')");

      //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
      // and hence following is returning the correct results for both
      // territory and non-territory based databases
	  joinTesting(s,"derby4631_t2", "derby4631_t1",
			  "derby4631_t2", "derby4631_t1",
			  " NATURAL LEFT OUTER JOIN ", "",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test with views
	  joinTesting(s,"derby4631_v2", "derby4631_v1",
			  "derby4631_v2", "derby4631_v1",
			  " NATURAL LEFT OUTER JOIN ", "",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test with global temporary tables
	  joinTesting(s,"gt2", "gt1",
			  "session.gt2 gt2", "session.gt1 gt1",
			  " NATURAL LEFT OUTER JOIN ", "",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test with VALUES
	  joinTesting(s,"v2", "v1",
			  " (values ('b','Y'),('c','x')) v2(x,y) ",
			  " (values('A','z'),('B','y')) v1(x,y) ", 
			  " NATURAL LEFT OUTER JOIN ", "",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test subqueries.
	  joinTesting(s,"t2", "t1",
			  " (select * from derby4631_t2) t2(x,y) ",
			  " (select * from derby4631_t1) t1(x,y) ", 
			  " NATURAL LEFT OUTER JOIN ", "",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
	  
      //Do the same test as above, but this time using the USING clause
      // rather the NATURAL join
      //
      //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
      // and hence following is returning the correct results.
	  joinTesting(s,"derby4631_t2", "derby4631_t1",
			  "derby4631_t2", "derby4631_t1",
			  "  LEFT OUTER JOIN ", " USING(x,y)",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test with views
	  joinTesting(s,"derby4631_v2", "derby4631_v1",
			  "derby4631_v2", "derby4631_v1",
			  "  LEFT OUTER JOIN ", " USING(x,y)",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test with global temporary tables
	  joinTesting(s,"gt2", "gt1",
			  "session.gt2 gt2", "session.gt1 gt1",
			  "  LEFT OUTER JOIN ", " USING(x,y)",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test with VALUES
	  joinTesting(s,"v2", "v1",
			  " (values ('b','Y'),('c','x')) v2(x,y) ",
			  " (values('A','z'),('B','y')) v1(x,y) ", 
			  " LEFT OUTER JOIN ", " USING(x,y)",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      //Test subqueries.
	  joinTesting(s,"t2", "t1",
			  " (select * from derby4631_t2) t2(x,y) ",
			  " (select * from derby4631_t1) t1(x,y) ", 
			  " LEFT OUTER JOIN ", " USING(x,y)",
			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
      
      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
    	  //Following query is returning correct data because DERBY-4631 is
    	  // fixed
    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
    			  "derby4631_t2", "derby4631_t1",
    			  " NATURAL RIGHT OUTER JOIN ", "",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test with views
    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
    			  "derby4631_v2", "derby4631_v1",
    			  " NATURAL RIGHT OUTER JOIN ", "",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test with global temporary tables
    	  joinTesting(s,"gt2", "gt1",
    			  "session.gt2 gt2", "session.gt1 gt1",
    			  " NATURAL RIGHT OUTER JOIN ", "",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test with VALUES
    	  joinTesting(s,"v2", "v1",
    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
    			  " (values('A','z'),('B','y')) v1(x,y) ", 
    			  " NATURAL RIGHT OUTER JOIN ", "",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test subqueries.
    	  joinTesting(s,"t2", "t1",
    			  " (select * from derby4631_t2) t2(x,y) ",
    			  " (select * from derby4631_t1) t1(x,y) ", 
    			  " NATURAL RIGHT OUTER JOIN ", "",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Do the same test as above, but this time using the USING clause
          // rather the NATURAL join
    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
    			  "derby4631_t2", "derby4631_t1",
    			  " RIGHT OUTER JOIN ", " USING(x,y)",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test with views
    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
    			  "derby4631_v2", "derby4631_v1",
    			  " RIGHT OUTER JOIN ", " USING(x,y)",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test with global temporary tables
    	  joinTesting(s,"gt2", "gt1",
    			  "session.gt2 gt2", "session.gt1 gt1",
    			  " RIGHT OUTER JOIN ", " USING(x,y)",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test with VALUES
    	  joinTesting(s,"v2", "v1",
    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
    			  " (values('A','z'),('B','y')) v1(x,y) ", 
    			  " RIGHT OUTER JOIN ", " USING(x,y) ",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
          //Test subqueries.
    	  joinTesting(s,"t2", "t1",
    			  " (select * from derby4631_t2) t2(x,y) ",
    			  " (select * from derby4631_t1) t1(x,y) ", 
    			  " RIGHT OUTER JOIN ", " USING(x,y)",
    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
      } else {
    	  //Case-sensitive collation will not run into any problems for the
    	  // given data set and hence following is returning correct results.
    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
    			  "derby4631_t2", "derby4631_t1",
    			  " NATURAL RIGHT OUTER JOIN ", "",
            		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
          //Test with views
    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
    			  "derby4631_v2", "derby4631_v1",
    			  " NATURAL RIGHT OUTER JOIN ", "",
            		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
          //Test with VALUES
    	  joinTesting(s,"v2", "v1",
    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
    			  " (values('A','z'),('B','y')) v1(x,y) ", 
    			  " NATURAL RIGHT OUTER JOIN ", "",
          		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
          //Do the same test as above, but this time using the USING clause
          // rather the NATURAL join
          //
    	  //Case-sensitive collation will not run into any problems for the
    	  // given data set and hence following is returning correct results.
    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
    			  "derby4631_t2", "derby4631_t1",
    			  " RIGHT OUTER JOIN ", " USING(x,y)",
    			  new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
          //Test with views
    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
    			  "derby4631_v2", "derby4631_v1",
    			  " RIGHT OUTER JOIN ", " USING(x,y)",
    			  new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
          //Test with VALUES
    	  joinTesting(s,"v2", "v1",
    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
    			  " (values('A','z'),('B','y')) v1(x,y) ", 
    			  " RIGHT OUTER JOIN ", " USING(x,y) ",
    			  new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
      }

      s.executeUpdate("DROP TABLE session.gt1");
      s.executeUpdate("DROP TABLE session.gt2");
      s.executeUpdate("DROP VIEW derby4631_v1");
      s.executeUpdate("DROP VIEW derby4631_v2");
      s.executeUpdate("DROP TABLE derby4631_t1");
      s.executeUpdate("DROP TABLE derby4631_t2");
      
}

private void joinTesting(Statement s, 
		String leftTableName, String rightTableName, 
		String leftTableSource, String rightTableSource,
		String joinSpecification, String usingClause, 
		String[][] expectedResults) 
				throws SQLException{
	String query = "SELECT x, y," +
      		"coalesce("+leftTableName+".x, "+
			rightTableName+".x) cx, " +
      		"coalesce("+leftTableName+".y, "+
      		rightTableName+".y) cy " +
    		"FROM "+leftTableSource+joinSpecification+
    		rightTableSource+usingClause;
	checkLangBasedQuery(s, query,
    		expectedResults);
    
}
  /**
   * Test order by with English collation
   *
  * @throws SQLException
  */
public void testEnglishCollation() throws SQLException {

      setAutoCommit(false);
      Statement s = createStatement();
      setUpTable(s);

      //The collation should be TERRITORY_BASED for this database
      checkLangBasedQuery(s,
      		"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')",
			new String[][] {{"TERRITORY_BASED"}});

      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
      		new String[][] {{"6","aacorn"},{"4","Acorn"},{"2","\u0104corn"},{"0","Smith"},
      		{"5","\u015Amith"},{"1","Zebra"},{"3","\u017Bebra"} });

      //COMPARISONS INVOLVING CONSTANTS
      //In English, 'aacorn' != 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
      		null);
      //In English, 'aacorn' is < 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < 'Acorn'",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"2","\u0104corn"},
      		{"3","\u017Bebra"}, {"4","Acorn"}, {"5","\u015Amith"},
			{"6","aacorn"} });

      //COMPARISONS INVOLVING CONSTANT and PERSISTENT COLUMN
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME <= 'Smith' ",
      		new String[][] {{"0","Smith"}, {"2","\u0104corn"}, {"4","Acorn"},
      		{"6","aacorn"} });
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME between 'Acorn' and 'Zebra' ",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"2","\u0104corn"},
      		{"4","Acorn"}, {"5","\u015Amith"} });
      //After index creation, the query above will return same data but in
      //different order
      /*s.executeUpdate("CREATE INDEX CUSTOMER_INDEX1 ON CUSTOMER(NAME)");
      s.executeUpdate("INSERT INTO CUSTOMER VALUES (NULL, NULL)");
      checkLangBasedQuery(s,
      		"SELECT ID, NAME FROM CUSTOMER -- derby-properties index=customer_index1 \r WHERE NAME between 'Acorn' and " +
			" 'Zebra'", //ORDER BY NAME",
      		new String[][] {{"4","Acorn"}, {"2","\u0104corn"}, {"0","Smith"},
      		{"5","\u015Amith"}, {"1","Zebra"} });
      */
      //For collated databases, COMPARISONS OF USER PERSISTENT CHARACTER
      //COLUMN AND CHARACTER CONSTANT WILL FAIL IN SYSTEM SCHEMA.
      s.executeUpdate("set schema SYS");
      assertStatementError("42818", s, "SELECT ID, NAME FROM APP.CUSTOMER WHERE NAME <= 'Smith' ");

      //Do some testing with MAX/MIN operators
      s.executeUpdate("set schema APP");
      checkLangBasedQuery(s, "SELECT MAX(NAME) maxName FROM CUSTOMER ORDER BY maxName ",
      		new String[][] {{"\u017Bebra"}});
      checkLangBasedQuery(s, "SELECT MIN(NAME) minName FROM CUSTOMER ORDER BY minName ",
      		new String[][] {{"aacorn"}});

      commonTestingForTerritoryBasedDB(s);

      s.close();
      }

  /**
   * Test with Swedish case insensitive collation
   *
  * @throws SQLException
  */
public void testSwedishCaseInsensitiveCollation() throws SQLException {

      setAutoCommit(false);
      Statement s = createStatement();
      setUpTable(s);

      //The collation should be TERRITORY_BASED for this database
      checkLangBasedQuery(s,
      		"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')",
			new String[][] {{"TERRITORY_BASED:SECONDARY"}});

      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
      		new String[][] {{"6","aacorn"},{"4","Acorn"},{"2","\u0104corn"},{"0","Smith"},
      		{"5","\u015Amith"},{"1","Zebra"},{"3","\u017Bebra"} });

      //COMPARISONS INVOLVING CONSTANTS
      //In Swedish, 'aacorn' != 'Acorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
      		null);

      //In case insensitive Swedish, 'aacorn' == 'AaCorn'
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where NAME = 'AaCorn' ORDER BY NAME ",
      		new String[][] {{"6","aacorn"}});

      //Selection with LIKE
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where NAME like 'a%'",
      		new String[][] {{"4","Acorn"}, {"6","aacorn"} });

      //COMPARISONS INVOLVING CONSTANT and PERSISTENT COLUMN
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME <= 'Smith' ",
      		new String[][] {{"0","Smith"}, {"2","\u0104corn"}, {"4","Acorn"},
      		{"6","aacorn"} });
      checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER WHERE NAME between 'acorn' and 'zebra' ",
      		new String[][] {{"0","Smith"}, {"1","Zebra"}, {"2","\u0104corn"},
      		{"4","Acorn"}, {"5","\u015Amith"} });

	  //For collated databases, COMPARISONS OF USER PERSISTENT CHARACTER
      //COLUMN AND CHARACTER CONSTANT WILL FAIL IN SYSTEM SCHEMA.
      s.executeUpdate("set schema SYS");
      assertStatementError("42818", s, "SELECT ID, NAME FROM APP.CUSTOMER WHERE NAME <= 'Smith' ");

      //Do some testing with MAX/MIN operators
      s.executeUpdate("set schema APP");
      checkLangBasedQuery(s, "SELECT MAX(NAME) maxName FROM CUSTOMER ORDER BY maxName ",
      		new String[][] {{"\u017Bebra"}});
      checkLangBasedQuery(s, "SELECT MIN(NAME) minName FROM CUSTOMER ORDER BY minName ",
      		new String[][] {{"aacorn"}});

      commonTestingForTerritoryBasedDB(s);

      s.close();
      }

private void commonTestingForTerritoryBasedDB(Statement s) throws SQLException{
	PreparedStatement ps;
	ResultSet rs;	
    
    Connection conn = s.getConnection();

    s.executeUpdate("set schema APP");
    //Following sql will fail because the compilation schema is user schema
    //and hence the character constant "CUSTOMER" will pickup the collation
    //of user schema, which is territory based for this database. But the
    //persistent character columns from sys schema, which is TABLENAME in
    //following query will have the UCS_BASIC collation. Since the 2 
    //collation types don't match, the following comparison will fail
    assertStatementError("42818", s, "SELECT 1 FROM SYS.SYSTABLES WHERE " +
    		" TABLENAME = 'CUSTOMER' ");   
    //To get around the problem in the query above, use cast for persistent 
    //character column from system table and then compare it against a 
    //character constant. Do this when the compilation schema is a user 
    //schema and not system schema. This will ensure that the result 
    //of the casting will pick up the collation of the user schema. And 
    //constant character string will also pick up the collation of user 
    //schema and hence the comparison between the 2 will not fail
    checkLangBasedQuery(s, "SELECT 1 FROM SYS.SYSTABLES WHERE CAST " +
    		" (TABLENAME AS CHAR(15)) = 'CUSTOMER' ",
    		new String[][] {{"1"} });   

    //Do some testing using CASE WHEN THEN ELSE
    //following sql will not work for a database with territory based
    //collation for user schemas. This is because the resultant string type 
    //from the CASE expression below will have collation derivation of NONE.
    //The reason for collation derivation of NONE is that the CASE's 2 
    //operands have different collation types and as per SQL standards, if an
    //aggregate method has operands with different collations, then the 
    //result will have collation derivation of NONE. The right side of =
    //operation has collation type of territory based and hence the following
    //sql fails. DERBY-2678 This query should not fail because even though 
    //left hand side of = has collation derivation of NONE, the right hand
    //side has collation derivation of IMPLICIT, and so we should just pick the
    //collation of the rhs as per SQL standard. Once DERBY-2678 is fixed, we
    //don't need to use the CAST on this query to make it work (we are doing
    //that in the next test).
    assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CASE " +
    		" WHEN 1=1 THEN TABLENAME ELSE 'c' END = 'SYSCOLUMNS'");
    //CASTing the result of the CASE expression will solve the problem in the
    //query above. Now both the operands around = operation will have 
    //collation type of territory based and hence the sql won't fail
    checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CAST " +
    		" ((CASE WHEN 1=1 THEN TABLENAME ELSE 'c' END) AS CHAR(12)) = " +
			" 'SYSCOLUMNS'",
    		new String[][] {{"SYSCOLUMNS"} });   
    //Another test for CASE WHEN THEN ELSE DERBY-2776
    //The data type for THEN is not same as the data type for ELSE.
    //THEN is of type CHAR and ELSE is of type VARCHAR. VARCHAR has higher
    //precedence hence the type associated with the return type of CASE will
    //be VARCHAR. Also, since the collation type of THEN and ELSE match,
    //which is TERRITORY BASED, the return type of CASE will have the collation
    //of TERRITORY BASED. This collation is same as the rhs of the = operation
    //and hence following sql will pass. 
    checkLangBasedQuery(s, "SELECT count(*) FROM CUSTOMER WHERE CASE WHEN " +
    		" 1=1 THEN NAMECHAR ELSE NAME END = NAMECHAR",
    		new String[][] {{"7"} });   
    //The query below will work for the same reason. 
    checkLangBasedQuery(s, "SELECT count(*) FROM SYS.SYSTABLES WHERE CASE " +
    		" WHEN 1=1 THEN TABLENAME ELSE TABLEID END = TABLENAME",
    		new String[][] {{"26"} });

    //Do some testing using CONCATENATION
    //following will fail because result string of concatenation has 
    //collation derivation of NONE. That is because it's 2 operands have
    //different collation types. TABLENAME has collation type of UCS_BASIC
    //but constant character string ' ' has collation type of territory based
    //So the left hand side of = operator has collation derivation of NONE
    //and right hand side has collation derivation of territory based and
    //that causes the = comparison to fail. DERBY-2678 This query should not 
    //fail because even though left hand side of = has collation derivation of 
    //NONE, the right hand side has collation derivation of IMPLICIT, and so we 
    //should just pick the collation of the rhs as per SQL standard. Once 
    //DERBY-2678 is fixed, we don't need to use the CAST on this query to make 
    //it work (we are doing that in the next test).
    assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" TABLENAME || ' ' = 'SYSCOLUMNS '");   
    //CASTing the result of the concat expression will solve the problem in 
    //the query above. Now both the operands around = operation will have 
    //collation type of territory based and hence the sql won't fail
    checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" CAST (TABLENAME || ' ' AS CHAR(12)) = " +
			" 'SYSCOLUMNS '",
    		new String[][] {{"SYSCOLUMNS"} });
    //Following will fail because both sides of the = operator have collation
    //derivation of NONE. DERBY-2725
    assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" TABLENAME || ' ' = TABLENAME || 'SYSCOLUMNS '");   

    //Do some testing using COALESCE
    //following will fail because result string of COALESCE has 
    //collation derivation of NONE. That is because it's 2 operands have
    //different collation types. TABLENAME has collation type of UCS_BASIC
    //but constant character string 'c' has collation type of territory based
    //So the left hand side of = operator has collation derivation of NONE
    //and right hand side has collation derivation of territory based and
    //that causes the = comparison to fail. DERBY-2678 This query should not 
    //fail because even though left hand side of = has collation derivation of 
    //NONE, the right hand side has collation derivation of IMPLICIT, and so we 
    //should just pick the collation of the rhs as per SQL standard. Once 
    //DERBY-2678 is fixed, we don't need to use the CAST on this query to make 
    //it work (we are doing that in the next test).
    assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" COALESCE(TABLENAME, 'c') = 'SYSCOLUMNS'");   
    //CASTing the result of the COALESCE expression will solve the problem in 
    //the query above. Now both the operands around = operation will have 
    //collation type of territory based and hence the sql won't fail
    checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" CAST (COALESCE (TABLENAME, 'c') AS CHAR(12)) = " +
			" 'SYSCOLUMNS'",
    		new String[][] {{"SYSCOLUMNS"} });   

    //Do some testing using NULLIF
    //following will fail because result string of NULLIF has 
    //collation derivation of NONE. That is because it's 2 operands have
    //different collation types. TABLENAME has collation type of UCS_BASIC
    //but constant character string 'c' has collation type of territory based
    //So the left hand side of = operator has collation derivation of NONE
    //and right hand side has collation derivation of territory based and
    //that causes the = comparison to fail. DERBY-2678 This query should not 
    //fail because even though left hand side of = has collation derivation of 
    //NONE, the right hand side has collation derivation of IMPLICIT, and so 
    //we should just pick the collation of the rhs as per SQL standard. Once 
    //DERBY-2678 is fixed, we don't need to use the CAST on this query to make 
    //it work (we are doing that in the next test).
    assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" NULLIF(TABLENAME, 'c') = 'SYSCOLUMNS'");   
    //CASTing the result of the NULLIF expression will solve the problem in 
    //the query above. Now both the operands around = operation will have 
    //collation type of territory based and hence the sql won't fail
    checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" NULLIF (CAST (TABLENAME AS CHAR(12)), 'c' ) = " +
			" 'SYSCOLUMNS'",
    		new String[][] {{"SYSCOLUMNS"} });   

    //Do some testing with CHAR/VARCHAR functions
    s.executeUpdate("set schema SYS");
    //Following will work because both operands are = have the collation type
    //of UCS_BASIC
    checkLangBasedQuery(s, "SELECT CHAR(ID) FROM APP.CUSTOMER WHERE " +
    		" CHAR(ID)='0'", new String[] [] {{"0"}});
    //Derby does not allow VARCHAR function on numeric columns and hence 
    //this VARCHAR test looks little different than the CHAR test above.
    checkLangBasedQuery(s, "SELECT ID FROM APP.CUSTOMER WHERE " +
    		" VARCHAR(NAME)='Smith'", new String[] [] {{"0"}});
    //Now try a negative test
    s.executeUpdate("set schema APP");
    //following will fail because CHAR(TABLENAME)= TABLENAME is causing compare
    //between 2 character string types with different collation types. The lhs
    //operand has collation of territory based but rhs operand has collation of
    //UCS_BASIC
    assertStatementError("42818", s, "SELECT CHAR(TABLENAME) FROM " +
    		" SYS.SYSTABLES WHERE CHAR(TABLENAME)= TABLENAME AND " + 
			" VARCHAR(TABLENAME) = 'SYSCOLUMNS'");
    //To resolve the problem above, we need to use CAST around TABLENAME
    checkLangBasedQuery(s, "SELECT CHAR(TABLENAME) FROM SYS.SYSTABLES WHERE " +
    		" CHAR(TABLENAME)= (CAST (TABLENAME AS CHAR(12))) AND " + 
			" VARCHAR(TABLENAME) = 'SYSCOLUMNS'",
    		new String[][] {{"SYSCOLUMNS"} });  

    //Test USER/CURRENT_USER/SESSION_USER/CURRENT SCHMEA/ CURRENT ISOLATION
    //following will fail because we are trying to compare UCS_BASIC 
    //(CURRENT_USER) with territory based ("APP" taking it's collation from
    //compilation schema which is user schema at this time). 
    assertStatementError("42818", s, "SELECT count(*) FROM CUSTOMER WHERE "+
    		"CURRENT_USER = 'APP'");  
    //The problem above can be fixed by CASTing CURRENT_USER so that the 
    //collation type will be picked up from compilation schema which is user
    //schema at this point.
    checkLangBasedQuery(s, "SELECT count(*) FROM CUSTOMER WHERE "+ 
    		"CAST(CURRENT_USER AS CHAR(12)) = 'APP'",
    		new String[][] {{"7"}});   
    //following comparison will not cause compilation error because both the
    //operands around = have collation type of UCS_BASIC
    checkLangBasedQuery(s, "SELECT count(*) FROM CUSTOMER WHERE "+ 
    		"SESSION_USER = USER", new String[][] {{"7"}});
    //following will fail because we are trying to compare UCS_BASIC 
    //(CURRENT ISOLATION) with territory based ("CS" taking it's collation from
    //compilation schema which is user schema at this time). 
    assertStatementError("42818", s, "SELECT count(*) FROM CUSTOMER WHERE "+
	"CURRENT ISOLATION = 'CS'");  
    //Following will not give compilation error because both sides in = have 
    //the same collation type 
    checkLangBasedQuery(s, "SELECT count(*) FROM CUSTOMER WHERE "+ 
    		"CAST(CURRENT ISOLATION AS CHAR(12)) = 'CS'",
    		new String[][] {{"7"}});   
    //Following will not cause compilation error because both the operands
    //around the = have collation type of UCS_BASIC. We are in the SYS
    //schema and hence character string constant 'APP' has picked the collation
    //type of SYS schema which is UCS_BASIC
    s.executeUpdate("set schema SYS");
    checkLangBasedQuery(s, "SELECT count(*) FROM APP.CUSTOMER WHERE "+ 
    		"CURRENT SCHEMA = 'SYS'", new String[][] {{"7"}});   
    
    s.executeUpdate("set schema APP");
    if (XML.classpathMeetsXMLReqs()) {
        assertStatementError("42818", s, "SELECT XMLSERIALIZE(x as CHAR(10)) " +
        		" FROM xmlTable, SYS.SYSTABLES WHERE " + 
    			" XMLSERIALIZE(x as CHAR(10)) = TABLENAME");
        checkLangBasedQuery(s, "SELECT XMLSERIALIZE(x as CHAR(10)) FROM " +
        		" xmlTable, SYS.SYSTABLES WHERE XMLSERIALIZE(x as CHAR(10)) = " + 
    			" CAST(TABLENAME AS CHAR(10))",
        		null);
        //Do some parameter testing for XMLSERIALIZE. ? is not supported inside
        //the XMLSERIALIZE function and hence following will result in errors.
        assertCompileError("42Z70", "SELECT XMLSERIALIZE(x as CHAR(10)) " +
        		" FROM xmlTable, SYS.SYSTABLES WHERE " +
				" XMLSERIALIZE(? as CHAR(10)) = TABLENAME");
        assertCompileError("42Z70", "SELECT XMLSERIALIZE(x as CHAR(10)) FROM " +
        		" xmlTable, SYS.SYSTABLES WHERE XMLSERIALIZE(? as CHAR(10)) = " + 
    			" CAST(TABLENAME AS CHAR(10))");
    }
    
    //Start of user defined function testing
    //At this point, just create a function which involves character strings
    //in it's definition. In subsequent checkin, there will be collation 
    //related testing using this function's return value
    s.executeUpdate("set schema APP");
    s.executeUpdate("CREATE FUNCTION CONCAT_NOCALL(VARCHAR(10), VARCHAR(10)) "+
    		" RETURNS VARCHAR(20) RETURNS NULL ON NULL INPUT EXTERNAL NAME " + 
			"'org.apache.derbyTesting.functionTests.tests.lang.RoutineTest.concat' "+
			" LANGUAGE JAVA PARAMETER STYLE JAVA");
    //DERBY-2831 Creating a function inside a non-existent schema should not
    //fail when it's return type is of character string type. Following is a
    //simple test case copied from DERBY-2831
    s.executeUpdate("CREATE FUNCTION AA.B() RETURNS VARCHAR(10) NO SQL " +
    		"PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'aaa.bbb.ccc' ");
    //following fails as expected because aaa.bbb.ccc doesn't exist 
    assertStatementError("XJ001", s, "SELECT AA.B() FROM CUSTOMER ");

    //Start of parameter testing
    //Start with simple ? param in a string comparison
    //Following will work fine because ? is supposed to take it's collation 
    //from the context which in this case is from TABLENAME and TABLENAME
    //has collation type of UCS_BASIC
    s.executeUpdate("set schema APP");
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" ? = TABLENAME");
    ps.setString(1, "SYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});      

    //Do parameter testing with SUBSTR
    //Won't work in territory based database because in 
    //SUBSTR(?, int) = TABLENAME
    //? will get the collation of the current schema which is a user
    //schema and hence the collation type of result of SUBSTR will also be 
    //territory based since the result of SUBSTR always picks up the 
    //collation of it's first operand. So the comparison between left hand
    //side with terriotry based and right hand side with UCS_BASIC will fail.
    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" SUBSTR(?,2) = TABLENAME");
    //To fix the problem above, we need to CAST TABLENAME so that the result 
    //of CAST will pick up the collation of the current schema and this will
    //cause both the operands of SUBSTR(?,2) = CAST(TABLENAME AS CHAR(10)) 
    //to have same collation
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" SUBSTR(?,2) = CAST(TABLENAME AS CHAR(10))");
    ps.setString(1, "aSYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});      

    //Do parameter testing with CONCATENATION operator
    //Following will fail because the result of concatenation will have 
    //collation type of UCS_BASIC whereas the right hand side of = operator
    //will have collation type current schema which is territory based.
    //The reason CONCAT will have collation type of UCS_BASIC is because ? will
    //take collation from context which here will be TABLENAME and hence the
    //result of concatenation will have collation type of it's 2 operands,
    //namely UCS_BASIC
    assertCompileError("42ZA2", "SELECT TABLENAME FROM SYS.SYSTABLES " +
    		" WHERE TABLENAME || ? LIKE 'SYSCOLUMNS '");   
    //The query above can be made to work if we are in SYS schema or if we use
    //CAST while we are trying to run the query is user schema
    //Let's try CAST first
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" CAST((TABLENAME || ?) AS CHAR(20)) LIKE 'SYSCOLUMNS'");   
    //try switching to SYS schema and then run the original query without CAST
    s.executeUpdate("set schema SYS");
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
    		" WHERE TABLENAME || ? LIKE 'SYSCOLUMNS'");   
    s.executeUpdate("set schema APP");
    //The following will fail because the left hand side of LIKE has collation
    //derivation of NONE where as the right hand side has collation derivation
    //of IMPLICIT
    assertStatementError("42ZA2", s, "SELECT TABLENAME FROM SYS.SYSTABLES " +
    		" WHERE TABLENAME || 'AA' LIKE 'SYSCOLUMNS '");   
    //To fix the problem, we can use CAST on the left hand side so it's 
    //collation will be picked up from the compilation schema which is same as
    //what happens for the right hand operand.
    checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" CAST ((TABLENAME || 'AA') AS CHAR(12)) LIKE 'SYSCOLUMNS '",
    		null );   

    //Do parameter testing for IS NULL
    //Following query will pass because it doesn't matter what the collation of
    //? is when doing a NULL check
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" ? IS NULL");   
    ps.setString(1, " ");
    rs = ps.executeQuery();
	JDBC.assertEmpty(rs);
	//Now do the testing for IS NOT NULL
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" ? IS NOT NULL");
    ps.setNull(1, java.sql.Types.VARCHAR);
    rs = ps.executeQuery();
    JDBC.assertEmpty(rs);

    //Do parameter testing for LENGTH
    //Following query will fail because LENGTH operator is not allowed to take
    //a parameter. I just wanted to have a test case out for the changes that
    //are going into engine code (ie LengthOperatorNode)
    assertCompileError("42X36", "SELECT COUNT(*) FROM CUSTOMER WHERE " +
    		" LENGTH(?) != 0");   

    //Do parameter testing for BETWEEN
    //Following should pass for ? will take the collation from the context and
    //hence, it will be UCS_BASIC
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
	" TABLENAME NOT BETWEEN ? AND TABLENAME");   
    ps.setString(1, " ");
    rs = ps.executeQuery();
	JDBC.assertEmpty(rs);
	//Following will fail because ? will take collation of territory based but
	//the left hand side has collation of UCS_BASIC
    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" TABLENAME NOT BETWEEN ? AND 'SYSCOLUMNS'");   
    
    //Do parameter testing with COALESCE
    //following will pass because the ? inside the COALESCE will take the 
    //collation type of the other operand which is TABLENAME. The result of
    //COALESCE will have collation type of UCS_BASIC and that is the same
    //collation that the ? on rhs of = will get.
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
	" COALESCE(TABLENAME, ?) = ?");   
    ps.setString(1, " ");
    ps.setString(2, "SYSCOLUMNS ");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

    //Do parameter testing with LTRIM
    //Won't work in territory based database because in 
    //LTRIM(?) = TABLENAME
    //? will get the collation of the current schema which is a user
    //schema and hence the collation type of result of LTRIM will also be 
    //territory based since the result of LTRIM always picks up the 
    //collation of it's operand. So the comparison between left hand
    //side with terriotry based and right hand side with UCS_BASIC will fail.
    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" LTRIM(?) = TABLENAME");
    //To fix the problem above, we need to CAST TABLENAME so that the result 
    //of CAST will pick up the collation of the current schema and this will
    //cause both the operands of LTRIM(?) = CAST(TABLENAME AS CHAR(10)) 
    //to have same collation
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" LTRIM(?) = CAST(TABLENAME AS CHAR(10))");
    ps.setString(1, " SYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

    //Similar testing for RTRIM
    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" RTRIM(?) = TABLENAME");
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
		" RTRIM(?) = CAST(TABLENAME AS CHAR(10))");
    ps.setString(1, "SYSCOLUMNS  ");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

    //Similar testing for TRIM
    //Following won't work because the character string constant 'a' is 
    //picking up the collation of the current schema which is territory based.
    //And the ? in TRIM will pick up it's collation from 'a' and hence the
    //comparison between territory based character string returned from TRIM
    //function will fail against UCS_BASIC based TABLENAME on the right
    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" TRIM('a' FROM ?) = TABLENAME");
    //The problem can be fixed by using CAST on TABLENAME so the resultant of
    //CAST string will compare fine with the output of TRIM. Note CAST always
    //picks up the collation of the compilation schema.
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" TRIM('a' FROM ?) = CAST(TABLENAME AS CHAR(10))");
    ps.setString(1, "aSYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});
    //Another test for TRIM
    //Following will not fail because the ? in TRIM will pick up collation
    //from it's first parameter which is a SUBSTR on TABLENAME and hence the 
    //result of TRIM will have UCS_BASIC collation which matches the collation
    //on the right.
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" TRIM(LEADING SUBSTR(TABLENAME, LENGTH(TABLENAME)) FROM ?) = TABLENAME");
    ps.setString(1, "SYSCOLUMNS");
    rs = ps.executeQuery();
    //No rows returned because the result of TRIM is going to be 'YSCOLUMNS'
    JDBC.assertEmpty(rs);
    
    //Do parameter testing for LOCATE
    //Following will fail because 'LOOKFORME' has collation of territory based
    //but TABLENAME has collation of UCS_BASIC and hence LOCATE will fail 
    //because the collation types of it's two operands do not match
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" LOCATE(?, TABLENAME) != 0");
    ps.setString(1, "ABC");
    rs = ps.executeQuery();
    JDBC.assertEmpty(rs);
    //Just switch the parameter position and try the sql again
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
    		" LOCATE(TABLENAME, ?) != 0");
    ps.setString(1, "ABC");
    rs = ps.executeQuery();
    JDBC.assertEmpty(rs);
    
    //Do parameter testing with IN and subquery
    //Following will work just fine because ? will take it's collation from the
    //context which in this case will be collation of TABLENAME which has 
    //collation type of UCS_BASIC. 
    ps = prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE ? IN " +
    		" (SELECT TABLENAME FROM SYS.SYSTABLES)");
    ps.setString(1, "SYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"7"}});

    //Testing for NOT IN. Following won't work becuase ? is taking the 
    //collation type from context which will be from the character string
    //literal 'SYSCOLUMNS'. That literal will have the collation type of the
    //current schema which is the user schema and hence it's collation type
    //will be territory based. But that collation does not match the left hand
    //side on IN clause and hence it results in compliation error.
    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES " +
    		" WHERE TABLENAME NOT IN (?, ' SYSCOLUMNS ') AND " +
			" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
    //We can make the query work in 2 ways
    //1)Be in the SYS schema and then ? will take the collation of UCS_BASIC
    //because that is what the character string literal ' SYSCOLUMNS ' has.
    s.executeUpdate("set schema SYS");
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
    		" WHERE TABLENAME NOT IN (?, ' SYSCOLUMNS ') AND " +
			" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
    ps.setString(1, "aSYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});
    //2)The other way to fix the query would be to do a CAST on TABLENAME so
    //it will have the collation of current schema which is APP 
    s.executeUpdate("set schema APP");
    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " + 
	" CAST(TABLENAME AS CHAR(10)) NOT IN (?, ' SYSCOLUMNS ') AND " +
	" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
    ps.setString(1, "aSYSCOLUMNS");
    rs = ps.executeQuery();
    JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});

    //Following will not fail because collation of ? here does not matter 
    //since we are not doing a collation related method 
    s.executeUpdate("set schema SYS");
    ps = prepareStatement("INSERT INTO APP.CUSTOMER(NAME) VALUES(?)");
    ps.setString(1, "SYSCOLUMNS");
    ps.executeUpdate();
    ps.close();
    s.executeUpdate("INSERT INTO APP.CUSTOMER(NAME) VALUES('abc')");
    rs = s.executeQuery("SELECT COUNT(*) FROM APP.CUSTOMER ");
    JDBC.assertFullResultSet(rs,new String[][] {{"9"}});
    //following will fail because NAME has collation type of territory based
    //but 'abc' has collation type of UCS_BASIC
    assertStatementError("42818", s, "DELETE FROM APP.CUSTOMER WHERE NAME = 'abc'");
    //changing to APP schema will fix the problem
    s.executeUpdate("set schema APP");
    s.executeUpdate("DELETE FROM APP.CUSTOMER WHERE NAME = 'abc'");
    rs = s.executeQuery("SELECT COUNT(*) FROM APP.CUSTOMER ");
    JDBC.assertFullResultSet(rs,new String[][] {{"8"}});
    //End of parameter testing
    
    //The user table has to adhere to the collation type of the schema in which
    //it resides. If the table creation breaks that rule, then an exception 
    //will be thrown. DERBY-2879
    s.executeUpdate("set schema APP");
    //following fails as expected because otherwise character types in T will
    //have collation type of UCS_BASIC but the APP schema has collation of
    //territory based
    assertStatementError("42ZA3", s, "CREATE TABLE T AS SELECT TABLENAME " +
    		" FROM SYS.SYSTABLES WITH NO DATA");
    //But following will work because there is no character string type
    //involved. (DERBY-2959)
    s.executeUpdate("CREATE TABLE T AS SELECT COLUMNNUMBER FROM " +
    		" SYS.SYSCOLUMNS WITH NO DATA");
    
    //DERBY-2951
    //Following was giving Assert failure in store code because we were not
    //writing and reading the collation information from the disk.
    s.execute("create table assoc (x char(10) not null primary key, "+
    		" y char(100))");
    s.execute("create table assocout(x char(10))");
    ps = prepareStatement("insert into assoc values (?, 'hello')");
    ps.setString(1, "10");
    ps.executeUpdate();     
    
    //DERBY-2955
    //We should set the collation type in the bind phase of create table rather
    //than in code generation phase. Otherwise, following sql will give 
    //incorrect exception about collation mismatch for the LIKE clause
    s.execute("CREATE TABLE DERBY_2955 (EMPNAME CHAR(20), CONSTRAINT " +
    		" STAFF9_EMPNAME CHECK (EMPNAME NOT LIKE 'T%'))");
    
    //DERBY-2960
    //Following group by was failing earlier because we were generating
    //SQLVarchar rather than CollatorSQLVarchar in territory based db 
    s.execute("CREATE TABLE DERBY_2960 (C CHAR(10), V VARCHAR(50))");
    s.execute("INSERT INTO DERBY_2960 VALUES ('duplicate', 'is duplicated')");
    rs = s.executeQuery("SELECT SUBSTR(c||v, 1, 4), COUNT(*) FROM DERBY_2960" +
    		" GROUP BY SUBSTR(c||v, 1, 4)");
    JDBC.assertFullResultSet(rs,new String[][] {{"dupl","1"}});
    
    //DERBY-2966
    //Moving to insert row in a territory based db should not cause exception
    ps = conn.prepareStatement("SELECT * FROM CUSTOMER FOR UPDATE",
    		ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    rs = ps.executeQuery();
    rs.moveToInsertRow();
    rs.close();
    ps.close();
    
    //DERBY-2973
    //alter table modify column should not give an error
    s.execute("CREATE TABLE DERBY_2973 (V VARCHAR(40))");
    s.execute("CREATE INDEX DERBY_2973_I1 ON DERBY_2973 (V)");
    s.execute("ALTER TABLE DERBY_2973 ALTER V SET DATA TYPE VARCHAR(4096)");
    s.execute("INSERT INTO DERBY_2973 VALUES('hello')");
    
    //DERBY-2967
    //The special character _ should match one character and not just advance
    //by number of collation elements that special character _ represents
    s.executeUpdate("create table DERBY_2967(c11 int)"); 
    s.executeUpdate("insert into DERBY_2967 values 1"); 
    ps = prepareStatement("select 1 from DERBY_2967 where '\uFA2D' like ?");
    String[] match = { "%", "_", "\uFA2D" }; 
    for (int i = 0; i < match.length; i++) { 
        ps.setString(1, match[i]); 
        rs = ps.executeQuery(); 
        JDBC.assertFullResultSet(rs,new String[][] {{"1"}});
    }

    //DERBY-2961
    //Should generate collation sensitive data type when working with something
    //like V AS CLOB insdie XMLSERIALIZE as shown below 
    //SELECT ID, XMLSERIALIZE(V AS CLOB), XMLSERIALIZE(V AS CLOB) FROM 
    //    DERBY_2961 ORDER BY 1
    s.executeUpdate("set schema APP");
    if (XML.classpathMeetsXMLReqs()) {
        checkLangBasedQuery(s, "SELECT ID, XMLSERIALIZE(V AS CLOB) " +
        		" FROM DERBY_2961 ORDER BY 1",
        		new String[][] {{"1",null}});
    }
    
    // Test Collation for functions DERBY-2972
    s.executeUpdate("CREATE FUNCTION HELLO () RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.hello' LANGUAGE JAVA PARAMETER STYLE JAVA");
    s.executeUpdate("create table testing (a varchar(2024))");
    s.executeUpdate("insert into testing values('hello')");
    rs = s.executeQuery("select * from testing where a = HELLO()");
    JDBC.assertSingleValueResultSet(rs, "hello");
    s.executeUpdate("DROP FUNCTION hello");
    s.executeUpdate("DROP TABLE  testing");
    
    // Test system functions. Should have UCS_BASIC collation
    // so a statement like this won't work, we need to cast the function.
    assertStatementError("42818",s,"VALUES case WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') = '50000'  THEN 'LOGSHUTDOWN  ERRORS' ELSE 'DONT KNOW' END");
    // cast function output and we it will match the compilation schema and run
    rs = s.executeQuery("VALUES case WHEN CAST(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') AS VARCHAR(30000))   = '50000'  THEN 'LOGSHUTDOWN  ERRORS' ELSE 'DONT KNOW' END");
    JDBC.assertSingleValueResultSet(rs,"DONT KNOW");
    
    // Test system table function.  Should have UCS_BASIC collation
    s.executeUpdate("create table lockfunctesttable (i int)");
    conn.setAutoCommit(false);
    s.executeUpdate("insert into lockfunctesttable values(1)");
    // This statement should error because of collation mismatch
    assertStatementError("42818",s,"select * from SYSCS_DIAG.LOCK_TABLE where tablename = 'LOCKFUNCTESTTABLE'");
    // we have to use parameter markers for it to work.
    ps = prepareStatement("select * from SYSCS_DIAG.LOCK_TABLE where tablename = ?");
    ps.setString(1,"LOCKFUNCTESTTABLE");
    rs = ps.executeQuery();
    JDBC.assertDrainResults(rs,2);
    
    s.executeUpdate("drop table lockfunctesttable");
    
    
    //DERBY-2910 
    // Test proper collation is set for  implicit cast with 
    // UPPER(CURRENT_DATE) and concatonation.
    
    s.executeUpdate("create table a (vc varchar(30))");
    s.executeUpdate("insert into a values(CURRENT_DATE)");
    rs = s.executeQuery("select vc from a where vc <= CURRENT_DATE");
    
    assertEquals(1,JDBC.assertDrainResults(rs));
    rs = s.executeQuery("select vc from a where vc <= UPPER(CURRENT_DATE)");
    
    JDBC.assertDrainResults(rs,1);
    rs = s.executeQuery("select vc from a where vc <= LOWER(CURRENT_DATE)");
    
    JDBC.assertDrainResults(rs,1);    
    rs = s.executeQuery("select vc from a where vc <=  '' || CURRENT_DATE");
    
    JDBC.assertDrainResults(rs,1);
    rs = s.executeQuery("select vc from a where '' || CURRENT_DATE >= vc");
    JDBC.assertDrainResults(rs,1);
    
    assertStatementError("42818",s,"select TABLENAME FROM SYS.SYSTABLES WHERE UPPER(CURRENT_DATE) = TABLENAME");
    
    
   //Use a parameter as cast operand and cast that to character type. The
   // * resultant type should get it's collation from the compilation schema
   ps = prepareStatement("select vc from a where  CAST (? AS VARCHAR(30)) = vc");
   ps.setString(1,"hello");
   rs = ps.executeQuery();
   JDBC.assertEmpty(rs);
	
   //Binary arithmetic operators do casting if one of the operands is
   //string and other is numeric. Test that combination
   rs = s.executeQuery("select vc from a where '1.2' + 1.2 = 2.4");
   JDBC.assertDrainResults(rs,1);
   
   //Do testing with UNION and use the results of UNION in collation
   //comparison 
   s.executeUpdate("create table t1 (vc varchar(30))");
   s.executeUpdate("create table t2 (vc varchar(30))");
   s.executeUpdate("insert into t2 values('hello2')");
   rs = s.executeQuery("select vc from t2 where vc = (select vc from t2 union select vc from t)");
   JDBC.assertFullResultSet(rs, new String[][] {{"hello2"}});
   s.executeUpdate("drop table t1");
   s.executeUpdate("drop table t2");
   
   // Cast in escape in like
   // Get Current date for use in query
   rs = s.executeQuery("VALUES CURRENT_DATE");
   rs.next();
   java.sql.Date d = rs.getDate(1);
   s.executeUpdate("create table t1 (vc varchar(30))");
   s.executeUpdate("insert into t1 values(CURRENT_DATE)");
   ps = prepareStatement("select vc from t1 where vc like ? escape '%'");
   ps.setDate(1,d);
   rs = ps.executeQuery();
   JDBC.assertDrainResults(rs);
   s.executeUpdate("drop table t1");
   s.close();

}

// methods used for function testing.

/**
 * Name says it all
 * @return hello
 */
public static String hello() {
        return "hello";
}

/**
 * Just return the value as passed in.  Used to make sure 
 * order by works properly with collation with order by expression
 * @param val value to return
 * @return string passed in
 */
public static String mimic(String val) {
    return val;
}


private void setUpTable(Statement s) throws SQLException {

    s.execute("CREATE TABLE CUSTOMER(ID INT, NAME VARCHAR(40), NAMECHAR CHAR(40))");
    
    Connection conn = s.getConnection();

    PreparedStatement ps = conn.prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?)");
    for (int i = 0; i < NAMES.length; i++)
    {
            ps.setInt(1, i);
            ps.setString(2, NAMES[i]);
            ps.setString(3, NAMES[i]);
            ps.executeUpdate();
    }

    s.execute("create table xmlTable (x xml)");
    s.executeUpdate("insert into xmlTable values(null)");

    s.execute("create table DERBY_2961 (ID INT  GENERATED ALWAYS AS " +
    		" IDENTITY PRIMARY KEY, V XML)");
    s.executeUpdate("insert into DERBY_2961(V) values(null)");
    
    conn.commit();
    ps.close();
}

/**
 * Execute the passed statement and compare the results against the
 * expectedResult 
 *
 * @param s              statement object to use to execute the query
 * @param query          string with the query to execute.
 * @param expectedResult Null for this means that the passed query is 
 * expected to return an empty resultset. If not empty, then the resultset
 * from the query should match this parameter
 *
 * @throws SQLException
 */
private void checkLangBasedQuery(Statement s, String query, String[][] expectedResult) throws SQLException {
    ResultSet rs = s.executeQuery(query);
    if (expectedResult == null) //expecting empty resultset from the query
    	JDBC.assertEmpty(rs);
    else
    	JDBC.assertFullResultSet(rs,expectedResult);
}

/**
 * We should get a locale unavailable message because there is no support for 
 * locale xx.
 */
public void testMissingCollatorSupport() throws SQLException {
      String createDBurl = ";create=true;territory=xx;collation=TERRITORY_BASED";
	  try {
		  //Use following utility method rather than 
		  //DriverManager.getConnection because the following utility method 
		  //will use DataSource or DriverManager depending on the VM that is 
		  //being used. Use of DriverManager to get a Connection will faile
		  //on JSR169 VMs. DERBY-3052
		  TestUtil.getConnection("missingCollatorDB", createDBurl);
	  } catch (SQLException sqle) {
          //Database can't be created because Collator support does not exist
		  //for the requested locale
		  BaseJDBCTestCase.assertSQLState("Unexpected error when connecting to database ",
                  "XBM04",
                  sqle);
      }
}
    
  /**
   * Tests only need to run in embedded since collation
   * is a server side operation.
   */
  public static Test suite() {
      
      BaseTestSuite suite = new BaseTestSuite("CollationTest");
      //Add the test case for a locale which does not exist. We have asked for
      //locale as 'xx' and since there is not support Collator support for such
      //a locale, we will get an exception during database create time.
      TestCase missingCollatorDbTest = new CollationTest(
    		  "testMissingCollatorSupport");
      suite.addTest(missingCollatorDbTest);

        suite.addTest(new CleanDatabaseTestSetup(
                new CollationTest("testDefaultCollation")));
        suite.addTest(collatedSuite("en", false, ENGLISH_CASE_SENSITIVE));
        suite.addTest(collatedSuite("en", true, ENGLISH_CASE_INSENSITIVE));
         
        // Only add tests for other locales if they are in fact supported 
        // by the jvm.
        Locale[] availableLocales = Collator.getAvailableLocales();
        boolean norwegian = false; 
        boolean polish = false;
        boolean french = false;
        boolean swedish = false;
        for (int i=0; i<availableLocales.length ; i++) {
            if("no".equals(availableLocales[i].getLanguage())) {
                norwegian = true;
            }
            if("pl".equals(availableLocales[i].getLanguage())) {
                polish = true;
            }
            if("fr".equals(availableLocales[i].getLanguage())) {
                french = true;
            }
            if("sv".equals(availableLocales[i].getLanguage())) {
                swedish = true;
            }
        }
        if(norwegian) {
            suite.addTest(collatedSuite("no", false, NORWEGIAN_CASE_SENSITIVE));
        }
        if(polish) {
            suite.addTest(collatedSuite("pl", false, POLISH_CASE_SENSITIVE));
        }
        if(french) {
            suite.addTest(collatedSuite("fr", false, FRENCH_CASE_SENSITIVE));
        }
        if(swedish && !hasBuggySwedishLocale()) {
            suite.addTest(collatedSuite("sv", true, SWEDISH_CASE_INSENSITIVE));
        }
        return suite;
    }

    /**
     * Check whether the JVM suffers from this bug:
     * http://bugs.sun.com/view_bug.do?bug_id=4804273
     * If it does, the tests that use Swedish locale will fail.
     *
     * @return true if the bug is present, false otherwise
     */
    private static boolean hasBuggySwedishLocale() {
        Collator c = Collator.getInstance(new Locale("sv"));
        if (c.compare("aa", "ab") < 0) {
            // OK, aa should be less than ab with Swedish collation
            return false;
        } else {
            // this is a bug
            return true;
        }
    }
 
  /**
   * Return a suite that uses a single use database with
   * a set of test cases from this class, plus potentially some other
   * test cases.
   *
   * @param locale Locale to use for the database
   * @param caseInsensitive Indicates if the database should use a case insensitive collation.
   * @param testNames the tests to run in the specified locale
   * @return suite of tests to run for the given locale
   */
  private static Test collatedSuite(
          String locale, boolean caseInsensitive, String[] testNames)
  {
      BaseTestSuite suite =
          new BaseTestSuite("CollationTest:territory=" + locale);

      for (int i = 0; i < testNames.length; i++) {
          suite.addTest(new CollationTest(testNames[i]));
      }

      // DMD.getTables() should not fail after the fix to DERBY-2896
      /*
       * The following check condition was added because it would be 
       * sufficient forthese testsuites to run with a single locale 
       * and would save some time running tests.
       * Related To: JIRA DERBY-3554
       */
      
      if ("en".equals(locale)) {
    	  suite.addTest(DatabaseMetaDataTest.suite());
          suite.addTest(BatchUpdateTest.embeddedSuite());
          suite.addTest(GroupByExpressionTest.suite());
          suite.addTest(UpdatableResultSetTest.suite());    	  
      }
      return caseInsensitive
		  ? Decorator.territoryCollatedCaseInsensitiveDatabase(suite, locale)
		  : Decorator.territoryCollatedDatabase(suite, locale);
  }

    /**
     * Test for an overload which was missing.
     */
    public void test_5951() throws Exception
    {
        Statement s = createStatement();
        
        s.execute("CREATE TABLE derby5951( a clob )");
        s.execute
            (
             "create function makeClob( contents varchar( 32672 ) ) returns clob\n" +
             "language java parameter style java no sql deterministic\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.makeClob'\n"
             );
        s.executeUpdate("INSERT INTO derby5951 VALUES( makeClob( 'a' ) )");
        ResultSet rs = s.executeQuery("select * from derby5951");
        JDBC.assertSingleValueResultSet(rs, "a");
        
        s.executeUpdate("DROP TABLE derby5951");
        s.executeUpdate("DROP function makeClob");
    }

   /** Clob-creating function */
    public  static  Clob    makeClob( String contents ) throws Exception
    {
        return new HarmonySerialClob( contents );
    }

    /**
     * Regression test case for DERBY-6030. The escape sequence in a LIKE
     * expression should consist of a single character and a single collation
     * element. Before DERBY-6030, with non-literal escape sequences, Derby
     * would only check that the sequence consisted of a single collation
     * element, and might incorrectly accept escape sequences with more than
     * one character.
     */
    public void testLikeEscapeClauseLengthRestriction() throws SQLException {
        setAutoCommit(false);
        Statement s = createStatement();
        s.execute("create table d6030" +
                  "(x varchar(10), y varchar(10), z varchar(10))");
        s.execute("insert into d6030 values ('a', 'b', 'c')");

        PreparedStatement select = prepareStatement(
            "select * from d6030 where x like y escape z");

        PreparedStatement update = prepareStatement("update d6030 set z = ?");

        // Escape clause 'c' is OK.
        JDBC.assertEmpty(select.executeQuery());

        // Sharp-s is NOT OK, as it has two collation elements.
        update.setString(1, "\u00df");
        assertUpdateCount(update, 1);
        assertStatementError(INVALID_ESCAPE, select);

        // 'aa' is NOT OK, as it has two characters. This used to succeed with
        // Norwegian collation, which treats 'aa' as a single collation
        // element. But it should fail since it's two characters.
        update.setString(1, "aa");
        assertUpdateCount(update, 1);
        assertStatementError(INVALID_ESCAPE, select);

        // Also test the same queries with literals in the escape clause.
        // Those queries follow a different code path, and they produced the
        // expected results even before the fix.
        JDBC.assertEmpty(
            s.executeQuery("select * from d6030 where x like y escape 'c'"));
        assertStatementError(INVALID_ESCAPE, s,
                "select * from d6030 where x like y escape '\u00df'");
        assertStatementError(INVALID_ESCAPE, s,
                "select * from d6030 where x like y escape 'aa'");
    }

    /**
     * Regression test case for DERBY-6227. Distinct grouped aggregates used
     * to eliminate duplicates by comparing the string representation of the
     * values using {@code String.equals()}. That does not give the right
     * results if the database collation defines equality in a different way
     * than {@code String.equals()}, for example when running with case
     * insensitive collation ({@code collation=TERRITORY_BASED:PRIMARY}).
     */
    public void testDerby6227() throws SQLException {
        String sql = "select i, count(distinct s) "
                + "from (values (1, 'a'), (1, 'a'), (2, 'b'), (2, 'B'), "
                + "(3, 'a'), (3, 'A'), (3, 'b'), (3, 'B'), (3, 'c')) v(i, s) "
                + "group by i order by i";

        // These are the expected results of the query when running with
        // case-insensitive collation. Before the fix, the query would return
        // (1, 1), (2, 2), (3, 5).
        String[][] expectedRows = {
            { "1", "1" }, { "2", "1" }, { "3", "3" }
        };

        Statement s = createStatement();
        JDBC.assertFullResultSet(s.executeQuery(sql), expectedRows);
    }
    public void testDerby6890()
                throws SQLException
    {
        Statement s = createStatement();
        s.execute( "CREATE TABLE Module (id BIGINT NOT NULL," +
                       " title VARCHAR(200)," +
                       " CONSTRAINT PK_MODULE PRIMARY KEY (id))" );
        s.execute( "CREATE INDEX Module_title ON Module(title)" );
        s.execute( "ALTER TABLE MODULE ADD COLUMN ID_TEMP BIGINT" +
                       " GENERATED BY DEFAULT AS IDENTITY" );
        s.execute( "UPDATE MODULE SET ID_TEMP = ID" );
        s.execute( "ALTER TABLE MODULE ALTER COLUMN ID_TEMP NOT NULL" );
        s.execute( "ALTER TABLE MODULE DROP ID" );
        s.execute( "RENAME COLUMN MODULE.ID_TEMP TO ID" );
        s.execute( "ALTER TABLE MODULE ADD CONSTRAINT PK_MODULE" +
                       " PRIMARY KEY (ID)" );

//          !!!!!!!!!!! WORKAROUND !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//            'DROP INDEX Module_title;\n' +
//            'CREATE INDEX Module_title ON Module(title);\n' +

        PreparedStatement pSt =
                prepareStatement("insert into Module(title) values(?)");

        int i = 0;
        setAutoCommit(false);
        while (i < 295) {
            pSt.setString(1, "1234567890123456789012345678901234567890" +
                             "1234567890123456789012345678901234567890" +
                             "1234567890123456789012345678901234567890" +
                             "1234567890123456789012345678901234567890" +
                             "1234567890123456789012345678901234567890" );
            pSt.executeUpdate();
            i++;
        }
        commit();
    }

}