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 ma...@apache.org on 2007/05/11 23:26:21 UTC
svn commit: r537296 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/ engine/org/apache/derby/iapi/types/
engine/org/apache/derby/impl/jdbc/ engine/org/apache/derby/impl/sql/
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/d...
Author: mamta
Date: Fri May 11 14:26:20 2007
New Revision: 537296
URL: http://svn.apache.org/viewvc?view=rev&rev=537296
Log:
DERBY-2335 Committing patch DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt attached to DERBY-2335 which addresses 3 issues and adds some tests
1)Easiest first, fixed the javadoc error in WorkHorseForCollatorDatatypes.java
2)CharConstantNode in it's bind method does the collation setting based on the compilation schema. But it didn't do the switching
of it's value from SQLChar/SQLVarchar/SQLLongvarchar/SQLClob to CollatorSQLChar/CollatoSQLVarchar/CollatoSQLLongvarchar/CollatoSQLClob
if the collation type for it ends up being territory based. By default, the value associated with CharConstantNode is always
UCS_BASIC collation. It should get switched to territory based and my fix in this class does that job.
3)DataTypeDesciptor.getNull value currently gets the DVD using typeId.getNull(). But we should check if we are dealing with
territory based collation and if yes, then we should change the DVD type returned by typeId.getNull from
SQLChar/SQLVarchar/SQLLongvarchar/SQLClob to CollatorSQLChar/CollatoSQLVarchar/CollatoSQLLongvarchar/CollatoSQLClob. My change
in DataTypeDescriptor.getNull does that job.
4)In addition, I have added tests in CollationTest class to do some persistent character columns testing. Some tests are
commented out and will be added later.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/WorkHorseForCollatorDatatypes.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharConstantNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UntypedNullConstantNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java Fri May 11 14:26:20 2007
@@ -40,7 +40,7 @@
* corresponding to the passed in type for each parameter.
* @param types expected to match the number of parameters.
*/
- void initialize(DataTypeDescriptor[] types);
+ void initialize(DataTypeDescriptor[] types) throws StandardException;
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java Fri May 11 14:26:20 2007
@@ -40,7 +40,9 @@
import org.apache.derby.iapi.services.loader.ClassFactory;
import org.apache.derby.iapi.services.loader.ClassInspector;
+import org.apache.derby.iapi.sql.conn.ConnectionUtil;
+import java.text.RuleBasedCollator;
import org.apache.derby.iapi.reference.JDBC30Translation;
import org.apache.derby.iapi.reference.SQLState;
@@ -735,8 +737,28 @@
/**
Get a Null for this type.
*/
- public DataValueDescriptor getNull() {
- return typeId.getNull();
+ public DataValueDescriptor getNull() throws StandardException {
+ DataValueDescriptor returnDVD = typeId.getNull();
+ //If we are dealing with default collation, then we have got the
+ //right DVD already. Just return it.
+ if (typeDescriptor.getCollationType() == StringDataValue.COLLATION_TYPE_UCS_BASIC)
+ return returnDVD;
+ //If we are dealing with territory based collation and returnDVD is
+ //of type StringDataValue, then we need to return a StringDataValue
+ //with territory based collation.
+ if (returnDVD instanceof StringDataValue) {
+ try {
+ RuleBasedCollator rbs = ConnectionUtil.getCurrentLCC().getDataValueFactory().
+ getCharacterCollator(typeDescriptor.getCollationType());
+ return ((StringDataValue)returnDVD).getValue(rbs);
+ }
+ catch( java.sql.SQLException sqle)
+ {
+ throw StandardException.plainWrapException( sqle);
+ }
+ }
+ else
+ return returnDVD;
}
/**
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=537296&r1=537295&r2=537296
==============================================================================
--- 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 11 14:26:20 2007
@@ -82,7 +82,7 @@
this.stringData = stringData;
}
- /** @see SQLChar.stringCompare(SQLChar, SQLChar) */
+ /** @see SQLChar#stringCompare(SQLChar, SQLChar) */
protected int stringCompare(SQLChar str1, SQLChar str2)
throws StandardException
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java Fri May 11 14:26:20 2007
@@ -267,14 +267,18 @@
// updated column information if the columns can be updated.
if (concurrencyOfThisResultSet == JDBC20Translation.CONCUR_UPDATABLE)
{
- //initialize arrays related to updateRow implementation
- columnGotUpdated = new boolean[columnCount];
- updateRow = factory.getValueRow(columnCount);
- for (int i = 1; i <= columnCount; i++) {
- updateRow.setColumn(i, resultDescription.getColumnDescriptor(i).
- getType().getNull());
+ try{
+ //initialize arrays related to updateRow implementation
+ columnGotUpdated = new boolean[columnCount];
+ updateRow = factory.getValueRow(columnCount);
+ for (int i = 1; i <= columnCount; i++) {
+ updateRow.setColumn(i, resultDescription.getColumnDescriptor(i).
+ getType().getNull());
+ }
+ initializeUpdateRowModifiers();
+ } catch (StandardException t) {
+ throw noStateChangeException(t);
}
- initializeUpdateRowModifiers();
} else {
updateRow = null;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java Fri May 11 14:26:20 2007
@@ -105,7 +105,7 @@
* Initialize the set by allocating a holder DataValueDescriptor object
* for each parameter.
*/
- public void initialize(DataTypeDescriptor[] types)
+ public void initialize(DataTypeDescriptor[] types) throws StandardException
{
for (int i = 0; i < parms.length; i++)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharConstantNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharConstantNode.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharConstantNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CharConstantNode.java Fri May 11 14:26:20 2007
@@ -162,6 +162,20 @@
//from the schema it is getting compiled in.
LanguageConnectionContext lcc = ConnectionUtil.getCurrentLCC();
getTypeServices().setCollationType(lcc.getDefaultSchema().getCollationType());
+
+ //Once we have the collation type, we should check if the value
+ //associated with this node should change from
+ //SQLChar/SQLVarchar/SQLLongvarchar/SQLClob
+ //to
+ //CollatorSQLChar/CollatoSQLVarchar/CollatoSQLLongvarchar/CollatoSQLClob.
+ //By default, the value associated with char constants are SQLxxx
+ //kind because that is what is needed for UCS_BASIC collation. But
+ //if at this bind time, we find that the char constant's collation
+ //type is territory based, then we should change value from SQLxxx
+ //to CollatorSQLxxx. That is what is getting done below.
+ value = ((StringDataValue)value).getValue(
+ lcc.getDataValueFactory().getCharacterCollator(
+ getTypeServices().getCollationType()));
}
catch( java.sql.SQLException sqle)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UntypedNullConstantNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UntypedNullConstantNode.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UntypedNullConstantNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UntypedNullConstantNode.java Fri May 11 14:26:20 2007
@@ -88,6 +88,7 @@
*
*/
public DataValueDescriptor convertDefaultNode(DataTypeDescriptor typeDescriptor)
+ throws StandardException
{
/*
** The default value is null, so set nullability to TRUE
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java Fri May 11 14:26:20 2007
@@ -249,7 +249,8 @@
PreparedStatement.
*/
- public final void setupActivation(ExecPreparedStatement ps, boolean scrollable) {
+ public final void setupActivation(ExecPreparedStatement ps, boolean scrollable)
+ throws StandardException {
preStmt = ps;
if (ps != null) {
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?view=diff&rev=537296&r1=537295&r2=537296
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Fri May 11 14:26:20 2007
@@ -64,15 +64,46 @@
JDBCDataSource.setBeanProperty(ds, "connectionAttributes",
"create=true");
setUpTable(ds);
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
+
+ Connection conn = ds.getConnection();
+ conn.setAutoCommit(false);
+ Statement s = conn.createStatement();
+
+ 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"}});
+ {"6","aacorn"}, {"2","\u0104corn"},{"5","\u015Amith"},{"3","\u017Bebra"} });
+
+ //COMPARISONS INVOLVING CONSTANTS
//In default JVM territory, 'aacorn' is != 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
null);
//In default JVM territory, 'aacorn' is not < 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < '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.close();
+ conn.commit();
+
dropTable(ds);
}
@@ -85,17 +116,50 @@
JDBCDataSource.setBeanProperty(ds, "connectionAttributes",
"create=true;territory=pl;collation=TERRITORY_BASED");
setUpTable(ds);
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
+
+ Connection conn = ds.getConnection();
+ conn.setAutoCommit(false);
+ Statement s = conn.createStatement();
+
+ 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"}});
+ {"0","Smith"},{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"} });
+
+ //COMPARISONS INVOLVING CONSTANTS
//In Polish, 'aacorn' is != 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
null);
//In Polish, 'aacorn' is < 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < '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"}});
+ {"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' ");
+
+ s.close();
+ conn.commit();
+
dropTable(ds);
}
@@ -110,15 +174,47 @@
JDBCDataSource.setBeanProperty(ds, "connectionAttributes",
"create=true;territory=no;collation=TERRITORY_BASED");
setUpTable(ds);
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
+
+ Connection conn = ds.getConnection();
+ conn.setAutoCommit(false);
+ Statement s = conn.createStatement();
+
+ 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"}});
+ {"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"}, {"6","aacorn"} });
+
+ //COMPARISONS INVOLVING CONSTANTS
//In Norway, 'aacorn' is != 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
null);
//In Norway, 'aacorn' is not < 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < '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' ");
+
+ s.close();
+ conn.commit();
+
dropTable(ds);
}
@@ -133,17 +229,50 @@
JDBCDataSource.setBeanProperty(ds, "connectionAttributes",
"create=true;territory=en;collation=TERRITORY_BASED");
setUpTable(ds);
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
+
+ Connection conn = ds.getConnection();
+ conn.setAutoCommit(false);
+ Statement s = conn.createStatement();
+
+ 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"}});
+ {"5","\u015Amith"},{"1","Zebra"},{"3","\u017Bebra"} });
+
+ //COMPARISONS INVOLVING CONSTANTS
//In English, 'aacorn' != 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
null);
//In English, 'aacorn' is < 'Acorn'
- checkLangBasedQuery(ds, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' < '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"}});
+ {"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' ");
+
+ s.close();
+ conn.commit();
+
dropTable(ds);
}
@@ -181,18 +310,12 @@
* from the query should match this paramter
* @throws SQLException
*/
-private void checkLangBasedQuery(DataSource ds, String query, String[][] expectedResult) throws SQLException {
- Connection conn = ds.getConnection();
- conn.setAutoCommit(false);
-
- Statement s = conn.createStatement();
+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);
- s.close();
- conn.commit();
}
public static Test suite() {