Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r655947 -
 /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Date: Tue, 13 May 2008 16:52:52 -0000
To: derby-commits@db.apache.org
From: bpendleton@apache.org
Message-Id: <20080513165252.5A29623889B9@eris.apache.org>

Author: bpendleton
Date: Tue May 13 09:52:51 2008
New Revision: 655947

URL: http://svn.apache.org/viewvc?rev=655947&view=rev
Log:
DERBY-2457: Use of column aliases in GROUP BY / HAVING clauses may fail

Some use of column aliases in group by / having clauses can cause queries 
to fail with error 42X04. The queries can sometimes be made to work by 
also aliasing the table or rewriting the query to use a subselect.

After analyzing the script, and studying the standard, we came to feel
that Derby was behaving correctly according to the standard. While
column aliases are valid in the ORDER BY clause, they are not valid
in the GROUP BY and HAVING clauses. Instead, Derby currently correctly
enforces the standard's requirement that the underlying column name
be used in these clauses.

This change updates the Derby GroupByTest.java test program to contain
the examples from the reproduction script, demonstrating that Derby's
behavior is correct, and adding to the body of GROUP BY test cases.


Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java

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=655947&r1=655946&r2=655947&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 May 13 09:52:51 2008
@@ -95,6 +95,13 @@
         st.executeUpdate("insert into d2085 values (1,1,1,1), (1,2,3,4), " +
                 "(4,3,2,1), (2,2,2,2)");
 
+        st.executeUpdate("create table d2457_o (name varchar(20), ord int)");
+        st.executeUpdate("create table d2457_a (ord int, amount int)");
+        st.executeUpdate("insert into d2457_o values ('John', 1)," +
+                " ('Jerry', 2), ('Jerry', 3), ('John', 4), ('John', 5)");
+        st.executeUpdate("insert into d2457_a values (1, 12), (2, 23), " +
+                "(3, 34), (4, 45), (5, 56)");
+
         // create an all types tables
         
         st.executeUpdate(
@@ -1587,5 +1594,102 @@
         assertStatementError("42Y36", s,
             "select a,b from d2085 group by a,b order by c*2");
     }
+
+    /**
+      * DERBY-2457: Derby does not support column aliases in the
+      * GROUP BY and HAVING clauses.
+      */
+    public void testColumnAliasInGroupByAndHaving() throws SQLException
+    {
+        Statement s = createStatement();
+        // 1) Using the underlying column names works fine, with or
+        // without a table alias:
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select name, count(ord) from d2457_o " +
+                    " group by name having count(ord) > 2"),
+            new String[][] {  {"John","3"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select name as col1, count(ord) as col2 " +
+                    " from d2457_o group by name having count(ord) > 2"),
+            new String[][] {  {"John","3"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select name as col1, count(ord) as col2 " +
+                    " from d2457_o ordertable group by name " +
+                    " having count(ord) > 2"),
+            new String[][] {  {"John","3"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select ordertable.name as col1, " +
+                    " count(ord) as col2 from d2457_o ordertable " +
+                    " group by name having count(ord) > 2"),
+            new String[][] {  {"John","3"} } );
+        // 2) References to column aliases in GROUP BY and HAVING are
+        // rejected with an error message:
+        assertStatementError("42X04", s,
+                "select name as col1, count(ord) as col2 from d2457_o " +
+                " group by name having col2 > 2");
+        assertStatementError("42X04", s,
+                "select name as col1, count(ord) as col2 from d2457_o " +
+                " group by col1 having col2 > 2");
+        assertStatementError("42X04", s,
+                "select name as col1, count(ord) as col2 from d2457_o " +
+                " group by col1 having count(ord) > 2");
+        assertStatementError("42X04", s,
+                "select name as col1, sum(amount) as col2 " +
+                " from d2457_o, d2457_a where d2457_o.ord = d2457_a.ord " +
+                " group by col1 having col2 > 2");
+        assertStatementError("42X04", s,
+                "select name as col1, sum(amount) as col2 " +
+                " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                " group by col1 having col2 > 2");
+        assertStatementError("42X04", s,
+                "select name as col1, sum(amount) as col2 " +
+                " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                " group by col1 having sum(amount) > 2");
+        assertStatementError("42X04", s,
+                "select name as col1, sum(amount) as col2 " +
+                " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                " group by col1 having col2 > 2");
+        assertStatementError("42X04", s,
+                "select * from (select t1.name as col, sum(amount) " +
+                " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                " group by col) as t12(col1, col2) where col2 > 2");
+        // 3) Demonstrate that column aliasing works correctly when the
+        // GROUP BY is packaged as a subquery:
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select * from " +
+                    "(select name, count(ord) from d2457_o ordertable " +
+                    " group by ordertable.name) as ordertable(col1, col2) " +
+                    " where col2 > 2"),
+            new String[][] {  {"John","3"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select * from " +
+                    "(select name as col, sum(amount) " +
+                    " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                    " group by name) as t12(col1, col2) where col2 > 2"),
+            new String[][] {  {"Jerry", "57"}, {"John","113"} } );
+        // 4) Demonatrate that table aliases can be used in GROUP BY and
+        // HAVING clauses
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select t1.name as col1, " +
+                    " sum(t2.amount) as col2 from d2457_o t1, d2457_a t2 " +
+                    " where t1.ord = t2.ord group by t1.name " +
+                    " having sum(t2.amount) > 2"),
+            new String[][] {  {"Jerry", "57"}, {"John","113"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select name as col1, sum(amount) as col2 " +
+                    " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                    " group by name having sum(amount) > 2"),
+            new String[][] {  {"Jerry", "57"}, {"John","113"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select t1.name as col1, sum(amount) as col2 " +
+                    " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                    " group by name having sum(amount) > 2"),
+            new String[][] {  {"Jerry", "57"}, {"John","113"} } );
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery("select name as col1, sum(t2.amount) as col2 " +
+                    " from d2457_o t1, d2457_a t2 where t1.ord = t2.ord " +
+                    " group by name having sum(amount) > 2"),
+            new String[][] {  {"Jerry", "57"}, {"John","113"} } );
+    }
 }