================================================================================
Merge Diffs: /ade/cdelahun_toplinkmain/tldev/source/essentials/oracle/toplink/essentials/queryframework/ReportQuery.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.27
Report generated at Wed Aug  9 16:47:22 2006
--------------------------------------------------------------------------------
*** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.27	Wed Aug  9 13:04:54 2006
--- /ade/cdelahun_toplinkmain/tldev/source/essentials/oracle/toplink/essentials/queryframework/ReportQuery.java	Wed Aug  9 16:47:22 2006
***************
*** 911,969 ****
       * Added to fix bug 3268040, addCount(objectAttribute) not supported.
       */
      protected void prepareObjectAttributeCount(Dictionary clonedExpressions) {
!         ReportItem countItem = (getItems().isEmpty() ? null : (ReportItem)getItems().elementAt(0));
!         if ((countItem != null) && (countItem.getAttributeExpression() instanceof FunctionExpression)) {
!             FunctionExpression count = (FunctionExpression)countItem.getAttributeExpression();
!             if (count.getOperator().getSelector() == ExpressionOperator.Count) {
!                 Expression baseExp = count.getBaseExpression();
!                 boolean distinctUsed = false;
!                 if (baseExp.isFunctionExpression() && (((FunctionExpression)baseExp).getOperator().getSelector() == ExpressionOperator.Distinct)) {
!                     distinctUsed = true;
!                     baseExp = ((FunctionExpression)baseExp).getBaseExpression();
!                 }
!                 if (baseExp.isQueryKeyExpression()) {
!                     QueryKeyExpression countExp = (QueryKeyExpression)baseExp;
! 
!                     // now need to find out if it is a direct to field or something else.
!                     DatabaseMapping mapping = getLeafMappingFor(countExp, getDescriptor());
!                     if ((mapping != null) && !mapping.isDirectToFieldMapping() && (mapping.getReferenceDescriptor() != null)) {
!                         // At this point we are committed to rewriting the query.
!                         ClassDescriptor newDescriptor = mapping.getReferenceDescriptor();
!                         if (distinctUsed) {
!                             // If this is a subselect countExp is yet uncloned,
!                             // and will miss out if moved now from items into a selection criteria.
!                             if (clonedExpressions != null) {
!                                 if (clonedExpressions.get(countExp.getBuilder()) != null) {
!                                     countExp = (QueryKeyExpression)countExp.copiedVersionFrom(clonedExpressions);
                                  } else {
!                                     countExp = (QueryKeyExpression)countExp.rebuildOn(getExpressionBuilder());
                                  }
!                             }
! 
!                             // Now the reference class of the query needs to be reversed.
!                             // See the bug description for an explanation.
!                             ExpressionBuilder countBuilder = countExp.getBuilder();
!                             ExpressionBuilder outerBuilder = new ExpressionBuilder();
! 
!                             ReportQuery subSelect = new ReportQuery(getReferenceClass(), countBuilder);
!                             subSelect.setShouldRetrieveFirstPrimaryKey(true);
! 
!                             // Make sure the outerBuilder does not appear on the left of the subselect.
!                             // Putting a builder on the left is desirable to trigger an optimization.
!                             if (getSelectionCriteria() != null) {
!                                 outerBuilder.setQueryClass(newDescriptor.getJavaClass());
!                                 subSelect.setSelectionCriteria(getSelectionCriteria().and(outerBuilder.equal(countExp)));
                              } else {
!                                 subSelect.setSelectionCriteria(countExp.equal(outerBuilder));
                              }
-                             setSelectionCriteria(outerBuilder.exists(subSelect));
-                             getItems().setElementAt(new ReportItem(countItem.getName(), outerBuilder.count()), 0);
-                             setReferenceClass(newDescriptor.getJavaClass());
-                             changeDescriptor(getSession());
-                         } else {
-                             // Here everything worked fine, save an empty COUNT() was being generated.
-                             Expression newCount = countExp.prefixSQL("*").count();
-                             getItems().setElementAt(new ReportItem(countItem.getName(), newCount), 0);
                          }
                      }
                  }
--- 911,973 ----
       * Added to fix bug 3268040, addCount(objectAttribute) not supported.
       */
      protected void prepareObjectAttributeCount(Dictionary clonedExpressions) {
!         int numOfReportItems = getItems().size(); 
!         //gf675: need to loop through all items to fix all count(..) instances
!         for (int i =0;i<numOfReportItems; i++){
!             ReportItem countItem = (ReportItem)getItems().elementAt(i);
!             if ((countItem != null) && (countItem.getAttributeExpression() instanceof FunctionExpression)) {
!                 FunctionExpression count = (FunctionExpression)countItem.getAttributeExpression();
!                 if (count.getOperator().getSelector() == ExpressionOperator.Count) {
!                     Expression baseExp = count.getBaseExpression();
!                     boolean distinctUsed = false;
!                     if (baseExp.isFunctionExpression() && (((FunctionExpression)baseExp).getOperator().getSelector() == ExpressionOperator.Distinct)) {
!                         distinctUsed = true;
!                         baseExp = ((FunctionExpression)baseExp).getBaseExpression();
!                     }
!                     if (baseExp.isQueryKeyExpression()) {
!                         QueryKeyExpression countExp = (QueryKeyExpression)baseExp;
!     
!                         // now need to find out if it is a direct to field or something else.
!                         DatabaseMapping mapping = getLeafMappingFor(countExp, getDescriptor());
!                         if ((mapping != null) && !mapping.isDirectToFieldMapping() && (mapping.getReferenceDescriptor() != null)) {
!                             // At this point we are committed to rewriting the query.
!                             ClassDescriptor newDescriptor = mapping.getReferenceDescriptor();
!                             if (distinctUsed) {
!                                 // If this is a subselect countExp is yet uncloned,
!                                 // and will miss out if moved now from items into a selection criteria.
!                                 if (clonedExpressions != null) {
!                                     if (clonedExpressions.get(countExp.getBuilder()) != null) {
!                                         countExp = (QueryKeyExpression)countExp.copiedVersionFrom(clonedExpressions);
!                                     } else {
!                                         countExp = (QueryKeyExpression)countExp.rebuildOn(getExpressionBuilder());
!                                     }
!                                 }
!     
!                                 // Now the reference class of the query needs to be reversed.
!                                 // See the bug description for an explanation.
!                                 ExpressionBuilder countBuilder = countExp.getBuilder();
!                                 ExpressionBuilder outerBuilder = new ExpressionBuilder();
!     
!                                 ReportQuery subSelect = new ReportQuery(getReferenceClass(), countBuilder);
!                                 subSelect.setShouldRetrieveFirstPrimaryKey(true);
!     
!                                 // Make sure the outerBuilder does not appear on the left of the subselect.
!                                 // Putting a builder on the left is desirable to trigger an optimization.
!                                 if (getSelectionCriteria() != null) {
!                                     outerBuilder.setQueryClass(newDescriptor.getJavaClass());
!                                     subSelect.setSelectionCriteria(getSelectionCriteria().and(outerBuilder.equal(countExp)));
                                  } else {
!                                     subSelect.setSelectionCriteria(countExp.equal(outerBuilder));
                                  }
!                                 setSelectionCriteria(outerBuilder.exists(subSelect));
!                                 getItems().setElementAt(new ReportItem(countItem.getName(), outerBuilder.count()), i);
!                                 setReferenceClass(newDescriptor.getJavaClass());
!                                 changeDescriptor(getSession());
                              } else {
!                                 // Here everything worked fine, save an empty COUNT() was being generated.
!                                 Expression newCount = countExp.prefixSQL("*").count();
!                                 getItems().setElementAt(new ReportItem(countItem.getName(), newCount), i);
                              }
                          }
                      }
                  }
================================================================================
Merge Diffs: /ade/cdelahun_toplinkmain/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/ejb/ejbqltesting/JUnitEJBQLComplexAggregateTestSuite.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.9
Report generated at Wed Aug  9 16:47:22 2006
--------------------------------------------------------------------------------
*** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.9	Wed Aug  9 16:03:10 2006
--- /ade/cdelahun_toplinkmain/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/ejb/ejbqltesting/JUnitEJBQLComplexAggregateTestSuite.java	Wed Aug  9 16:47:22 2006
***************
*** 47,52 ****
--- 47,53 ----
  import java.util.List;
  
  import java.util.Vector;
+ import javax.persistence.Query;
  import junit.framework.Assert;
  import junit.framework.Test;
  import junit.framework.TestSuite;
***************
*** 56,61 ****
--- 57,63 ----
  
  import oracle.toplink.essentials.queryframework.ReportQuery;
  
+ import oracle.toplink.essentials.testing.models.cmp3.advanced.Address;
  import oracle.toplink.essentials.testing.models.cmp3.advanced.Employee;
  import oracle.toplink.essentials.testing.models.cmp3.advanced.EmployeePopulator;
  
***************
*** 108,114 ****
--- 110,119 ----
          TestSuite suite = new TestSuite();
          suite.setName("JUnitEJBQLComplexAggregateTestSuite");
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexAVGTest"));
+         suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountDistinctWithGroupByAndHavingTest"));
+         suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountDistinctWithGroupByWithGroupByTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountTest"));
+         suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountWithGroupByTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexDistinctCountTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexMaxTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexMinTest"));
***************
*** 149,155 ****
          };    
    }
    
-   
      public void complexAVGTest()
      {
          oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
--- 154,159 ----
***************
*** 178,183 ****
--- 182,244 ----
          Assert.assertTrue("Complex AVG test failed", comparer.compareObjects(result, expectedResult));                      
      }
      
+     /*
+      * test for gf675, using count, group by and having fails.  This test is specific for a a use case
+      * with Count and group by
+      */
+     public void complexCountDistinctWithGroupByAndHavingTest()
+     {
+         String havingFilterString = "Toronto";
+         oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
+         //Need to set the class in the expressionbuilder, as the Count(Distinct) will cause the 
+         // query to change and be built around the Employee class instead of the Address class.
+         ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
+             
+         ReportQuery rq = new ReportQuery(Address.class, expbldr);
+         Expression exp = expbldr.anyOf("employees");
+ 
+         Expression exp2 = expbldr.get("city");
+         rq.addAttribute("city", exp2);
+         rq.addCount("COUNT",exp.distinct(),Long.class );
+         rq.addGrouping(exp2);
+         rq.setHavingExpression(exp2.equal(havingFilterString));
+         Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
+         
+         String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city HAVING a.city =?1";
+         Query q = em.createQuery(ejbqlString3);
+         q.setParameter(1,havingFilterString);
+         List result = (List) q.getResultList();
+         
+         Assert.assertTrue("Complex COUNT test failed", comparer.compareObjects(result, expectedResult));                      
+     }
+     
+     /*
+      * test for gf675, using count, group by and having fails.  This test is specific for a a use case
+      * where DISTINCT is used with Count and group by
+      */
+     public void complexCountDistinctWithGroupByTest()
+     {
+         oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
+         
+         //need to set the class in the expressionbuilder, as the Count(Distinct) will cause the 
+         // query to change and be built around the Employee class instead of the Address class.  
+         ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
+             
+         ReportQuery rq = new ReportQuery(Address.class, expbldr);
+         Expression exp = expbldr.anyOf("employees");
+ 
+         Expression exp2 = expbldr.get("city");
+         rq.addAttribute("city", exp2);
+         rq.addCount("COUNT",exp.distinct(),Long.class );
+         rq.addGrouping(exp2);
+         Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
+         
+         String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city";
+         Query q = em.createQuery(ejbqlString3);
+         List result = (List) q.getResultList();
+         
+         Assert.assertTrue("Complex COUNT(Distinct) with Group By test failed", comparer.compareObjects(result, expectedResult));                      
+     }
      
      public void complexCountTest()
      {
***************
*** 205,210 ****
--- 266,298 ----
          Assert.assertTrue("Complex COUNT test failed", comparer.compareObjects(result, expectedResult));                      
      }
      
+     /*
+      * test for gf675, using count, group by and having fails.  This test is specific for a a use case
+      * with Count and group by
+      */
+     public void complexCountWithGroupByTest()
+     {
+         oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
+         //Need to set the class in the expressionbuilder, as the Count(Distinct) will cause the 
+         // query to change and be built around the Employee class instead of the Address class.  
+         ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
+             
+         ReportQuery rq = new ReportQuery(Address.class, expbldr);
+         Expression exp = expbldr.anyOf("employees");
+ 
+         Expression exp2 = expbldr.get("city");
+         rq.addAttribute("city", exp2);
+         rq.addCount("COUNT",exp.distinct(),Long.class );
+         rq.addGrouping(exp2);
+         Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
+ 
+         String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city";
+         Query q = em.createQuery(ejbqlString3);
+         List result = (List) q.getResultList();
+         
+         Assert.assertTrue("Complex COUNT with Group By test failed", comparer.compareObjects(result, expectedResult));                      
+     }
+     
      public void complexDistinctCountTest()
      {
          oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
================================================================================
Merge Diffs: /ade/cdelahun_toplinkmain/tldev/source/essentials/oracle/toplink/essentials/queryframework/ReportQuery.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.27
Report generated at Wed Aug  9 17:06:35 2006
--------------------------------------------------------------------------------
*** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.27	Wed Aug  9 13:04:54 2006
--- /ade/cdelahun_toplinkmain/tldev/source/essentials/oracle/toplink/essentials/queryframework/ReportQuery.java	Wed Aug  9 16:47:22 2006
***************
*** 911,969 ****
       * Added to fix bug 3268040, addCount(objectAttribute) not supported.
       */
      protected void prepareObjectAttributeCount(Dictionary clonedExpressions) {
!         ReportItem countItem = (getItems().isEmpty() ? null : (ReportItem)getItems().elementAt(0));
!         if ((countItem != null) && (countItem.getAttributeExpression() instanceof FunctionExpression)) {
!             FunctionExpression count = (FunctionExpression)countItem.getAttributeExpression();
!             if (count.getOperator().getSelector() == ExpressionOperator.Count) {
!                 Expression baseExp = count.getBaseExpression();
!                 boolean distinctUsed = false;
!                 if (baseExp.isFunctionExpression() && (((FunctionExpression)baseExp).getOperator().getSelector() == ExpressionOperator.Distinct)) {
!                     distinctUsed = true;
!                     baseExp = ((FunctionExpression)baseExp).getBaseExpression();
!                 }
!                 if (baseExp.isQueryKeyExpression()) {
!                     QueryKeyExpression countExp = (QueryKeyExpression)baseExp;
! 
!                     // now need to find out if it is a direct to field or something else.
!                     DatabaseMapping mapping = getLeafMappingFor(countExp, getDescriptor());
!                     if ((mapping != null) && !mapping.isDirectToFieldMapping() && (mapping.getReferenceDescriptor() != null)) {
!                         // At this point we are committed to rewriting the query.
!                         ClassDescriptor newDescriptor = mapping.getReferenceDescriptor();
!                         if (distinctUsed) {
!                             // If this is a subselect countExp is yet uncloned,
!                             // and will miss out if moved now from items into a selection criteria.
!                             if (clonedExpressions != null) {
!                                 if (clonedExpressions.get(countExp.getBuilder()) != null) {
!                                     countExp = (QueryKeyExpression)countExp.copiedVersionFrom(clonedExpressions);
                                  } else {
!                                     countExp = (QueryKeyExpression)countExp.rebuildOn(getExpressionBuilder());
                                  }
!                             }
! 
!                             // Now the reference class of the query needs to be reversed.
!                             // See the bug description for an explanation.
!                             ExpressionBuilder countBuilder = countExp.getBuilder();
!                             ExpressionBuilder outerBuilder = new ExpressionBuilder();
! 
!                             ReportQuery subSelect = new ReportQuery(getReferenceClass(), countBuilder);
!                             subSelect.setShouldRetrieveFirstPrimaryKey(true);
! 
!                             // Make sure the outerBuilder does not appear on the left of the subselect.
!                             // Putting a builder on the left is desirable to trigger an optimization.
!                             if (getSelectionCriteria() != null) {
!                                 outerBuilder.setQueryClass(newDescriptor.getJavaClass());
!                                 subSelect.setSelectionCriteria(getSelectionCriteria().and(outerBuilder.equal(countExp)));
                              } else {
!                                 subSelect.setSelectionCriteria(countExp.equal(outerBuilder));
                              }
-                             setSelectionCriteria(outerBuilder.exists(subSelect));
-                             getItems().setElementAt(new ReportItem(countItem.getName(), outerBuilder.count()), 0);
-                             setReferenceClass(newDescriptor.getJavaClass());
-                             changeDescriptor(getSession());
-                         } else {
-                             // Here everything worked fine, save an empty COUNT() was being generated.
-                             Expression newCount = countExp.prefixSQL("*").count();
-                             getItems().setElementAt(new ReportItem(countItem.getName(), newCount), 0);
                          }
                      }
                  }
--- 911,973 ----
       * Added to fix bug 3268040, addCount(objectAttribute) not supported.
       */
      protected void prepareObjectAttributeCount(Dictionary clonedExpressions) {
!         int numOfReportItems = getItems().size(); 
!         //gf675: need to loop through all items to fix all count(..) instances
!         for (int i =0;i<numOfReportItems; i++){
!             ReportItem countItem = (ReportItem)getItems().elementAt(i);
!             if ((countItem != null) && (countItem.getAttributeExpression() instanceof FunctionExpression)) {
!                 FunctionExpression count = (FunctionExpression)countItem.getAttributeExpression();
!                 if (count.getOperator().getSelector() == ExpressionOperator.Count) {
!                     Expression baseExp = count.getBaseExpression();
!                     boolean distinctUsed = false;
!                     if (baseExp.isFunctionExpression() && (((FunctionExpression)baseExp).getOperator().getSelector() == ExpressionOperator.Distinct)) {
!                         distinctUsed = true;
!                         baseExp = ((FunctionExpression)baseExp).getBaseExpression();
!                     }
!                     if (baseExp.isQueryKeyExpression()) {
!                         QueryKeyExpression countExp = (QueryKeyExpression)baseExp;
!     
!                         // now need to find out if it is a direct to field or something else.
!                         DatabaseMapping mapping = getLeafMappingFor(countExp, getDescriptor());
!                         if ((mapping != null) && !mapping.isDirectToFieldMapping() && (mapping.getReferenceDescriptor() != null)) {
!                             // At this point we are committed to rewriting the query.
!                             ClassDescriptor newDescriptor = mapping.getReferenceDescriptor();
!                             if (distinctUsed) {
!                                 // If this is a subselect countExp is yet uncloned,
!                                 // and will miss out if moved now from items into a selection criteria.
!                                 if (clonedExpressions != null) {
!                                     if (clonedExpressions.get(countExp.getBuilder()) != null) {
!                                         countExp = (QueryKeyExpression)countExp.copiedVersionFrom(clonedExpressions);
!                                     } else {
!                                         countExp = (QueryKeyExpression)countExp.rebuildOn(getExpressionBuilder());
!                                     }
!                                 }
!     
!                                 // Now the reference class of the query needs to be reversed.
!                                 // See the bug description for an explanation.
!                                 ExpressionBuilder countBuilder = countExp.getBuilder();
!                                 ExpressionBuilder outerBuilder = new ExpressionBuilder();
!     
!                                 ReportQuery subSelect = new ReportQuery(getReferenceClass(), countBuilder);
!                                 subSelect.setShouldRetrieveFirstPrimaryKey(true);
!     
!                                 // Make sure the outerBuilder does not appear on the left of the subselect.
!                                 // Putting a builder on the left is desirable to trigger an optimization.
!                                 if (getSelectionCriteria() != null) {
!                                     outerBuilder.setQueryClass(newDescriptor.getJavaClass());
!                                     subSelect.setSelectionCriteria(getSelectionCriteria().and(outerBuilder.equal(countExp)));
                                  } else {
!                                     subSelect.setSelectionCriteria(countExp.equal(outerBuilder));
                                  }
!                                 setSelectionCriteria(outerBuilder.exists(subSelect));
!                                 getItems().setElementAt(new ReportItem(countItem.getName(), outerBuilder.count()), i);
!                                 setReferenceClass(newDescriptor.getJavaClass());
!                                 changeDescriptor(getSession());
                              } else {
!                                 // Here everything worked fine, save an empty COUNT() was being generated.
!                                 Expression newCount = countExp.prefixSQL("*").count();
!                                 getItems().setElementAt(new ReportItem(countItem.getName(), newCount), i);
                              }
                          }
                      }
                  }
================================================================================
Merge Diffs: /ade/cdelahun_toplinkmain/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/ejb/ejbqltesting/JUnitEJBQLComplexAggregateTestSuite.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.9
Report generated at Wed Aug  9 17:06:35 2006
--------------------------------------------------------------------------------
*** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/cdelahun/cdelahun_ri_gf675_060809/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.9	Wed Aug  9 16:03:10 2006
--- /ade/cdelahun_toplinkmain/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/ejb/ejbqltesting/JUnitEJBQLComplexAggregateTestSuite.java	Wed Aug  9 16:47:22 2006
***************
*** 47,52 ****
--- 47,53 ----
  import java.util.List;
  
  import java.util.Vector;
+ import javax.persistence.Query;
  import junit.framework.Assert;
  import junit.framework.Test;
  import junit.framework.TestSuite;
***************
*** 56,61 ****
--- 57,63 ----
  
  import oracle.toplink.essentials.queryframework.ReportQuery;
  
+ import oracle.toplink.essentials.testing.models.cmp3.advanced.Address;
  import oracle.toplink.essentials.testing.models.cmp3.advanced.Employee;
  import oracle.toplink.essentials.testing.models.cmp3.advanced.EmployeePopulator;
  
***************
*** 108,114 ****
--- 110,119 ----
          TestSuite suite = new TestSuite();
          suite.setName("JUnitEJBQLComplexAggregateTestSuite");
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexAVGTest"));
+         suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountDistinctWithGroupByAndHavingTest"));
+         suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountDistinctWithGroupByWithGroupByTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountTest"));
+         suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexCountWithGroupByTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexDistinctCountTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexMaxTest"));
          suite.addTest(new JUnitEJBQLComplexAggregateTestSuite("complexMinTest"));
***************
*** 149,155 ****
          };    
    }
    
-   
      public void complexAVGTest()
      {
          oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
--- 154,159 ----
***************
*** 178,183 ****
--- 182,244 ----
          Assert.assertTrue("Complex AVG test failed", comparer.compareObjects(result, expectedResult));                      
      }
      
+     /*
+      * test for gf675, using count, group by and having fails.  This test is specific for a a use case
+      * with Count and group by
+      */
+     public void complexCountDistinctWithGroupByAndHavingTest()
+     {
+         String havingFilterString = "Toronto";
+         oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
+         //Need to set the class in the expressionbuilder, as the Count(Distinct) will cause the 
+         // query to change and be built around the Employee class instead of the Address class.
+         ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
+             
+         ReportQuery rq = new ReportQuery(Address.class, expbldr);
+         Expression exp = expbldr.anyOf("employees");
+ 
+         Expression exp2 = expbldr.get("city");
+         rq.addAttribute("city", exp2);
+         rq.addCount("COUNT",exp.distinct(),Long.class );
+         rq.addGrouping(exp2);
+         rq.setHavingExpression(exp2.equal(havingFilterString));
+         Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
+         
+         String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city HAVING a.city =?1";
+         Query q = em.createQuery(ejbqlString3);
+         q.setParameter(1,havingFilterString);
+         List result = (List) q.getResultList();
+         
+         Assert.assertTrue("Complex COUNT test failed", comparer.compareObjects(result, expectedResult));                      
+     }
+     
+     /*
+      * test for gf675, using count, group by and having fails.  This test is specific for a a use case
+      * where DISTINCT is used with Count and group by
+      */
+     public void complexCountDistinctWithGroupByTest()
+     {
+         oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
+         
+         //need to set the class in the expressionbuilder, as the Count(Distinct) will cause the 
+         // query to change and be built around the Employee class instead of the Address class.  
+         ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
+             
+         ReportQuery rq = new ReportQuery(Address.class, expbldr);
+         Expression exp = expbldr.anyOf("employees");
+ 
+         Expression exp2 = expbldr.get("city");
+         rq.addAttribute("city", exp2);
+         rq.addCount("COUNT",exp.distinct(),Long.class );
+         rq.addGrouping(exp2);
+         Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
+         
+         String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city";
+         Query q = em.createQuery(ejbqlString3);
+         List result = (List) q.getResultList();
+         
+         Assert.assertTrue("Complex COUNT(Distinct) with Group By test failed", comparer.compareObjects(result, expectedResult));                      
+     }
      
      public void complexCountTest()
      {
***************
*** 205,210 ****
--- 266,298 ----
          Assert.assertTrue("Complex COUNT test failed", comparer.compareObjects(result, expectedResult));                      
      }
      
+     /*
+      * test for gf675, using count, group by and having fails.  This test is specific for a a use case
+      * with Count and group by
+      */
+     public void complexCountWithGroupByTest()
+     {
+         oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();                  
+         //Need to set the class in the expressionbuilder, as the Count(Distinct) will cause the 
+         // query to change and be built around the Employee class instead of the Address class.  
+         ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
+             
+         ReportQuery rq = new ReportQuery(Address.class, expbldr);
+         Expression exp = expbldr.anyOf("employees");
+ 
+         Expression exp2 = expbldr.get("city");
+         rq.addAttribute("city", exp2);
+         rq.addCount("COUNT",exp.distinct(),Long.class );
+         rq.addGrouping(exp2);
+         Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
+ 
+         String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city";
+         Query q = em.createQuery(ejbqlString3);
+         List result = (List) q.getResultList();
+         
+         Assert.assertTrue("Complex COUNT with Group By test failed", comparer.compareObjects(result, expectedResult));                      
+     }
+     
      public void complexDistinctCountTest()
      {
          oracle.toplink.essentials.ejb.cmp3.EntityManager em = (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();