ERROR : column "this_.date" must appear in the GROUP BY clause or be used in an aggregate function

Question

   ERROR: column "this_.date" must appear in the GROUP BY clause or be used in an aggregate function
      Position: 296. Stacktrace follows:
    org.postgresql.util.PSQLException: ERROR: column "this_.date" must appear in the GROUP BY clause or be used in an aggregate function
      Position: 296
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at grails.orm.PagedResultList.<init>(PagedResultList.java:53)
        at grails.orm.HibernateCriteriaBuilder.invokeMethod(HibernateCriteriaBuilder.java:1511)
        at com.rsa.ci.TestResultsService.getProductVersionsWithCriteria(TestResultsService.groovy:501)
        at com.rsa.ci.TestResultsService.getBuildStatusHomePageResults(TestResultsService.groovy:568)
        at com.rsa.ci.BuildStatusController$_closure11.doCall(BuildStatusController.groovy:219)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

code from line No. 501 where the error is pointing the line number from -->

def reqProductVersions = TestRuns.createCriteria().list(max: reqNumberOfRecords, offset: startIndex) {
                                        projections {
                                            groupProperty "productVersion"
                                        }
                                        'in'("testType",filters.testTypes)
                                        'in'("testSuiteName",suites)
                                        eq("product",filters.product)
                                        like("branch",filters.branch)
                                        like("deploymentMode",filters.deploymentMode)
                                        if(filters.tag){
                                            isNotNull("tag")
                                            ne("tag","")
                                        }
                                        if(!filters.jenkinsInstance.equals("%%")){
                                            processedJenkinsRuns{
                                                like("jenkinsServer",filters.jenkinsInstance)
                                            }
                                        }
                                        cache true
                                        order("date","desc")
                                    }
            // we need total count. createcriteria has issue when we ask total count with group property combined
            def totalCount = TestRuns.createCriteria().get {
                                        projections {
                                            countDistinct "productVersion"
                                        }
                                        'in'("testType",filters.testTypes)
                                        'in'("testSuiteName",suites)
                                        eq("product",filters.product)
                                        like("branch",filters.branch)
                                        like("deploymentMode",filters.deploymentMode)
                                        if(filters.tag){
                                            isNotNull("tag")
                                            ne("tag","")
                                        }
                                        if(!filters.jenkinsInstance.equals("%%")){
                                            processedJenkinsRuns{
                                                like("jenkinsServer",filters.jenkinsInstance)
                                            }
                                        }
                                        cache true
                                    }
            def productVersionAndCount = [:];

            productVersionAndCount.put("total",totalCount);
            productVersionAndCount.put("productVersions",reqProductVersions);
            return productVersionAndCount;

        }

        def getAllTemplateNames(def productVersions){
            def templatesResults = ArtifactsInformation.findAllByP4ChangeListNumInList(productVersions);
            Map<String, List<String>> templates = new HashMap<String, List<String>>();

            templatesResults.each{ tmp ->
                List<String> tempList = templates.get(tmp.p4ChangeListNum);
                if(tempList == null){
                    tempList = new ArrayList<String>();
                    templates.put(tmp.p4ChangeListNum, tempList)
                }
                tempList.add(tmp.templateName);
            }
            return templates;
        }

        def getBuildStatusHomePageResults(int startIndex,int reqNumberOfRecords,BuildParamsDTO filters, def isCompareChecked){

        **568 ->**  def productVersionsAndCount = filters.junitAndSeleniumFilter?getProductVersionWithJunitAndSeleniumAndCriteria(filters,startIndex,reqNumberOfRecords):getProductVersionsWithCriteria(filters,startIndex,reqNumberOfRecords);

            def productVersions = productVersionsAndCount.get("productVersions");

            int totalRecords = productVersionsAndCount.get("total");

            log.debug productVersions.toString()+" are selected as required product versions in getData"

            Collections.sort(productVersions,Collections.reverseOrder());

            Map<String, VersionDto> buildResults = new LinkedHashMap<String, VersionDto>();

            productVersions.each{changeList ->
                buildResults.put(changeList, new VersionDto(changeList))
            }

            if(productVersions.size() > 0){
                getTestResults(productVersions, filters, buildResults);
            }

            def reqGroups = filters.testTypes;

            def reqSuites =filters.testSuites ;

            def dataToRender = [:]

            dataToRender =  buildDataTableObject(buildResults, reqGroups, reqSuites, filters, totalRecords, isCompareChecked);

            return dataToRender;


        }

Can anybody tell me what it actually means and why I'm getting this error.Pls

Answer 1

your criteria seems to be having common aggregation problem, your selected fields must appear in group by clause.

You can try defining 'date' in groupProperty otherwise you can make a sub-query to aggregate and then join with additional columns you want to show.

Take help from this answer

Hope this helps. Thanks

Answer 2

Could not find matching constructor for: com.rsa.ci.jsonDto.VersionDto(java.sql.Timestamp). Stacktrace follows:
groovy.lang.GroovyRuntimeException: Could not find matching constructor for: com.rsa.ci.jsonDto.VersionDto(java.sql.Timestamp)
    at com.rsa.ci.TestResultsService$_getBuildStatusHomePageResults_closure27$$EPSZjAGS.doCall(TestResultsService.groovy:583)
    at com.rsa.ci.TestResultsService$$EPSZjAAp.getBuildStatusHomePageResults(TestResultsService.groovy:582)
    at com.rsa.ci.BuildStatusController$_closure11.doCall(BuildStatusController.groovy:219)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)

TestResultService from Line 502

def reqProductVersions = TestRuns.createCriteria().list(max: reqNumberOfRecords, offset: startIndex) {
                                    projections {
                                        groupProperty "productVersion"
                                        groupProperty "date"
                                    }
                                    'in'("testType",filters.testTypes)
                                    'in'("testSuiteName",suites)
                                    eq("product",filters.product)
                                    like("branch",filters.branch)
                                    like("deploymentMode",filters.deploymentMode)
                                    if(filters.tag){
                                        isNotNull("tag")
                                        ne("tag","")
                                    }
                                    if(!filters.jenkinsInstance.equals("%%")){
                                        processedJenkinsRuns{
                                            like("jenkinsServer",filters.jenkinsInstance)
                                        }
                                    }
                                    cache true
                                    order("date","desc")
                                }
        // we need total count. createcriteria has issue when we ask total count with group property combined
        def totalCount = TestRuns.createCriteria().get {
                                    projections {
                                        countDistinct "productVersion"
                                    }
                                    'in'("testType",filters.testTypes)
                                    'in'("testSuiteName",suites)
                                    eq("product",filters.product)
                                    like("branch",filters.branch)
                                    like("deploymentMode",filters.deploymentMode)
                                    if(filters.tag){
                                        isNotNull("tag")
                                        ne("tag","")
                                    }
                                    if(!filters.jenkinsInstance.equals("%%")){
                                        processedJenkinsRuns{
                                            like("jenkinsServer",filters.jenkinsInstance)
                                        }
                                    }
                                    cache true
                                }
        def productVersionAndCount = [:];

        productVersionAndCount.put("total",totalCount);
        productVersionAndCount.put("productVersions",reqProductVersions);
        return productVersionAndCount;

    }

    def getAllTemplateNames(def productVersions){
        def templatesResults = ArtifactsInformation.findAllByP4ChangeListNumInList(productVersions);
        Map<String, List<String>> templates = new HashMap<String, List<String>>();

        templatesResults.each{ tmp ->
            List<String> tempList = templates.get(tmp.p4ChangeListNum);
            if(tempList == null){
                tempList = new ArrayList<String>();
                templates.put(tmp.p4ChangeListNum, tempList)
            }
            tempList.add(tmp.templateName);
        }
        return templates;
    }

    def getBuildStatusHomePageResults(int startIndex,int reqNumberOfRecords,BuildParamsDTO filters, def isCompareChecked){

        def productVersionsAndCount = filters.junitAndSeleniumFilter?getProductVersionWithJunitAndSeleniumAndCriteria(filters,startIndex,reqNumberOfRecords):getProductVersionsWithCriteria(filters,startIndex,reqNumberOfRecords);

        def productVersions = productVersionsAndCount.get("productVersions");

        int totalRecords = productVersionsAndCount.get("total");

        log.debug productVersions.toString()+" are selected as required product versions in getData"

        Collections.sort(productVersions,Collections.reverseOrder());

        Map<String, VersionDto> buildResults = new LinkedHashMap<String, VersionDto>();

        productVersions.each{changeList ->
            buildResults.put(changeList, new VersionDto(changeList))
        }

        if(productVersions.size() > 0){
            getTestResults(productVersions, filters, buildResults);
        }

        def reqGroups = filters.testTypes;

        def reqSuites =filters.testSuites ;

        def dataToRender = [:]

        dataToRender =  buildDataTableObject(buildResults, reqGroups, reqSuites, filters, totalRecords, isCompareChecked);

        return dataToRender;


    }
    def getProductVersions(BuildParamsDTO filters){
        def allProductVersions = TestRuns.createCriteria().list {
            projections{ distinct "productVersion"}
            eq("product",filters.product)
            like("branch",filters.branch)
            like("deploymentMode",filters.deploymentMode)
            like("repo",filters.repo)
            between("date", filters.firstDate,filters.lastDate)
            processedJenkinsRuns{
                like("jenkinsServer",filters.jenkinsInstance)
            }
            order("date","desc")
        }
        def returnArr=[];
        allProductVersions.each{prodVersion ->
            def insideArr=[];
            insideArr.push(prodVersion)
            returnArr.push(insideArr)

        }
        return returnArr;
    }