Skip to main content
Inspiring
March 3, 2026
Answered

CF2025 qoq aggregate functions shows empty string instead of null

  • March 3, 2026
  • 2 replies
  • 207 views

Hi All,

We are migrating from Cf2021 to CF2025.

One issue is that QoQ resuts shows empty string when is not data.

<cfscript>
tempQuery = queryNew("id,name,amt", "integer,varchar,double");

QueryAddRow(tempQuery);
QuerySetCell(tempQuery, "id", 1);
QuerySetCell(tempQuery, "name", "Marco");
QuerySetCell(tempQuery, "amt", 16);

QueryAddRow(tempQuery);
QuerySetCell(tempQuery, "id", 2);
QuerySetCell(tempQuery, "name", "Troy");
QuerySetCell(tempQuery, "amt", 7);

result = queryExecute(
"SELECT SUM(amt) AS cnt FROM tempQuery",
{},
{dbtype="query"}
);

writeDump(result);
writeDump(result.recordcount());

result = queryExecute(
"SELECT SUM(amt) AS cnt FROM tempQuery where id = 3",
{},
{dbtype="query"}
);

writeDump(result);
writeDump(result.recordcount());
</cfscript>

Result

The record count now is 1 for empty QoQ. It shoud be zero.

Any solution on this issue?

Thanks,

JFB

    Correct answer BKBK

    @jfb00 :

    “The record count now is 1 for empty QoQ. It shoud be zero.

    Any solution on this issue?”

     

    TL;DR Answer:
    This is not an issue. For all the ColdFusion versions before 2025, the QoQ record-count for SUM() was 0. That was incorrect. The 0 value contradicts modern SQL standards.

     

    Therefore, from ColdFusion 2025 onwards, the record-count has been changed to 1.

     

    As a workaround, you can add the GROUP BY clause to the SQL string. 

    2 replies

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    March 26, 2026

    @jfb00 :

    “The record count now is 1 for empty QoQ. It shoud be zero.

    Any solution on this issue?”

     

    TL;DR Answer:
    This is not an issue. For all the ColdFusion versions before 2025, the QoQ record-count for SUM() was 0. That was incorrect. The 0 value contradicts modern SQL standards.

     

    Therefore, from ColdFusion 2025 onwards, the record-count has been changed to 1.

     

    As a workaround, you can add the GROUP BY clause to the SQL string. 

    BKBK
    Community Expert
    Community Expert
    March 4, 2026

    I can see two issues:

    1. empty-string instead of null;
    2. record count of 1 instead of 0.

    The solution to 1. is to enable null-support. You can do so server-wide (on the page Server Settings > Settings in the ColdFusion Administrator) or application-wide (by defining this.enableNullSupport = true in Application.cfc).

     

    The issue 2. is a bit complicated. That is because the SQL, 

    "SELECT SUM(amt) AS cnt FROM tempQuery where id = 3"

    is not entirely correct. The aggregate function, SUM(), applies to all rows. So, the where-clause should not be there.

    BKBK
    Community Expert
    Community Expert
    March 4, 2026

    You might be interested in the following bug that I reported just over a month ago: https://tracker.adobe.com/#/view/CF-4229926 (“Query-of-query handles null values inconsistently for varchar and integer”)

    jfb00Author
    Inspiring
    March 4, 2026

    That is the issue, how this bug works? 

    can this issue be fix in the next update? how long it takes?

    Thanks