svn commit: r516454 [2/2] - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/d...

[ab...@apache.org]

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?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- 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 Fri Mar  9 08:37:20 2007
@@ -70,6 +70,12 @@
                         {2,11,202}});
 
         verifyQueryResults(
+                "Q1_2",
+                "select c1,c2,sum(c3) from test group by c2,c1 having c1=1 and c2=10 and sum(c3) > 40",
+                new int[][] {
+                        {1,10,100}});
+
+        verifyQueryResults(
                 "Q2",
                 "select c1+c2, sum(c3) from test group by c1,c2",
                 new int[][] {
@@ -106,6 +112,14 @@
                         {8,12,1},
                         {10,12,1},
                         {9,13,2}});
+
+        // having clause with expression. same as last query with an additional restriction.
+        verifyQueryResults(
+                "Q6b",
+                "select c2-c1, c1+c2, count(*) from test group by c1+c2, c2-c1 having (c2-c1)+count(*)>10",
+                new int[][] {
+                        {10,12,1},
+                        {9,13,2}});
     }
     
     
@@ -123,6 +137,11 @@
                 "select a+1, sum(b) from (select c1+1  a , c2+1 b from test) t group by a+1",
                 new int[][] {
                         {3,23}, {4,35}});
+
+        verifyQueryResults(
+                "Q2",
+                "select a+1, sum(b) from (select c1+1  a , c2+1 b from test) t group by a+1 having a+1 > 3",
+                new int[][] {{4,35}});
         
         verifyQueryResults(
                 "Q3",
@@ -130,6 +149,7 @@
                 "(select c1+1 a, max(c2) b from test group by c1+1) t " +
                 "group by b/2",
                 new int[][] {{5,5}});
+
     }
     
 
@@ -214,6 +234,13 @@
                 "select rtrim(c1) from t2 group by rtrim(c1)",
                 new String[][] { {"123"},
                                  {"abc"} });
+        
+       // rtrim in having clause as well.
+        verifyQueryResults(
+                "rtrim_having",
+                "select rtrim(c1) from t2 group by rtrim(c1) having rtrim(c1) like 'ab%'",
+                new String[][] { {"abc"} });
+        
 
         // locate (2-args)
         verifyQueryResults(

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql Fri Mar  9 08:37:20 2007
@@ -381,3 +381,17 @@
    select nullif('x','x') as f0, nullif(1,1) as f1 from b; 
 drop table a;
 drop table b;
+
+-- DERBY-681. Check union with group by/having
+create table o (name varchar(20), ord int);
+create table a (ord int, amount int);
+
+create view v1 (vx, vy) 
+as select name, sum(ord) from o where ord > 0 group by name, ord
+    having ord <= ANY (select ord from a);
+
+select vx, vy from v1
+     union select vx, sum(vy) from v1 group by vx, vy having (vy / 2) > 15;
+drop view v1;
+drop table o;
+drop table a;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/views.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/views.sql?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/views.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/views.sql Fri Mar  9 08:37:20 2007
@@ -203,3 +203,20 @@
 -- DERBY-2018
 -- expect error
 CREATE VIEW v1(c1) AS VALUES NULL;
+
+-- DERBY-681
+create table o (name varchar(20), ord int);
+create table a (ord int, amount int);
+
+create view v1 (vx, vy) 
+as select name, sum(ord) from o where ord > 0 group by name, ord;
+
+create view v2 (vx, vy) as
+  select name, sum(ord) from o where ord > 0 group by name, ord
+    having ord <= ANY (select ord from a);
+drop view v2;
+drop view v1;
+drop table a;
+drop table o;
+
+