db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r653988 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Wed, 07 May 2008 04:19:45 GMT
Author: bpendleton
Date: Tue May  6 21:19:40 2008
New Revision: 653988

URL: http://svn.apache.org/viewvc?rev=653988&view=rev
Log:
DERBY-2085: Misleading error message for non-matching ORDER BY clause

This change modifies the SQL compiler to change the error message that
is displayed for the query:

   SELECT i FROM t GROUP BY i ORDER BY j

After this patch, Derby will once again issue the error message that
it issued in release 10.1 and earlier:

   ERROR 42Y36: Column reference 'J' is invalid. For a SELECT list
   with a GROUP BY, the list may only contain grouping columns and
   valid aggregate expressions.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.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/VerifyAggregateExpressionsVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java?rev=653988&r1=653987&r2=653988&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java
Tue May  6 21:19:40 2008
@@ -82,7 +82,7 @@
 
 			if (groupByList.findGroupingColumn(cr) == null)
 			{
-				throw StandardException.newException(SQLState.LANG_INVALID_GROUPED_SELECT_LIST);
+				throw StandardException.newException(SQLState.LANG_INVALID_COL_REF_GROUPED_SELECT_LIST,
cr.getSQLColumnName());
 			}
 		} 
 		

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java?rev=653988&r1=653987&r2=653988&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java
Tue May  6 21:19:40 2008
@@ -229,19 +229,19 @@
                         {112, 2},
                         {114, 2}});
         assertCompileError(
-                "42Y30", "select c1+c2, sum(c3) from test group by c1");
+                "42Y36", "select c1+c2, sum(c3) from test group by c1");
         assertCompileError(
-                "42Y30", "select c1,c2, sum(c3) from test group by c1+c2,c1");
+                "42Y36", "select c1,c2, sum(c3) from test group by c1+c2,c1");
         assertCompileError(
-                "42Y30", "select c1+c2, sum(c3) from test group by 1");
+                "42Y36", "select c1+c2, sum(c3) from test group by 1");
         assertCompileError(
             "42X04", "select c1+c2 as expr, sum(c3) from test group by expr");
         assertCompileError(
             "42X04", "select c1 as c1a, c2, sum(c3) from test group by c1a,c2");
         assertCompileError(
-                "42Y30", "select c1 as c2, sum(c3) from test group by c2");
+                "42Y36", "select c1 as c2, sum(c3) from test group by c2");
         assertCompileError(
-                "42Y30", "select c1+(c2+c3), sum(c3) from test group by c3, (c1+c2)");
+                "42Y36", "select c1+(c2+c3), sum(c3) from test group by c3, (c1+c2)");
     }
     
     public void testSubSelect() throws Exception
@@ -483,48 +483,48 @@
                 "42Y30", "select count(*) from test group by r()");
         // invalid grouping expression.
         assertCompileError(
-                "42Y30", "select c1+1, count(*) from test group by c1+2");
+                "42Y36", "select c1+1, count(*) from test group by c1+2");
         
         // again invalid grouping expression because cast type is different.
         assertCompileError(
-                "42Y30", "select (cast (c as char(2))), count(*) " +
+                "42Y36", "select (cast (c as char(2))), count(*) " +
                 " from alltypes group by (cast (c as char(3)))");
 
         // same column name, same table but different tablenumber in the query
         assertCompileError(
-                "42Y30", 
+                "42Y36", 
                 "select t1.c1, count(*) from test t1, test t2 " + 
                 " group by t2.c1");
         // ternary operator, not equivalent test.
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select substr(c, 3, 4) from alltypes group by substr(v, 3, 4)");
 
         // DERBY-2008
         // invalid grouping expression 
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select substr(c1, 3, 4) from t1 group by substr(c1, 3)");
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select substr(c1, 3) from t1 group by substr(c1, 3, 4)");
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select locate(c1, 'abc') from t2 group by locate(c1, 'abc',3)");
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc')");
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc',3)");
 
         // DERBY-2014
         // invalid grouping expression
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select nullif(c1,c2) from t3 group by nullif(c2,c1)");
         assertCompileError(
-                "42Y30",
+                "42Y36",
                 "select nullif(c1,100) from t3 group by nullif(c1,200)");
 
         // aggregates in group by list.

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=653988&r1=653987&r2=653988&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  6 21:19:40 2008
@@ -91,6 +91,10 @@
         st.executeUpdate("insert into d3613 values (1,2,1,2), (1,2,3,4), " +
                 "(1,3,5,6), (2,2,2,2)");
 
+        st.executeUpdate("create table d2085 (a int, b int, c int, d int)");
+        st.executeUpdate("insert into d2085 values (1,1,1,1), (1,2,3,4), " +
+                "(4,3,2,1), (2,2,2,2)");
+
         // create an all types tables
         
         st.executeUpdate(
@@ -234,7 +238,7 @@
         // group by constant. should compile but fail because it 
         // is not a valid grouping expression.
         
-        assertStatementError("42Y30", st,
+        assertStatementError("42Y36", st,
             "select * from t1 group by 1");
         
         // column in group by list not in from list
@@ -244,13 +248,13 @@
         
         // column in group by list not in select list
         
-        assertStatementError("42Y30", st,
+        assertStatementError("42Y36", st,
             "select a as b from t1 group by b");
         
-        assertStatementError("42Y30", st,
+        assertStatementError("42Y36", st,
             " select a from t1 group by b");
         
-        assertStatementError("42Y30", st,
+        assertStatementError("42Y36", st,
             " select a, char(b) from t1 group by a");
         
         // cursor with group by is not updatable
@@ -1541,11 +1545,11 @@
         JDBC.assertUnorderedResultSet(rs,
                 new String[][] {{"1","7"},{"2","2"}});
         // A few error cases:
-        assertStatementError("42Y30", s,
+        assertStatementError("42Y36", s,
             "select distinct a,b from d3613 group by a");
-        assertStatementError("42Y30", s,
+        assertStatementError("42Y36", s,
             "select distinct a,b from d3613 group by a,c");
-        assertStatementError("42Y30", s,
+        assertStatementError("42Y36", s,
             "select distinct a,b,sum(b) from d3613 group by a");
         
         // A few queries from other parts of this suite, with DISTINCT added:
@@ -1569,5 +1573,19 @@
                 " select distinct t.dt from t group by i, dt, b order by dt"),
             new String [][] { {"1992-01-01"}, {"1992-09-09"}, {null} });
     }
+    /**
+      * DERBY-2085 check message on order by of non-grouped column
+      */
+    public void testOrderByNonGroupedColumn() throws SQLException
+    {
+        Statement s = createStatement();
+        ResultSet rs;
+        assertStatementError("42Y36", s,
+            "select a from d2085 group by a order by b");
+        assertStatementError("42Y36", s,
+            "select a from d2085 group by a,b order by c");
+        assertStatementError("42Y36", s,
+            "select a,b from d2085 group by a,b order by c*2");
+    }
 }
 



Mime
View raw message