From derby-commits-return-10562-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Wed Jul 23 09:04:27 2008 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 7557 invoked from network); 23 Jul 2008 09:04:27 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 23 Jul 2008 09:04:27 -0000 Received: (qmail 54753 invoked by uid 500); 23 Jul 2008 09:04:26 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 54731 invoked by uid 500); 23 Jul 2008 09:04:26 -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 54722 invoked by uid 99); 23 Jul 2008 09:04:26 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 Jul 2008 02:04:26 -0700 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 Jul 2008 09:03:40 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 43FC0238899E; Wed, 23 Jul 2008 02:04:06 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r679037 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Wed, 23 Jul 2008 09:04:05 -0000 To: derby-commits@db.apache.org From: kahatlen@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080723090406.43FC0238899E@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kahatlen Date: Wed Jul 23 02:04:04 2008 New Revision: 679037 URL: http://svn.apache.org/viewvc?rev=679037&view=rev Log: DERBY-3764: Union Query fail on Derby 10.4.1.3 Merged fix from trunk (revisions 676819 and 678668). Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=679037&r1=679036&r2=679037&view=diff ============================================================================== --- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original) +++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Wed Jul 23 02:04:04 2008 @@ -2232,12 +2232,18 @@ if (visibleSize() != otherRCL.visibleSize()) { SanityManager.THROWASSERT( - "size() = (" + - size() + - ") is expected to equal otherRCL.size (" + - otherRCL.size() + + "visibleSize() = (" + + visibleSize() + + ") is expected to equal otherRCL.visibleSize (" + + otherRCL.visibleSize() + ")"); } + + // Generated grouping columns should have been removed for the RCL + // of a SetOperatorNode, so that size and visible size are equal + // (DERBY-3764). + SanityManager.ASSERT(size() == visibleSize(), + "size() and visibleSize() should be equal"); } /* Make a dummy TableName to be shared by all new CRs */ @@ -2249,7 +2255,7 @@ ContextManager cm = getContextManager(); - int size = size(); + int size = visibleSize(); for (int index = 0; index < size; index++) { boolean nullableResult; @@ -2360,7 +2366,19 @@ */ public boolean isExactTypeAndLengthMatch(ResultColumnList otherRCL) throws StandardException { - int size = size(); + + if (SanityManager.DEBUG) { + // The visible size of the two RCLs must be equal. + SanityManager.ASSERT(visibleSize() == otherRCL.visibleSize(), + "visibleSize() should match"); + // The generated grouping columns should have been removed from the + // RCL of the SetOperatorNode, so size and visible size should be + // equal (DERBY-3764). + SanityManager.ASSERT(size() == visibleSize(), + "size() and visibleSize() should match"); + } + + int size = visibleSize(); for (int index = 0; index < size; index++) { ResultColumn thisRC = (ResultColumn) elementAt(index); Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?rev=679037&r1=679036&r2=679037&view=diff ============================================================================== --- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java (original) +++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java Wed Jul 23 02:04:04 2008 @@ -625,6 +625,10 @@ */ resultColumns = leftResultSet.getResultColumns().copyListAndObjects(); + // The generated grouping columns of the left result set should not be + // part of the result from the set operation (DERBY-3764). + resultColumns.removeGeneratedGroupingColumns(); + /* Create new expressions with the dominant types after verifying * union compatibility between left and right sides. */ Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=679037&r1=679036&r2=679037&view=diff ============================================================================== --- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original) +++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Wed Jul 23 02:04:04 2008 @@ -24,6 +24,14 @@ import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; +import java.sql.PreparedStatement; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collection; +import java.util.HashSet; +import java.util.Iterator; +import java.util.List; import junit.framework.Test; import junit.framework.TestSuite; @@ -231,5 +239,315 @@ } + + /** + * Test that GROUP BY can be used in the sub-expressions of set operations + * (DERBY-3764). + */ + public void testSetOperationsAndGroupBy() throws SQLException { + // turn off auto-commit to clean up test data automatically + getConnection().setAutoCommit(false); + + Statement s = createStatement(); + + s.execute("CREATE TABLE D3764A (A1 INTEGER, A2 VARCHAR(10))"); + + int[] valuesA = { 1, 2, 3, 4, 5, 6, 5, 3, 1 }; + + PreparedStatement insertA = + prepareStatement("INSERT INTO D3764A (A1) VALUES (?)"); + + for (int i = 0; i < valuesA.length; i++) { + insertA.setInt(1, valuesA[i]); + insertA.executeUpdate(); + } + + s.execute("CREATE TABLE D3764B (B1 INTEGER, B2 VARCHAR(10))"); + + int[] valuesB = { 1, 2, 3, 3, 7, 9 }; + + PreparedStatement insertB = + prepareStatement("INSERT INTO D3764B (B1) VALUES (?)"); + + for (int i = 0; i < valuesB.length; i++) { + insertB.setInt(1, valuesB[i]); + insertB.executeUpdate(); + } + + // Define some queries with one result column and a varying number of + // grouping columns. + String[] singleColumnQueries = { + "SELECT A1 FROM D3764A", + "SELECT COUNT(A1) FROM D3764A", + "SELECT COUNT(A1) FROM D3764A GROUP BY A1", + "SELECT COUNT(A1) FROM D3764A GROUP BY A2", + "SELECT COUNT(A1) FROM D3764A GROUP BY A1, A2", + "SELECT B1 FROM D3764B", + "SELECT COUNT(B1) FROM D3764B", + "SELECT COUNT(B1) FROM D3764B GROUP BY B1", + "SELECT COUNT(B1) FROM D3764B GROUP BY B2", + "SELECT COUNT(B1) FROM D3764B GROUP BY B1, B2", + "VALUES 1, 2, 3, 4", + }; + + // The expected results from the queries above. + String[][][] singleColumnExpected = { + { {"1"}, {"2"}, {"3"}, {"4"}, {"5"}, {"6"}, {"5"}, {"3"}, {"1"} }, + { { Integer.toString(valuesA.length) } }, + { {"2"}, {"1"}, {"2"}, {"1"}, {"2"}, {"1"} }, + { { Integer.toString(valuesA.length) } }, + { {"2"}, {"1"}, {"2"}, {"1"}, {"2"}, {"1"} }, + { {"1"}, {"2"}, {"3"}, {"3"}, {"7"}, {"9"} }, + { { Integer.toString(valuesB.length) } }, + { {"1"}, {"1"}, {"2"}, {"1"}, {"1"} }, + { { Integer.toString(valuesB.length) } }, + { {"1"}, {"1"}, {"2"}, {"1"}, {"1"} }, + { {"1"}, {"2"}, {"3"}, {"4"} }, + }; + + // Test all the set operations with all the combinations of the queries + // above. + doAllSetOperations(s, singleColumnQueries, singleColumnExpected); + + // Define some queries with two result columns and a varying number of + // grouping columns. + String[] twoColumnQueries = { + "SELECT A1-1, A1+1 FROM D3764A", + "SELECT COUNT(A1), A1 FROM D3764A GROUP BY A1", + "SELECT COUNT(A1), LENGTH(A2) FROM D3764A GROUP BY A2", + "SELECT COUNT(A1), A1 FROM D3764A GROUP BY A1, A2", + "SELECT B1-1, B1+1 FROM D3764B", + "SELECT COUNT(B1), B1 FROM D3764B GROUP BY B1", + "SELECT COUNT(B1), LENGTH(B2) FROM D3764B GROUP BY B2", + "SELECT COUNT(B1), B1 FROM D3764B GROUP BY B1, B2", + "VALUES (1, 2), (3, 4)", + }; + + // The expected results from the queries above. + String[][][] twoColumnExpected = { + { {"0","2"}, {"1","3"}, {"2","4"}, {"3","5"}, {"4","6"}, + {"5","7"}, {"4","6"}, {"2","4"}, {"0","2"} }, + { {"2","1"}, {"1","2"}, {"2","3"}, {"1","4"}, {"2","5"}, + {"1","6"} }, + { { Integer.toString(valuesA.length), null } }, + { {"2","1"}, {"1","2"}, {"2","3"}, {"1","4"}, {"2","5"}, + {"1","6"} }, + { {"0","2"}, {"1","3"}, {"2","4"}, {"2","4"}, {"6","8"}, + {"8","10"} }, + { {"1","1"}, {"1","2"}, {"2","3"}, {"1","7"}, {"1","9"} }, + { { Integer.toString(valuesB.length), null } }, + { {"1","1"}, {"1","2"}, {"2","3"}, {"1","7"}, {"1","9"} }, + { {"1","2"}, {"3","4"} }, + }; + + // Test all the set operations with all the combinations of the queries + // above. + doAllSetOperations(s, twoColumnQueries, twoColumnExpected); + + // Test that set operations cannot be used on sub-queries with + // different number of columns. + assertSetOpErrors("42X58", s, singleColumnQueries, twoColumnQueries); + assertSetOpErrors("42X58", s, twoColumnQueries, singleColumnQueries); + } + + /** + * Try all set operations (UNION [ALL], EXCEPT [ALL], INTERSECT [ALL]) on + * all combinations of the specified queries. + * + * @param s the statement used to execute the queries + * @param queries the different queries to use, all of which must be union + * compatible + * @param expectedResults the expected results from the different queries + */ + private static void doAllSetOperations(Statement s, String[] queries, + String[][][] expectedResults) + throws SQLException { + + assertEquals(queries.length, expectedResults.length); + + for (int i = 0; i < queries.length; i++) { + final String query1 = queries[i]; + final List rows1 = resultArrayToList(expectedResults[i]); + + for (int j = 0; j < queries.length; j++) { + final String query2 = queries[j]; + final List rows2 = resultArrayToList(expectedResults[j]); + + String query = query1 + " UNION " + query2; + String[][] rows = union(rows1, rows2, false); + JDBC.assertUnorderedResultSet(s.executeQuery(query), rows); + + query = query1 + " UNION ALL " + query2; + rows = union(rows1, rows2, true); + JDBC.assertUnorderedResultSet(s.executeQuery(query), rows); + + query = query1 + " EXCEPT " + query2; + rows = except(rows1, rows2, false); + JDBC.assertUnorderedResultSet(s.executeQuery(query), rows); + + query = query1 + " EXCEPT ALL " + query2; + rows = except(rows1, rows2, true); + JDBC.assertUnorderedResultSet(s.executeQuery(query), rows); + + query = query1 + " INTERSECT " + query2; + rows = intersect(rows1, rows2, false); + JDBC.assertUnorderedResultSet(s.executeQuery(query), rows); + + query = query1 + " INTERSECT ALL " + query2; + rows = intersect(rows1, rows2, true); + JDBC.assertUnorderedResultSet(s.executeQuery(query), rows); + } + } + } + + /** + * Try all set operations with queries from {@code queries1} in the left + * operand and queries from {@code queries2} in the right operand. All the + * set operations are expected to fail with the same SQLState. + * + * @param sqlState the expected SQLState + * @param s the statement used to execute the queries + * @param queries1 queries to use as the left operand + * @param queries2 queries to use as the right operand + */ + private static void assertSetOpErrors(String sqlState, + Statement s, + String[] queries1, + String[] queries2) + throws SQLException { + + final String[] operators = { + " UNION ", " UNION ALL ", " EXCEPT ", " EXCEPT ALL ", + " INTERSECT ", " INTERSECT ALL " + }; + + for (int i = 0; i < queries1.length; i++) { + for (int j = 0; j < queries2.length; j++) { + for (int k = 0; k < operators.length; k++) { + assertStatementError( + sqlState, s, queries1[i] + operators[k] + queries2[j]); + } + } + } + } + + /** + * Find the union between two collections of rows (each row is a list of + * strings). Return the union as an array of string arrays. + * + * @param rows1 the first collection of rows + * @param rows2 the second collection of rows + * @param all whether or not bag semantics (as in UNION ALL) should be used + * instead of set semantics + * @return the union of {@code rows1} and {@code rows2}, as a {@code + * String[][]} + */ + private static String[][] union(Collection rows1, + Collection rows2, + boolean all) { + Collection bagOrSet = newBagOrSet(all); + bagOrSet.addAll(rows1); + bagOrSet.addAll(rows2); + return toResultArray(bagOrSet); + } + + /** + * Find the difference between two collections of rows (each row is a list + * of strings). Return the difference as an array of string arrays. + * + * @param rows1 the first operand to the set difference operator + * @param rows2 the second operand to the set difference operator + * @param all whether or not bag semantics (as in EXCEPT ALL) should be + * used instead of set semantics + * @return the difference between {@code rows1} and {@code rows2}, as a + * {@code String[][]} + */ + private static String[][] except(Collection rows1, + Collection rows2, + boolean all) { + Collection bagOrSet = newBagOrSet(all); + bagOrSet.addAll(rows1); + // could use removeAll() for sets, but need other behaviour for bags + for (Iterator it = rows2.iterator(); it.hasNext(); ) { + bagOrSet.remove(it.next()); + } + return toResultArray(bagOrSet); + } + + /** + * Find the intersection between two collections of rows (each row is a + * list of strings). Return the intersection as an array of string arrays. + * + * @param rows1 the first collection of rows + * @param rows2 the second collection of rows + * @param all whether or not bag semantics (as in INTERSECT ALL) should be + * used instead of set semantics + * @return the intersection between {@code rows1} and {@code rows2}, as a + * {@code String[][]} + */ + private static String[][] intersect(Collection rows1, + Collection rows2, + boolean all) { + Collection bagOrSet = newBagOrSet(all); + List copyOfRows2 = new ArrayList(rows2); + // could use retainAll() for sets, but need other behaviour for bags + for (Iterator it = rows1.iterator(); it.hasNext(); ) { + Object x = it.next(); + if (copyOfRows2.remove(x)) { + // x is present in both of the collections, add it + bagOrSet.add(x); + } + } + return toResultArray(bagOrSet); + } + + /** + * Create a {@code Collection} that can be used as a bag or a set. + * + * @param bag tells whether or not the collection should be a bag + * @return a {@code List} if a bag is requested, or a {@code Set} otherwise + */ + private static Collection newBagOrSet(boolean bag) { + if (bag) { + return new ArrayList(); + } else { + return new HashSet(); + } + } + + /** + * Convert a {@code Collection} of rows to an array of string arrays that + * can be passed as an argument with expected results to + * {@link JDBC#assertUnorderedResultSet(ResultSet,String[][])}. + * + * @param rows a collection of rows, where each row is a list of strings + * @return a {@code String[][]} containing the same values as {@code rows} + */ + private static String[][] toResultArray(Collection rows) { + String[][] results = new String[rows.size()][]; + Iterator it = rows.iterator(); + for (int i = 0; i < results.length; i++) { + List row = (List) it.next(); + results[i] = (String[]) row.toArray(new String[row.size()]); + } + return results; + } + + /** + * Return a list of lists containing the same values as the specified array + * of string arrays. This method can be used to make it easier to perform + * set operations on a two-dimensional array of strings. + * + * @param results a two dimensional array of strings (typically expected + * results from a query} + * @return the values of {@code results} in a list of lists + */ + private static List resultArrayToList(String[][] results) { + ArrayList rows = new ArrayList(results.length); + for (int i = 0; i < results.length; i++) { + rows.add(Arrays.asList(results[i])); + } + return rows; + } }