We have upgrade to Lucee 5.3.8.189, and we have found out some issue.
Using sum on query of query from another query result it have inserted blank record (see image 3 below).
It will happen only if the query result source result is null.
Any settings to solve the issue? Lucee version 5.3.7.47 do not have this issue.
@SonnyG3 Can you please add a reproducible case of code that shows this issue that other people can run? Neither this thread nor the ticket has enough information to fully reproduce.
I would still like a repro case as I asked above-- but after poking a bit, I have a feeling I know what youâre trying to convey. There was a change in 5.3.8 that fixed an incorrect behavior in Luceeâs query of queries and brought it inline with the SQL spec observed by every other major DB out there (SQL Server, MySQL, Oracle, etc).
The long and short is that aggregated selects will always return one row, even if the source set was empty. And in that case, most aggregates (except count()) will return a null value as their result. Here is a quick example in SQL Server that shows how DBâs handle it, though you can create this in any other DB you like and see the same thing:
declare @tmp table (col int)
SELECT sum( col )
FROM @tmp
That code, when run against MS SQL Server, will return a single row containing null.
Here would be a repro case for doing the same thing in CFML:
Thatâs not really necessary since QoQ can do this already with
isNull( sum( col ), 0 )
but if you read the original post, he wasnât asking how to get back a 0. The behavior he experienced on 5.3.7 (which my group by workaround above reproduces) is getting back no records at all.
Again, you have that backwards. You may desire result 1 to be correct, but it isnât and never has been. This was a bug in Lucee where itâs QoQ handling of aggregate functions did not match every other DB in existence. I do apologize that you had some code depending on that incorrect behavior, but you will need to update the code. You can either put in a GROUP BY like I showed above or modify your usage of the result to not check for qryResult.recordCount but instead len( qryResult.aggreateCol ).
If you need more assistance with this, you can post some examples of your full queries and we can help you.
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)
select dnum_auto, amount_local
from account_tbl
Result: 100 records
a. create a query from above query results (qs_result)
select sum(amount_local) as amount_local
from qs_result
where dnum_auto = âxDQ1047â
Result: 0 records
b. create a query from query results (testquery)
select sum(amount_local) as amount_local
from testquery
where dnum_auto = âxDQ1047â
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â)>
@SonnyG3 Please provide full code samples that we can just copy/paste and run. Youâre only showing us bits and pieces which makes it difficult to reproduce what youâve got going on.
So to clarify-- youâve got two separate questions youâre asking. The second one I just figured out after doing some experimentation with your last code sample.
Question 1: aggregate functions return a single row with a null value in 5.3.8 when the set being operated against is empty
As covered above, this is a new and desired behavior in Lucee 5.3.8 to bring itâs functionality in line with other databases. I understand you want it to âwork like it did beforeâ but this was done on purpose and youâve been presented with a couple ways to modify your code to work around it.
Question 2: The new aggregate functionality isnât consistent.
You have found a bug here as it seems the new aggregate functionality âmissed a spotâ. The differentiator is when you have a WHERE clause on your query as this follows a different code path. Here is a trycf example shows shows what you were trying to convey.
This needs fixed and Iâll send a pull request soon to bring the proper behavior to both query of queries with and without a WHERE clause (including adding missing tests).
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.
       < 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).