db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject 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
Date Thu, 29 Mar 2007 12:58:04 GMT
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;



Mime
View raw message