db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r678668 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ResultColumnList.java engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Date Tue, 22 Jul 2008 08:03:18 GMT
Author: kahatlen
Date: Tue Jul 22 01:03:18 2008
New Revision: 678668

URL: http://svn.apache.org/viewvc?rev=678668&view=rev
Log:
DERBY-3764: Union Query fail on Derby 10.4.1.3

A SetOperatorNode inherits the result column list from its left
operand. This patch makes sure that all generated grouping columns are
removed from the SetOperatorNode's column list to prevent it from
seeing extra columns that cause confusion when the set operation is
performed.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=678668&r1=678667&r2=678668&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Tue Jul 22 01:03:18 2008
@@ -2216,12 +2216,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 */
@@ -2344,6 +2350,18 @@
 	 */
 	public boolean isExactTypeAndLengthMatch(ResultColumnList otherRCL) throws StandardException
 	{
+
+        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++)
 		{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?rev=678668&r1=678667&r2=678668&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
Tue Jul 22 01:03:18 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=678668&r1=678667&r2=678668&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Tue Jul 22 01:03:18 2008
@@ -26,6 +26,13 @@
 import java.sql.Statement;
 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;
 
@@ -1760,20 +1767,313 @@
     }
 
     /**
-     * Test that GROUP BY can be used in the sub-queries of a UNION.
-     * DERBY-3764.
+     * Test that GROUP BY can be used in the sub-expressions of set operations
+     * (DERBY-3764).
      */
-    public void testUnionAndGroupBy() throws SQLException {
-        PreparedStatement ps1 =
-            prepareStatement("select sum(a) from yy group by a union values 1");
-        // The bug is not completely fixed, so executing the statement fail
-        // JDBC.assertDrainResults(ps1.executeQuery());
-
-        PreparedStatement ps2 =
-            prepareStatement("select sum(a) from yy group by a union " +
-                             "select sum(a) from yy group by a");
-        // The bug is not completely fixed, so executing the statement fail
-        // JDBC.assertDrainResults(ps2.executeQuery());
+    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;
     }
 }
 



Mime
View raw message