You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2007/05/26 01:45:48 UTC
svn commit: r541822 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java
testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
Author: mikem
Date: Fri May 25 16:45:47 2007
New Revision: 541822
URL: http://svn.apache.org/viewvc?view=rev&rev=541822
Log:
DERBY-2670
Changed WorkHorseForCollatorDatatypes to not cache info about the collation
elements across calls to like on the object. This info needed to get
invalidated when object for a column was reused to process the next row.
Added a test to CollationTest2 based on bug report.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java?view=diff&rev=541822&r1=541821&r2=541822
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java Fri May 25 16:45:47 2007
@@ -229,25 +229,41 @@
return (int[]) null;
}
+
+
+ // Caching of collationElementsForString is not working properly, in
+ // order to cache it needs to get invalidated everytime the container
+ // type's value is changed - through any interface, eg: readExternal,
+ // setValue, ... To get proper behavior, disabling caching, and will
+ // file a performance enhancement to implement correct caching.
+ collationElementsForString = null;
+ countOfCollationElements = 0;
+
+
if (collationElementsForString != null)
{
return collationElementsForString;
}
- // countOfCollationElements should always be 0 when collationElementsForString is null
+ // countOfCollationElements should always be 0 when
+ // collationElementsForString is null
if (SanityManager.DEBUG)
{
if (countOfCollationElements != 0)
{
SanityManager.THROWASSERT(
- "countOfCollationElements expected to be 0, not " + countOfCollationElements);
+ "countOfCollationElements expected to be 0, not " +
+ countOfCollationElements);
}
}
+
collationElementsForString = new int[stringData.getLength()];
- CollationElementIterator cei = collatorForCharacterDatatypes.getCollationElementIterator(
- stringData.getString());
+ CollationElementIterator cei =
+ collatorForCharacterDatatypes.getCollationElementIterator(
+ stringData.getString());
+
int nextInt;
while ((nextInt = cei.next()) != CollationElementIterator.NULLORDER)
{
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java?view=diff&rev=541822&r1=541821&r2=541822
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java Fri May 25 16:45:47 2007
@@ -50,7 +50,7 @@
T3: (DONE) order by on heap using in memory sorter
T4: (TODO) order by on heap using disk based sorter
T5: (TODO) system catalogs should not be collated
-T6: (TODO) test like
+T6: (DONE) test like
T7: (TODO) test create conglomerate triggered by DiskHashtable code
T8: (TODO) test create conglomerate triggered by DataDictionaryImpl
T9: (TODO) test create conglomerate triggered by java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java
@@ -63,6 +63,30 @@
T15: (TODO) java/engine/org/apache/derby/impl/sql/execute/MaterializedResultSet.java
T16: (TODO) /java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java
T17: (TODO) /java/engine/org/apache/derby/impl/store/access/PropertyConglomerate.java
+T18: (TODO) upgrade tests - may be changes to upgrade suite rather than here.
+T19: (TODO) recovery testing - may be old function harness changes as no one has
+ suggested how to do this in junit harness.
+T20: (TODO) For both a newly created 10.3 database and an upgraded 10.3
+ database, make sure that metadata continues to show the scale for
+ character datatypes as 0 (rather than the collation type value).
+ That is, test that the scale of the character datatypes is always
+ 0 and it didn't get impacted negatively by the overloading of scale
+ field as collation type in TypeDescriptor.
+T21: (TODO) Testing with views
+T22: (TODO) Testing with global temporary tables
+T23: (TODO) Alter table testing. Two specific cases 1)add a new character column and 2)increase the length of an existing character
+T24: (TODO) Need to add a test case for DERBY-2669 If no territory attribute is not specified at create database time, then we should ignore the collation attribute if specified.
+column(this 2nd test should not impact the collation type setting of the character column).
+
+13)Performance- CollatorSQLChar has a method called getCollationElementsForString which currently gets called by like method.
+getCollationElementsForString gets the collation elements for the value of CollatorSQLChar class. But say like method is looking
+for pattern 'A%' and the value of CollatorSQLChar is 'BXXXXXXXXXXXXXXXXXXXXXXX'. This is eg of one case where it would have been
+better to get collation element one character of CollatorSQLChar value at a time so we don't go through the process of getting
+collation elements for the entire string when we don't really need. This is a performance issue and could be taken up at the end
+of the implementation. Comments on this from Dan and Dag can be found in DERBY-2416.
+15)DERBY-2668 At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we
+should throw a meaningful error message
+
**/
@@ -73,7 +97,12 @@
**************************************************************************
*/
-
+
+ /**
+ * Set to get output if something in the test is failing and you want
+ * more information about what was going on.
+ **/
+ private static final boolean verbose_debug = false;
private static final int TEST_DEFAULT = 0;
private static final int TEST_ENGLISH = 1;
@@ -110,7 +139,7 @@
null,
"en",
"pl",
- "no"
+ "no_NO"
};
@@ -178,6 +207,122 @@
NORWAY_NAME_ORDER
};
+
+ /**
+ * set up LIKE test cases, configured for all languages by
+ * the TEST_* constants.
+ * <p>
+ * Insert all data to tested against into LIKE_NAMES. A customer table
+ * will be filled with this data.
+ * {p>
+ * Insert test cases for like string into the LIKE_TEST_CASES, results
+ * are expected only to return a single row.
+ * <p>
+ * Insert actual string expected back for each language, for each test
+ * case in the {LANG}_LIKE_RESULT array. Insert null if no match is
+ * expected.
+ * <p>
+ * Current test tries all 4 datatypes, CHAR will blank pad making the
+ * results different than the other datatypes if data is shorter than
+ * type, thus a different set of LIKE clauses needs to be entered in the
+ * LIKE_CHAR_TEST_CASES which should match the same results in a CHAR
+ * field as does the corresponding LIKE_TEST_CASES test.
+ *
+ **/
+ private static final String[] LIKE_NAMES =
+ {
+ "Waagan", // 0
+ "Smith", // 1
+ "Zebra", // 2
+ "xcorn", // 3
+ "aBebra", // 4
+ "Acorn", // 5
+ "Amith", // 6
+ "aacorn", // 7
+ "xxxaa", // 8
+ "aaxxx", // 9
+ "yyyaa y", // 10
+ };
+
+ private static final String[] LIKE_TEST_CASES =
+ {
+ "Waagan",
+ "W_gan",
+ "aaxxx",
+ "_xxx",
+ "xxxaa",
+ "xxx_",
+ "xxx_%",
+ "yyy_%"
+ };
+ private static final String[] LIKE_CHAR_TEST_CASES =
+ {
+ "Waagan ",
+ "W_gan ",
+ "aaxxx%",
+ "_xxx%",
+ "xxx%",
+ "xxx_ %",
+ "xxx%",
+ "yyy_%"
+ };
+
+ private static final int[] DEFAULT_LIKE_RESULT =
+ {
+ 0,
+ -1,
+ 9,
+ -1,
+ 8,
+ -1,
+ 8,
+ 10
+ };
+
+ private static final int[] ENGLISH_LIKE_RESULT =
+ {
+ 0,
+ -1,
+ 9,
+ -1,
+ 8,
+ -1,
+ 8,
+ 10
+ };
+
+ private static final int[] POLISH_LIKE_RESULT =
+ {
+ 0,
+ -1,
+ 9,
+ -1,
+ 8,
+ -1,
+ 8,
+ 10
+ };
+
+ private static final int[] NORWAY_LIKE_RESULT =
+ {
+ 0,
+ 0,
+ 9,
+ 9,
+ 8,
+ 8,
+ 8,
+ 10
+ };
+
+ private static final int[][] EXPECTED_LIKE_RESULTS =
+ {
+ DEFAULT_LIKE_RESULT,
+ ENGLISH_LIKE_RESULT,
+ POLISH_LIKE_RESULT,
+ NORWAY_LIKE_RESULT
+ };
+
/**************************************************************************
* Constructors for This class:
@@ -194,6 +339,34 @@
**************************************************************************
*/
+ /**
+ * Creates a database and return connection to database.
+ * <p>
+ * Creates a database with territory/collation as indicated by db_index
+ * test case.
+ * Database name: TEST_DATABASE[db_index]
+ * territory attribute: TEST_CONNECTION_ATTRIBUTE[db_index]
+ **/
+ private Connection setUpDBandOpenConnection(int db_index)
+ throws SQLException
+ {
+ DataSource ds =
+ JDBCDataSource.getDataSourceLogical(TEST_DATABASE[db_index]);
+
+ String conn_string =
+ "create=true" +
+ ((TEST_CONNECTION_ATTRIBUTE[db_index] == null) ?
+ "" :
+ ";territory=" +
+ TEST_CONNECTION_ATTRIBUTE[db_index] +
+ ";collation=TERRITORY_BASED");
+
+ JDBCDataSource.setBeanProperty(ds, "connectionAttributes", conn_string);
+
+ Connection conn = ds.getConnection();
+
+ return(conn);
+ }
private void setUpTable(Connection conn) throws SQLException
{
@@ -224,6 +397,36 @@
s.close();
}
+ private void setUpLikeTable(Connection conn) throws SQLException
+ {
+ Statement s = conn.createStatement();
+ s.execute(
+ "CREATE TABLE CUSTOMER (" +
+ "NAME_CHAR CHAR(10), " +
+ "NAME_VARCHAR VARCHAR(40)," +
+ "NAME_LONGVARCHAR LONG VARCHAR," +
+ "NAME_CLOB CLOB," +
+ "ID INT)");
+
+ conn.setAutoCommit(false);
+ PreparedStatement ps =
+ conn.prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?)");
+
+ for (int i = 0; i < LIKE_NAMES.length; i++)
+ {
+ ps.setString(1, LIKE_NAMES[i]);
+ ps.setString(2, LIKE_NAMES[i]);
+ ps.setString(3, LIKE_NAMES[i]);
+ ps.setString(4, LIKE_NAMES[i]);
+ ps.setInt( 5, i);
+ ps.executeUpdate();
+ }
+
+ conn.commit();
+ ps.close();
+ s.close();
+ }
+
/**
* Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
*/
@@ -281,10 +484,19 @@
/**
* Produce an expect row set given the order and asc/desc info.
* <p>
- * Input array gives the offset into the NAMES array of data of the
- * expected order of rows to return for this test.
+ * Given the expected order of rows, the offset of first and last row
+ * to return, and whether rows will be ascending or descending produce
+ * a 2d expected row set. Each row in the row set represents a row
+ * with 2 columns (ID, NAME) from the CUSTOMER table used throughout
+ * this test.
*
* @param expected_order Expected order of rows in this language.
+ * @param start_offset expect rows starting at
+ * expected_order[start_offset] up to and including
+ * expected_order[stop_offset].
+ * @param stop_offset expect rows starting at
+ * expected_order[start_offset] up to and including
+ * expected_order[stop_offset].
* @param ascending_order true if rows are in order, else rows are in
* reverse order.
**/
@@ -329,10 +541,42 @@
return(ret_order);
}
+ /**
+ * Produce an expect row set given list and offset of row in list.
+ * <p>
+ * Given the list of rows and offset of the expected row in the list
+ * produce a 2d expected row set. If expected_row is -1 then no row
+ * set is returned. Each row in the row set represents a row
+ * with 2 columns (ID, NAME) from the CUSTOMER table used throughout
+ * this test.
+ *
+ * @param expected_row -1 if no expected row, else
+ * ret_list[expected_row] is single value expected.
+ * @param ret_list list of strings in data set.
+ **/
+ private String[][] full_row_single_value(
+ int expected_row,
+ String[] ret_list)
+ {
+ String[][] ret_order = null;
+
+ if (expected_row != -1)
+ {
+ // if not -1 then exactly one row expected.
+ ret_order = new String[1][2];
+ ret_order[0][0] = String.valueOf(expected_row);
+ ret_order[0][1] = ret_list[expected_row];
+ }
+
+ return(ret_order);
+ }
+
+
private void checkLangBasedQuery(
Connection conn,
String query,
- String[][] expectedResult)
+ String[][] expectedResult,
+ boolean ordered)
throws SQLException
{
Statement s = conn.createStatement();
@@ -410,7 +654,8 @@
expected_order,
0,
i - 1,
- true));
+ true),
+ true);
// '<=' test
checkLangBasedQuery(
@@ -421,7 +666,8 @@
expected_order,
0,
i,
- true));
+ true),
+ true);
// '=' test
checkLangBasedQuery(
@@ -432,7 +678,8 @@
expected_order,
i,
i,
- true));
+ true),
+ true);
// '>=' test
checkLangBasedQuery(
@@ -443,7 +690,8 @@
expected_order,
i,
expected_order.length - 1,
- true));
+ true),
+ true);
// '>' test
@@ -455,7 +703,8 @@
expected_order,
i + 1,
expected_order.length - 1,
- true));
+ true),
+ true);
// now check prepared query
@@ -560,7 +809,8 @@
expected_order,
4,
expected_order.length - 1,
- true));
+ true),
+ true);
// check persistent compared to persistent - CHAR TO VARCHAR,
// should return rows bigger than 3rd in expected order.
@@ -571,7 +821,8 @@
expected_order,
4,
expected_order.length - 1,
- true));
+ true),
+ true);
// check persistent compared to persistent - VARCHAR TO VARCHAR,
// should return rows bigger than 3rd in expected order.
@@ -582,7 +833,8 @@
expected_order,
4,
expected_order.length - 1,
- true));
+ true),
+ true);
// check persistent compared to persistent - CHAR TO CHAR,
// should return rows bigger than 3rd in expected order.
@@ -593,7 +845,8 @@
expected_order,
4,
expected_order.length - 1,
- true));
+ true),
+ true);
// put back data the way it was on entry to test.
conn.rollback();
@@ -632,7 +885,8 @@
EXPECTED_NAME_ORDER[db_index],
0,
EXPECTED_NAME_ORDER[db_index].length - 1,
- true));
+ true),
+ true);
// Simple check of getting all rows back in order
checkLangBasedQuery(
@@ -642,7 +896,8 @@
EXPECTED_NAME_ORDER[db_index],
0,
EXPECTED_NAME_ORDER[db_index].length - 1,
- true));
+ true),
+ true);
// Simple check of getting all rows back in opposite order
checkLangBasedQuery(
@@ -652,7 +907,8 @@
EXPECTED_NAME_ORDER[db_index],
0,
EXPECTED_NAME_ORDER[db_index].length - 1,
- false));
+ false),
+ true);
// Check <, <=, =, >=, > operators on constant vs. column
checkSimpleCompare(conn, EXPECTED_NAME_ORDER[db_index]);
@@ -666,6 +922,72 @@
conn.commit();
}
+ /**
+ * Test various like expressions against all string datatypes.
+ *
+ * T6: (DONE) test like
+ * @throws SQLException
+ **/
+ private void runLikeTests(
+ Connection conn,
+ int db_index)
+ throws SQLException
+ {
+ setUpLikeTable(conn);
+
+ for (int i = 0; i < LIKE_TEST_CASES.length; i++)
+ {
+ if (verbose_debug)
+ {
+ System.out.println(
+ "Running like test[" + i + "] = " + LIKE_TEST_CASES[i]);
+ }
+
+ // varchar column
+ checkLangBasedQuery(
+ conn,
+ "SELECT ID, NAME_VARCHAR FROM CUSTOMER WHERE NAME_VARCHAR LIKE " +
+ "'" + LIKE_TEST_CASES[i] + "'",
+ full_row_single_value(
+ EXPECTED_LIKE_RESULTS[db_index][i],
+ LIKE_NAMES),
+ true);
+
+ // long varchar column
+ checkLangBasedQuery(
+ conn,
+ "SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER WHERE NAME_LONGVARCHAR LIKE " +
+ "'" + LIKE_TEST_CASES[i] + "'",
+ full_row_single_value(
+ EXPECTED_LIKE_RESULTS[db_index][i],
+ LIKE_NAMES),
+ true);
+
+ // clob column
+ checkLangBasedQuery(
+ conn,
+ "SELECT ID, NAME_CLOB FROM CUSTOMER WHERE NAME_CLOB LIKE " +
+ "'" + LIKE_TEST_CASES[i] + "'",
+ full_row_single_value(
+ EXPECTED_LIKE_RESULTS[db_index][i],
+ LIKE_NAMES),
+ true);
+
+ // char column, char includes blank padding so alter all these
+ // tests cases to match for blanks at end also.
+ checkLangBasedQuery(
+ conn,
+ "SELECT ID, NAME_CHAR FROM CUSTOMER WHERE NAME_CHAR LIKE " +
+ "'" + LIKE_CHAR_TEST_CASES[i] + "%'",
+ full_row_single_value(
+ EXPECTED_LIKE_RESULTS[db_index][i],
+ LIKE_NAMES),
+ true);
+ }
+
+ dropTable(conn);
+ }
+
/**
* test paths through alter table compress
@@ -871,6 +1193,55 @@
conn.commit();
}
+
+ /**
+ * Test case for DERBY-2670 - problem with like in no like processing.
+ * <p>
+ * Before fix, the table/query below would return results like B and
+ * C, obviously wrong for like %a%. The code was incorrectly caching
+ * collation key info in a DataValueDescriptor across the reuse of the
+ * holder object from one row to the next.
+ **/
+ private void runDerby2670(
+ Connection conn)
+ throws SQLException
+ {
+ Statement s = conn.createStatement();
+
+ String[] rows =
+ { "Waagan", "W\u00E5han", "Wanvik", "Wågan", "ekstrabetaling",
+ "ekstraarbeid", "ekstra\u00ADarbeid", "\u00ADa", "a", "\u00ADb",
+ "b", "-a", "-b", " a", " b", "A", "B", "C"
+ };
+
+
+ s.executeUpdate("create table t (x varchar(20))");
+ PreparedStatement ps = conn.prepareStatement("insert into t values ?");
+ for (int i = 0; i < rows.length; i++) {
+ ps.setString(1, rows[i]);
+ ps.executeUpdate();
+ }
+
+ checkLangBasedQuery(
+ conn,
+ "select * from t where x like '%a%'",
+ new String[][]
+ {
+ {"Waagan"}, {"W\u00E5han"}, {"Wanvik"}, {"Wågan"},
+ {"ekstrabetaling"}, {"ekstra\u00ADarbeid"}, {"\u00ADa"}, {"a"},
+ {"-a"}, {" a"}
+ },
+ true);
+
+ s.executeUpdate("drop table t");
+
+ conn.commit();
+
+ // cleanup
+ ps.close();
+ s.close();
+ }
+
/**
* Shared code to run all test cases against a single collation.
* <p>
@@ -882,27 +1253,15 @@
*
* @exception SQLException
**/
- private void runTestIter(int db_index) throws SQLException
+ private void runTestIter(
+ Connection conn,
+ int db_index)
+ throws SQLException
{
- DataSource ds =
- JDBCDataSource.getDataSourceLogical(TEST_DATABASE[db_index]);
-
- String conn_string =
- "create=true" +
- ((TEST_CONNECTION_ATTRIBUTE[db_index] == null) ?
- "" :
- ";territory=" +
- TEST_CONNECTION_ATTRIBUTE[db_index] +
- ";collation=TERRITORY_BASED");
-
- JDBCDataSource.setBeanProperty(ds, "connectionAttributes", conn_string);
-
- Connection conn = ds.getConnection();
Statement s = conn.createStatement();
setUpTable(conn);
-
// run tests against base table no index, exercise heap path
// Tests the following:
// T0: Heap based compare using predicate pushing
@@ -947,14 +1306,14 @@
runBulkInsert(conn, db_index);
+ runLikeTests(conn, db_index);
+
/*
TODO -MIKEM, this test does not work yet.
runAlterTableDropColumn(conn, db_index);
*/
-
conn.commit();
- conn.close();
}
/**************************************************************************
@@ -964,23 +1323,34 @@
public void testDefaultCollation() throws SQLException
{
- runTestIter(TEST_DEFAULT);
+ Connection conn = setUpDBandOpenConnection(TEST_DEFAULT);
+ runTestIter(conn, TEST_DEFAULT);
+ conn.close();
}
public void testEnglishCollation() throws SQLException
{
- runTestIter(TEST_ENGLISH);
+ Connection conn = setUpDBandOpenConnection(TEST_ENGLISH);
+ runTestIter(conn, TEST_ENGLISH);
+ conn.close();
}
public void testPolishCollation() throws SQLException
{
- runTestIter(TEST_POLISH);
+ Connection conn = setUpDBandOpenConnection(TEST_POLISH);
+ runTestIter(conn, TEST_POLISH);
+ conn.close();
}
public void testNorwayCollation() throws SQLException
{
- runTestIter(TEST_NORWAY);
+ Connection conn = setUpDBandOpenConnection(TEST_NORWAY);
+ runTestIter(conn, TEST_NORWAY);
+
+ runDerby2670(conn);
+ conn.close();
}
- public static Test suite() {
+ public static Test suite()
+ {
Test test =
TestConfiguration.embeddedSuite(CollationTest2.class);