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 fu...@apache.org on 2006/09/28 09:39:14 UTC
svn commit: r450736 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master:
j9_13/groupBy.out j9_22/groupBy.out
Author: fuzzylogic
Date: Thu Sep 28 00:39:13 2006
New Revision: 450736
URL: http://svn.apache.org/viewvc?view=rev&rev=450736
Log:
DERBY-1841: Update lang/groupBy.sql masters for j9.
Committed for Myrna Van Lunteren <m....@gmail.com>
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out?view=diff&rev=450736&r1=450735&r2=450736
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/groupBy.out Thu Sep 28 00:39:13 2006
@@ -5,24 +5,20 @@
0 rows inserted/updated/deleted
ij> insert into t2 values (1,1,1), (2,2,2);
2 rows inserted/updated/deleted
-ij> -- group by position
+ij> -- group by constant. should compile but fail because
+-- it is not a valid grouping expression.
select * from t1 group by 1;
-ERROR 42X01: Syntax error: Encountered "1" at line 2, column 27.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> -- column in group by list not in from list
select a as d from t1 group by d;
ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table.
ij> -- column in group by list not in select list
select a as b from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> select a from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> select a, char(b) from t1 group by a;
-ERROR 42Y36: Column reference 'B' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
-ij> -- columns in group by list must be unique
-select a, b from t1 group by a, a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
-ij> select a, b from t1 group by a, t1.a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> -- cursor with group by is not updatable
get cursor c1 as 'select a from t1 group by a for update';
ERROR 42Y90: FOR UPDATE is not permitted in this type of statement.
@@ -31,7 +27,7 @@
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- correlation on outer table
select t2.a, (select b from t1 where t1.b = t2.b) from t1 t2 group by t2.a;
-ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> -- having clause
-- cannot contain column references which are not grouping columns
select a from t1 group by a having c = 1;
@@ -40,7 +36,7 @@
ERROR 42X04: Column 'B.O' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.O' is not a column in the target table.
ij> -- ?s in group by
select a from t1 group by ?;
-ERROR 42X01: Syntax error: Encountered "?" at line 2, column 27.
+ERROR 42X01: Syntax error: ?.
ij> -- group by on long varchar type
create table unmapped(c1 long varchar);
0 rows inserted/updated/deleted
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out?view=diff&rev=450736&r1=450735&r2=450736
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/groupBy.out Thu Sep 28 00:39:13 2006
@@ -5,24 +5,20 @@
0 rows inserted/updated/deleted
ij> insert into t2 values (1,1,1), (2,2,2);
2 rows inserted/updated/deleted
-ij> -- group by position
+ij> -- group by constant. should compile but fail because
+-- it is not a valid grouping expression.
select * from t1 group by 1;
-ERROR 42X01: Syntax error: Encountered "1" at line 2, column 27.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> -- column in group by list not in from list
select a as d from t1 group by d;
ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table.
ij> -- column in group by list not in select list
select a as b from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> select a from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> select a, char(b) from t1 group by a;
-ERROR 42Y36: Column reference 'B' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
-ij> -- columns in group by list must be unique
-select a, b from t1 group by a, a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
-ij> select a, b from t1 group by a, t1.a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> -- cursor with group by is not updatable
get cursor c1 as 'select a from t1 group by a for update';
ERROR 42Y90: FOR UPDATE is not permitted in this type of statement.
@@ -31,7 +27,7 @@
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- correlation on outer table
select t2.a, (select b from t1 where t1.b = t2.b) from t1 t2 group by t2.a;
-ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.
ij> -- having clause
-- cannot contain column references which are not grouping columns
select a from t1 group by a having c = 1;
@@ -40,7 +36,7 @@
ERROR 42X04: Column 'B.O' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'B.O' is not a column in the target table.
ij> -- ?s in group by
select a from t1 group by ?;
-ERROR 42X01: Syntax error: Encountered "?" at line 2, column 27.
+ERROR 42X01: Syntax error: ?.
ij> -- group by on long varchar type
create table unmapped(c1 long varchar);
0 rows inserted/updated/deleted