Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 45134 invoked from network); 30 Aug 2007 15:28:36 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 30 Aug 2007 15:28:36 -0000 Received: (qmail 10332 invoked by uid 500); 30 Aug 2007 15:28:32 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 10302 invoked by uid 500); 30 Aug 2007 15:28:32 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 10291 invoked by uid 99); 30 Aug 2007 15:28:31 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Aug 2007 08:28:31 -0700 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Aug 2007 15:29:34 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 39D861A9832; Thu, 30 Aug 2007 08:28:11 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r571208 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/lang/ junit/ Date: Thu, 30 Aug 2007 15:28:10 -0000 To: derby-commits@db.apache.org From: kmarsden@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20070830152811.39D861A9832@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kmarsden Date: Thu Aug 30 08:28:09 2007 New Revision: 571208 URL: http://svn.apache.org/viewvc?rev=571208&view=rev Log: DERBY-3034 Address testing todo items in CollationTest.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/SQLUtilities.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java?rev=571208&r1=571207&r2=571208&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java Thu Aug 30 08:28:09 2007 @@ -35,7 +35,6 @@ import org.apache.derbyTesting.junit.SQLUtilities; import org.apache.derbyTesting.junit.TestConfiguration; -import org.apache.derbyTesting.functionTests.tests.lang.CastingTest; public class CaseExpressionTest extends BaseJDBCTestCase { @@ -79,6 +78,8 @@ "2000-01-01 15:30:20.0"}, /*BLOB(1k)*/ {null,null,null,null}, }; + + // Results if the Case Expression evaluates to a NULL value : // @@ -241,6 +242,7 @@ */ protected void decorateSQL(Statement s) throws SQLException { SQLUtilities.createAndPopulateAllDataTypesTable(s); + } }; } @@ -258,7 +260,7 @@ int row; for (colType = 0; - colType < CastingTest.SQLTypes.length; + colType < SQLUtilities.SQLTypes.length; colType++) { rs = st.executeQuery( @@ -267,6 +269,7 @@ caseExprEnd); row = 0; + while (rs.next()) { String val = rs.getString(1); assertEquals(expRS[colType][row], val); @@ -274,5 +277,9 @@ } rs.close(); } + } + + + } Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java?rev=571208&r1=571207&r2=571208&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java Thu Aug 30 08:28:09 2007 @@ -30,6 +30,7 @@ import org.apache.derbyTesting.junit.BaseJDBCTestCase; import org.apache.derbyTesting.junit.JDBC; +import org.apache.derbyTesting.junit.SQLUtilities; import org.apache.derbyTesting.junit.TestConfiguration; /** @@ -71,28 +72,6 @@ public static int TIMESTAMP_OFFSET = 15; public static int BLOB_OFFSET = 16; - // Note: This array is accessed in lang.NullIfTest - public static String[] SQLTypes = - { - "SMALLINT", - "INTEGER", - "BIGINT", - "DECIMAL(10,5)", - "REAL", - "DOUBLE", - "CHAR(60)", - "VARCHAR(60)", - "LONG VARCHAR", - "CHAR(60) FOR BIT DATA", - "VARCHAR(60) FOR BIT DATA", - "LONG VARCHAR FOR BIT DATA", - "CLOB(1k)", - "DATE", - "TIME", - "TIMESTAMP", - "BLOB(1k)", - }; - public static int[] jdbcTypes = { java.sql.Types.SMALLINT, java.sql.Types.INTEGER, @@ -294,8 +273,8 @@ protected void setUp() throws SQLException { Statement scb = createStatement(); - for (int type = 0; type < SQLTypes.length; type++) { - String typeName = SQLTypes[type]; + for (int type = 0; type < SQLUtilities.SQLTypes.length; type++) { + String typeName = SQLUtilities.SQLTypes[type]; String tableName = getTableName(type); String createSQL = "create table " + tableName + " (c " + typeName @@ -307,7 +286,7 @@ // * testing literal inserts for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++) - for (int type = 0; type < SQLTypes.length; type++) { + for (int type = 0; type < SQLUtilities.SQLTypes.length; type++) { try { String tableName = getTableName(type); @@ -331,9 +310,9 @@ // Try to insert each sourceType into the targetType table for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++) - for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) { - String sourceTypeName = SQLTypes[sourceType]; - for (int targetType = 0; targetType < SQLTypes.length; targetType++) { + for (int sourceType = 0; sourceType < SQLUtilities.SQLTypes.length; sourceType++) { + String sourceTypeName = SQLUtilities.SQLTypes[sourceType]; + for (int targetType = 0; targetType < SQLUtilities.SQLTypes.length; targetType++) { try { String targetTableName = getTableName(targetType); @@ -369,14 +348,14 @@ Statement s = createStatement(); // Try Casts from each type to the - for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) { + for (int sourceType = 0; sourceType < SQLUtilities.SQLTypes.length; sourceType++) { - String sourceTypeName = SQLTypes[sourceType]; + String sourceTypeName = SQLUtilities.SQLTypes[sourceType]; //System.out.print("/*" + sourceTypeName + "*/ {"); for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++) - for (int targetType = 0; targetType < SQLTypes.length; targetType++) { + for (int targetType = 0; targetType < SQLUtilities.SQLTypes.length; targetType++) { try { - String targetTypeName = SQLTypes[targetType]; + String targetTypeName = SQLUtilities.SQLTypes[targetType]; // For casts from Character types use strings that can // be converted to the targetType. @@ -385,8 +364,8 @@ String query = "VALUES CAST (CAST (" + convertString + " AS " - + SQLTypes[sourceType] + ") AS " - + SQLTypes[targetType] + " )"; + + SQLUtilities.SQLTypes[sourceType] + ") AS " + + SQLUtilities.SQLTypes[targetType] + " )"; ResultSet rs = s.executeQuery(query); rs.next(); String val = rs.getString(1); @@ -433,7 +412,7 @@ // Comparison's using literals - for (int type = 0; type < SQLTypes.length; type++) { + for (int type = 0; type < SQLUtilities.SQLTypes.length; type++) { try { int dataOffset = 1; // don't use null values String tableName = getTableName(type); @@ -454,9 +433,9 @@ // Try to compare each sourceType with the targetType for (int dataOffset = 0; dataOffset < SQLData[0].length; dataOffset++) - for (int sourceType = 0; sourceType < SQLTypes.length; sourceType++) { - String sourceTypeName = SQLTypes[sourceType]; - for (int targetType = 0; targetType < SQLTypes.length; targetType++) { + for (int sourceType = 0; sourceType < SQLUtilities.SQLTypes.length; sourceType++) { + String sourceTypeName = SQLUtilities.SQLTypes[sourceType]; + for (int targetType = 0; targetType < SQLUtilities.SQLTypes.length; targetType++) { try { String targetTableName = getTableName(targetType); @@ -500,8 +479,8 @@ protected void tearDown() throws SQLException, Exception { Statement scb = createStatement(); - for (int type = 0; type < SQLTypes.length; type++) { - String typeName = SQLTypes[type]; + for (int type = 0; type < SQLUtilities.SQLTypes.length; type++) { + String typeName = SQLUtilities.SQLTypes[type]; String tableName = getTableName(type); String dropSQL = "drop table " + tableName; @@ -536,7 +515,7 @@ */ private static String getShortTypeName(int type) { - String typeName = SQLTypes[type]; + String typeName = SQLUtilities.SQLTypes[type]; String shortName = typeName; int parenIndex = typeName.indexOf('('); if (parenIndex >= 0) { @@ -607,16 +586,16 @@ } private static void checkSupportedCast(int sourceType, int targetType) { - String description = " Cast from " + SQLTypes[sourceType] + " to " - + SQLTypes[targetType]; + String description = " Cast from " + SQLUtilities.SQLTypes[sourceType] + " to " + + SQLUtilities.SQLTypes[targetType]; if (!isSupportedCast(sourceType, targetType)) fail(description + "should not succeed"); } private static void checkSupportedAssignment(int sourceType, int targetType) { - String description = " Assignment from " + SQLTypes[sourceType] - + " to " + SQLTypes[targetType]; + String description = " Assignment from " + SQLUtilities.SQLTypes[sourceType] + + " to " + SQLUtilities.SQLTypes[targetType]; if (!isSupportedAssignment(sourceType, targetType)) fail(description + "should not succeed"); @@ -624,8 +603,8 @@ } private static void checkSupportedComparison(int sourceType, int targetType) { - String description = " Comparison of " + SQLTypes[sourceType] + " to " - + SQLTypes[targetType]; + String description = " Comparison of " + SQLUtilities.SQLTypes[sourceType] + " to " + + SQLUtilities.SQLTypes[targetType]; if (!isSupportedComparison(sourceType, targetType)) fail("FAIL: unsupported comparison:" + description); 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?rev=571208&r1=571207&r2=571208&view=diff ============================================================================== --- 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 Thu Aug 30 08:28:09 2007 @@ -47,31 +47,6 @@ public class CollationTest extends BaseJDBCTestCase { - /* - * ToDo test cases - * 1)Use a parameter as cast operand and cast that to character type. The - * resultant type should get it's collation from the compilation schema - * 2)Test conditional if (NULLIF and CASE) with different datatypes to see - * how casting works. The compile node for this SQL construct seems to be - * dealing with lot of casting code (ConditionalNode) - * 3)When doing concatenation testing, check what happens if concatantion - * is between non-char types. This is because ConcatenationOperatorNode - * in compile package has following comment "If either the left or right - * operands are non-string, non-bit types, then we generate an implicit - * cast to VARCHAR." - * 4)Do testing with upper and lower - * 5)It looks like node for LIKE ESCAPE which is LikeEscapeOperatorNode - * also uses quite a bit of casting. Should include test for LIKE ESCAPE - * which will trigger the casting. - * 6)Binary arithmetic operators do casting if one of the operands is - * string and other is numeric. Test that combination - * 7)Looks like import utility does casting (in ColumnInfo class). See - * if any testing is required for that. - * 8)Do testing with UNION and use the results of UNION in collation - * comparison (if there is something like that possible. I didn't put too - * much thought into it but wanted to list here so we can do the required - * testing if needed). - */ public CollationTest(String name) { super(name); } @@ -1065,15 +1040,58 @@ 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"); - assertEquals(1,JDBC.assertDrainResults(rs)); + JDBC.assertDrainResults(rs,1); + assertStatementError("42818",s,"select TABLENAME FROM SYS.SYSTABLES WHERE UPPER(CURRENT_DATE) = TABLENAME"); - s.close(); + + + //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(); } private void setUpTable(Statement s) throws SQLException { @@ -1181,7 +1199,6 @@ suite.addTest(BatchUpdateTest.embeddedSuite()); suite.addTest(GroupByExpressionTest.suite()); suite.addTest(UpdatableResultSetTest.suite()); - return Decorator.territoryCollatedDatabase(suite, locale); } Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java?rev=571208&r1=571207&r2=571208&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java Thu Aug 30 08:28:09 2007 @@ -135,11 +135,11 @@ */ public void testAllDatatypesCombinations() throws SQLException { Statement s = createStatement(); - for (int firstColumnType = 0; firstColumnType < CastingTest.SQLTypes.length; firstColumnType++) { + for (int firstColumnType = 0; firstColumnType < SQLUtilities.SQLTypes.length; firstColumnType++) { StringBuffer nullIfString = new StringBuffer("SELECT NULLIF(" + SQLUtilities.allDataTypesColumnNames[firstColumnType]); - for (int secondColumnType = 0; secondColumnType < CastingTest.SQLTypes.length; secondColumnType++) { + for (int secondColumnType = 0; secondColumnType < SQLUtilities.SQLTypes.length; secondColumnType++) { int row = 0; try { @@ -192,7 +192,7 @@ * @throws SQLException */ public void testParameterForFirstOperandToNullIf() throws SQLException { - for (int secondColumnType = 0; secondColumnType < CastingTest.SQLTypes.length; secondColumnType++) { + for (int secondColumnType = 0; secondColumnType < SQLUtilities.SQLTypes.length; secondColumnType++) { String nullIfString = new String("SELECT NULLIF(?," + SQLUtilities.allDataTypesColumnNames[secondColumnType] Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/SQLUtilities.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/SQLUtilities.java?rev=571208&r1=571207&r2=571208&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/SQLUtilities.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/SQLUtilities.java Thu Aug 30 08:28:09 2007 @@ -22,7 +22,6 @@ import java.sql.SQLException; import java.sql.Statement; -import org.apache.derbyTesting.functionTests.tests.lang.CastingTest; public class SQLUtilities { @@ -87,25 +86,27 @@ StringBuffer createSQL = new StringBuffer( "create table AllDataTypesTable ("); - for (int type = 0; type < CastingTest.SQLTypes.length - 1; type++) { - createSQL.append(allDataTypesColumnNames[type] + " " + CastingTest.SQLTypes[type] + for (int type = 0; type < SQLUtilities.SQLTypes.length - 1; type++) { + createSQL.append(allDataTypesColumnNames[type] + " " + SQLUtilities.SQLTypes[type] + ","); } - createSQL.append(allDataTypesColumnNames[CastingTest.SQLTypes.length - 1] + " " - + CastingTest.SQLTypes[CastingTest.SQLTypes.length - 1] + ")"); + createSQL.append(allDataTypesColumnNames[SQLUtilities.SQLTypes.length - 1] + " " + + SQLUtilities.SQLTypes[SQLUtilities.SQLTypes.length - 1] + ")"); s.executeUpdate(createSQL.toString()); for (int row = 0; row < allDataTypesSQLData[0].length; row++) { createSQL = new StringBuffer( "insert into AllDataTypesTable values("); - for (int type = 0; type < CastingTest.SQLTypes.length - 1; type++) { + for (int type = 0; type < SQLUtilities.SQLTypes.length - 1; type++) { createSQL.append(allDataTypesSQLData[type][row] + ","); } - createSQL.append(allDataTypesSQLData[CastingTest.SQLTypes.length - 1][row] + ")"); + createSQL.append(allDataTypesSQLData[SQLUtilities.SQLTypes.length - 1][row] + ")"); s.executeUpdate(createSQL.toString()); } } + + /** * @@ -140,5 +141,27 @@ s2.close(); return parser; } + + // Note: This array is accessed in lang.NullIfTest + public static String[] SQLTypes = + { + "SMALLINT", + "INTEGER", + "BIGINT", + "DECIMAL(10,5)", + "REAL", + "DOUBLE", + "CHAR(60)", + "VARCHAR(60)", + "LONG VARCHAR", + "CHAR(60) FOR BIT DATA", + "VARCHAR(60) FOR BIT DATA", + "LONG VARCHAR FOR BIT DATA", + "CLOB(1k)", + "DATE", + "TIME", + "TIMESTAMP", + "BLOB(1k)", + }; }