Google Git
Sign in
apache / derby / dbed020d3949dd7707a6eca23741aa5ba155ece1 / . / java / testing / org / apache / derbyTesting / functionTests / tests / lang / CollationTest.java
blob: a03700aa2a1b4decd10ed67f907c2687a50ac518 [file] [log] [blame]
/**
* 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();
}
}