You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2008/05/07 06:19:45 UTC
svn commit: r653988 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
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");
+ }
}