Hi Brad Wood,

We are using to check for record count not equal to zero (query.recordcount neq 0), and loop from query to set the new value of variable.
See sample script on the method we use. 

 
For the new version Lucee 5.3.8, checking for the record count will not be possible as it always return the value greater than zero. 
Sample: Record count is always 1 even the value is null, see sample code that affect the condition.

Sample code 1:

<cfif query.recordcount neq 0><cfset new_amount = new_amount  + amount>>

Sample code 2:

<cfset amount = 0>
<cfloop query="qs_result_data">
                <cfset new_amount = new_amount  + amount>
</cfloop>

For this issue any other suggestions besides changing the codes as above?  Or is there a more efficient method to change the codes?

 A number of my friends using Lucee are also affected by same issue after our review of multi-years of affected query-of-query files (running into several thousand files).

Thanks and Regards,

 
Sonny

I updated the conversation here
https://dev.lucee.org/t/query-of-query-on-lucee-5-3-8-189/8612/12?u=bdw429s

@Sonny Boy Taleon Let’s please only have this conversation in one place. I would prefer Discourse.

The TL; DR; is that there is an inconsistent behavior in the new aggregate functionality based on whether a WHERE clause is present. I will work on fixing this so it behaves the same in all cases.

https://luceeserver.atlassian.net/browse/LDEV-3632

@Sonny Boy Taleon In example b, dnum_auto is used in where clause but dnum_auto column doesn't exist in testquery. is this expected?

But while using aggregate with or without where clause also lucee 5.3.8.189 returns one row as null. without where clause ACF returns zero rows. As @Brad Wood said Now lucee matches the behaviour of DB (check with MSSQL & POSTGRES)

the example c) confuses me? as it’s not doing an aggregate

https://trycf.com/gist/7087a930b7e81a653d360f358f76be7b/lucee5?theme=monokai

Hi Mr Brad Wood,

After careful review of the code, we found out the issue of inserted blank rowitem data will occur if the data source is from the database recordset. When use query of query to  create new recordset (see item a) and from this recordset create another query (see item b) on previous Lucee 5.3.7.47 version it doesn’t have this issue.

I have test and created new recordset using queryNew. This case it all consistent and do not have the same issue on above example (see item c).

Currently we are using Item b heavily in our coding and we are hoping to have same result as the Item C.

Database Query (PostgreSQL)
<cfquery datasource="postgresqldb" name="qs_result" >
        select dnum_auto, amount_local
        from account_tbl
</cfquery>

Result: 100 records

a. create a query from above query results (qs_result)
<cfquery name="testquery" dbtype="query">
        select sum(amount_local) as amount_local
        from qs_result
        where dnum_auto = 'xDQ1047'
</cfquery>
Result: 0 records

b. create a query from query results (testquery)
<cfquery name="testquery2" dbtype="query">
        select sum(amount_local) as amount_local
        from testquery
        where dnum_auto = 'xDQ1047'
</cfquery>

Results: 1 row return – this should be zero row return,
we are expecting the same results on Scenario 2.

c. create a recordset using queryNew

<cfset new_query_set = queryNew("code, qnty","VarChar, Integer")>
<cfset queryAddRow(new_query_set)>
<cfset querySetCell(new_query_set, "code", "AAA")>
<cfset querySetCell(new_query_set, "qnty", "1")>            

<cfquery dbtype="query" name="new_query">
                select    *
                from      new_query_set
</cfquery>

Result: 0 records

<cfquery dbtype="query" name="new_query2">
                select    *
                from      new_query_set
                where code = 'AAB'
</cfquery>

Result: 0 records

Regards,

Sonny