Author: jleroux
Date: Mon Aug  7 10:41:32 2017
New Revision: 1804320

URL: http://svn.apache.org/viewvc?rev=1804320&view=rev
Log:
"Applied fix from trunk framework for revision: 1804319  " 
------------------------------------------------------------------------
r1804319 | jleroux | 2017-08-07 12:40:32 +0200 (lun. 07 août 2017) | 20 lignes

Fixed: Bug SQL Count Distinct command in GenericDAO.java 
(OFBIZ-5701)

jleroux: Kieuanhvu's explanation was not totally clear. So I rather provide
Renuka Srishti's at OFBIZ-9428 "getResultsSizeAfterPartialList() return wrong 
count with distinct() for View Entity"

Here is the code sample to test the issue:

EntityListIterator productAssocListItr = null;
productAssocListItr = from("ProductAndAssoc").distinct().queryIterator();
productAssocListSize = productAssocListItr.getResultsSizeAfterPartialList();

productAssocListSize will differ from the actual distinct records in the 
ProductAndAssoc View Entity.
This issue exists because it gives distinct records on the basis of the 
first column in the table.

Thanks: Kieuanhvu for the patch, Renuka Srishti for a clear explanation and a 
simple way to test (in a groovy for me)
------------------------------------------------------------------------


Modified:
    ofbiz/branches/release16.11/   (props changed)
    
ofbiz/branches/release16.11/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java

Propchange: ofbiz/branches/release16.11/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Mon Aug  7 10:41:32 2017
@@ -10,5 +10,5 @@
 /ofbiz/branches/json-integration-refactoring:1634077-1635900
 /ofbiz/branches/multitenant20100310:921280-927264
 /ofbiz/branches/release13.07:1547657
-/ofbiz/ofbiz-framework/trunk:1783202,1783388,1784549,1784558,1784708,1785882,1785925,1786079,1786214,1786525,1787047,1787133,1787176,1787535,1787906-1787911,1787949,1789665,1789863,1789874,1790396,1790810,1791277,1791288,1791342,1791346,1791490,1791496,1791625,1791634,1791791,1791804,1792270,1792272,1792275,1792432,1792609,1792638,1794008,1794132,1796047,1796262,1797733,1798668,1798682,1798796,1798803,1798808,1799088,1799183,1799327,1799417,1799687,1799767,1799793,1799859,1800250,1800780,1800832,1800853,1801094,1801262-1801263,1801273-1801274,1801303,1801318-1801319,1801336,1801340,1801346,1801359,1801742,1802657,1802766,1803525
+/ofbiz/ofbiz-framework/trunk:1783202,1783388,1784549,1784558,1784708,1785882,1785925,1786079,1786214,1786525,1787047,1787133,1787176,1787535,1787906-1787911,1787949,1789665,1789863,1789874,1790396,1790810,1791277,1791288,1791342,1791346,1791490,1791496,1791625,1791634,1791791,1791804,1792270,1792272,1792275,1792432,1792609,1792638,1794008,1794132,1796047,1796262,1797733,1798668,1798682,1798796,1798803,1798808,1799088,1799183,1799327,1799417,1799687,1799767,1799793,1799859,1800250,1800780,1800832,1800853,1801094,1801262-1801263,1801273-1801274,1801303,1801318-1801319,1801336,1801340,1801346,1801359,1801742,1802657,1802766,1803525,1804319
 
/ofbiz/trunk:1770481,1770490,1770540,1771440,1771448,1771516,1771935,1772346,1772880,1774772,1775441,1779724,1780659,1781109,1781125,1781979,1782498,1782520

Modified: 
ofbiz/branches/release16.11/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java
URL: 
http://svn.apache.org/viewvc/ofbiz/branches/release16.11/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java?rev=1804320&r1=1804319&r2=1804320&view=diff
==============================================================================
--- 
ofbiz/branches/release16.11/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java
 (original)
+++ 
ofbiz/branches/release16.11/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java
 Mon Aug  7 10:41:32 2017
@@ -1023,6 +1023,7 @@ public class GenericDAO {
         }
 
         boolean isGroupBy = false;
+        boolean isCountGroup = false;
         ModelViewEntity modelViewEntity = null;
         if (modelEntity instanceof ModelViewEntity) {
             modelViewEntity = (ModelViewEntity) modelEntity;
@@ -1053,11 +1054,20 @@ public class GenericDAO {
                     // if the field has a function already we don't want to 
count just it, would be meaningless
                     sqlBuffer.append("COUNT(DISTINCT *) ");
                 } else {
-                    sqlBuffer.append("COUNT(DISTINCT ");
-                    // this only seems to support a single column, which is 
not desirable but seems a lot better than no columns or in certain cases all 
columns
-                    sqlBuffer.append(firstSelectField.getColValue());
-                    // 
sqlBuffer.append(modelEntity.colNameString(selectFields, ", ", "", 
datasource.aliasViews));
-                    sqlBuffer.append(")");
+                    isCountGroup = true;
+                    StringBuilder sqlBufferTMP = new StringBuilder("SELECT 
COUNT(*) FROM(");
+                    sqlBuffer.append("DISTINCT ");
+                    for (int i = 0; i < selectFields.size() - 1; i++) {
+                        ModelViewEntity.ModelAlias tmpMA = modelViewEntity != 
null ? modelViewEntity.getAlias(selectFields.get(i).getName()) : null;
+                        if (tmpMA != null && !tmpMA.getColAlias().isEmpty()) {
+                            sqlBuffer.append(selectFields.get(i).getColValue() 
+ " as " + tmpMA.getColAlias() + ",");
+                        } else {
+                            sqlBuffer.append(selectFields.get(i).getColValue() 
+ ",");
+                        }
+                    }
+                    sqlBuffer.append(selectFields.get(selectFields.size() - 
1).getColValue());
+                    sqlBufferTMP.append(sqlBuffer);
+                    sqlBuffer = sqlBufferTMP;
                 }
             } else {
                 sqlBuffer.append("COUNT(DISTINCT *) ");
@@ -1097,6 +1107,9 @@ public class GenericDAO {
         if (isGroupBy) {
             sqlBuffer.append(") TEMP_NAME");
         }
+        if (isCountGroup) {
+            sqlBuffer.append(") TEMP_COUNT_NAME");
+        }
 
         String sql = sqlBuffer.toString();
         if (Debug.verboseOn()) Debug.logVerbose("Count select sql: " + sql, 
module);


Reply via email to