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 ka...@apache.org on 2007/03/29 14:58:04 UTC
svn commit: r523691 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/aggregate.out tests/lang/GroupByTest.java tests/lang/aggregate.sql
Author: kahatlen
Date: Thu Mar 29 05:58:03 2007
New Revision: 523691
URL: http://svn.apache.org/viewvc?view=rev&rev=523691
Log:
DERBY-2493 (partial) Use unsynchronized collections in BackingStoreHashtable
Updated some tests so that they are not sensitive to the order of the
rows in the returned ResultSet.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/aggregate.sql
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out?view=diff&rev=523691&r1=523690&r2=523691
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out Thu Mar 29 05:58:03 2007
@@ -910,89 +910,4 @@
ERROR 42903: Invalid use of an aggregate function.
ij> drop table tmax;
0 rows inserted/updated/deleted
-ij> --
--- JIRA Bug 280.
---
--- Giving two columns the same name confuses the wacky
--- query rewriting which the parser undertakes for grouped
--- and aggregated queries.
---
-
-create table bug280
-(
- a int,
- b int
-);
-0 rows inserted/updated/deleted
-ij> insert into bug280( a, b )
-values ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 1 ), ( 2, 2 );
-5 rows inserted/updated/deleted
-ij> --
--- This is bug 280. The alias confused the grouping.
--- The second query should return the same results
--- as the first. However, this bug cannot be fixed
--- until we clean up the query rewriting done by
--- the parser (see bug 681). Until then, the second
--- query will raise an exception advising the customer
--- to rewrite the query.
---
-select a, count( a )
-from bug280
-group by a;
-A |2
------------------------
-1 |3
-2 |2
-ij> -- should raise an error
-select a, count( a ) as a
-from bug280
-group by a;
-A |A
------------------------
-1 |3
-2 |2
-ij> -- should return same results as first query (but with extra column)
-select a, count( a ), a
-from bug280
-group by a;
-A |2 |A
------------------------------------
-1 |3 |1
-2 |2 |2
-ij> -- different tables with same column name ok
-select t.t_i, m.t_i from
-(select a, b from bug280 group by a, b) t (t_i, t_dt),
-(select a, b from bug280 group by a, b) m (t_i, t_dt)
-where t.t_i = m.t_i and t.t_dt = m.t_dt
-group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,m.t_i;
-T_I |T_I
------------------------
-1 |1
-1 |1
-1 |1
-2 |2
-2 |2
-ij> -- should be allowed
-select a, a from bug280 group by a;
-A |A
------------------------
-2 |2
-1 |1
-ij> select bug280.a, a from bug280 group by a;
-A |A
------------------------
-2 |2
-1 |1
-ij> select bug280.a, bug280.a from bug280 group by a;
-A |A
------------------------
-2 |2
-1 |1
-ij> select a, bug280.a from bug280 group by a;
-A |A
------------------------
-2 |2
-1 |1
-ij> drop table bug280;
-0 rows inserted/updated/deleted
-ij>
\ No newline at end of file
+ij>
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?view=diff&rev=523691&r1=523690&r2=523691
==============================================================================
--- 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 Thu Mar 29 05:58:03 2007
@@ -48,5 +48,67 @@
s.execute("declare global temporary table session.ztemp ( orderID varchar( 50 ) ) not logged");
JDBC.assertEmpty(s.executeQuery("select orderID from session.ztemp group by orderID"));
}
+
+ /**
+ * DERBY-280: Wrong result from select when aliasing to same name as used
+ * in group by
+ */
+ public void testGroupByWithAliasToSameName() throws SQLException {
+ // disable auto-commit so that no tear-down code is needed
+ getConnection().setAutoCommit(false);
+
+ Statement s = createStatement();
+ s.executeUpdate("create table bug280 (a int, b int)");
+ s.executeUpdate("insert into bug280 (a, b) " +
+ "values (1,1), (1,2), (1,3), (2,1), (2,2)");
+
+ String[][] expected1 = {{"1", "3"}, {"2", "2"}};
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select a, count(a) from bug280 group by a"),
+ expected1);
+ // The second query should return the same results as the first. Would
+ // throw exception before DERBY-681.
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select a, count(a) as a from bug280 group by a"),
+ expected1);
+
+ // should return same results as first query (but with extra column)
+ String[][] expected2 = {{"1", "3", "1"}, {"2", "2", "2"}};
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select a, count(a), a from bug280 group by a"),
+ expected2);
+
+ // different tables with same column name ok
+ String[][] expected3 = {
+ {"1","1"}, {"1","1"}, {"1","1"}, {"2","2"}, {"2","2"} };
+ JDBC.assertFullResultSet(
+ s.executeQuery("select t.t_i, m.t_i from " +
+ "(select a, b from bug280 group by a, b) " +
+ "t (t_i, t_dt), " +
+ "(select a, b from bug280 group by a, b) " +
+ "m (t_i, t_dt) " +
+ "where t.t_i = m.t_i and t.t_dt = m.t_dt " +
+ "group by t.t_i, t.t_dt, m.t_i, m.t_dt " +
+ "order by t.t_i,m.t_i"),
+ expected3);
+
+ // should be allowed
+ String[][] expected4 = { {"1", "1"}, {"2", "2"} };
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select a, a from bug280 group by a"),
+ expected4);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select bug280.a, a from bug280 group by a"),
+ expected4);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select bug280.a, bug280.a from bug280 group by a"),
+ expected4);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select a, bug280.a from bug280 group by a"),
+ expected4);
+
+ s.close();
+ rollback();
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/aggregate.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/aggregate.sql?view=diff&rev=523691&r1=523690&r2=523691
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/aggregate.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/aggregate.sql Thu Mar 29 05:58:03 2007
@@ -417,58 +417,3 @@
select i from tmax where substr('abc', sum(1), 3) = 'abc';
drop table tmax;
-
---
--- JIRA Bug 280.
---
--- Giving two columns the same name confuses the wacky
--- query rewriting which the parser undertakes for grouped
--- and aggregated queries.
---
-
-create table bug280
-(
- a int,
- b int
-);
-
-insert into bug280( a, b )
-values ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 1 ), ( 2, 2 );
-
---
--- This is bug 280. The alias confused the grouping.
--- The second query should return the same results
--- as the first. However, this bug cannot be fixed
--- until we clean up the query rewriting done by
--- the parser (see bug 681). Until then, the second
--- query will raise an exception advising the customer
--- to rewrite the query.
---
-select a, count( a )
-from bug280
-group by a;
-
--- should raise an error
-select a, count( a ) as a
-from bug280
-group by a;
-
--- should return same results as first query (but with extra column)
-select a, count( a ), a
-from bug280
-group by a;
-
--- different tables with same column name ok
-select t.t_i, m.t_i from
-(select a, b from bug280 group by a, b) t (t_i, t_dt),
-(select a, b from bug280 group by a, b) m (t_i, t_dt)
-where t.t_i = m.t_i and t.t_dt = m.t_dt
-group by t.t_i, t.t_dt, m.t_i, m.t_dt order by t.t_i,m.t_i;
-
--- should be allowed
-select a, a from bug280 group by a;
-select bug280.a, a from bug280 group by a;
-select bug280.a, bug280.a from bug280 group by a;
-select a, bug280.a from bug280 group by a;
-
-drop table bug280;