Query of Query on Lucee 5.3.8.189

Hi,

OS: Ubuntu 18.04.5 LTS
Java Version: 11.0.11 (Ubuntu) 64bit
Tomcat Version: Apache Tomcat/8.5.56
Lucee Version: Lucee 5.3.8.189

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.

Please help

Thanks and Regards,

Sonny

Can you file a bug https://luceeserver.atlassian.net/ ?

Hi Zackster,

Thank you, I have already file a bug on the site you have provided.

Regards,

Sonny

@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.

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

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.
image

Here would be a repro case for doing the same thing in CFML:

qry = queryNew( 'col', 'Integer' )

writedump( qry )

qoq = queryExecute(
  'SELECT sum( col )
   FROM qry',
  [],
  { dbType = 'query' }
)

writedump( qoq )

Adobe ColdFusion also suffers from the same bug Lucee used to have, and ACF has a ticket here to address it:
https://tracker.adobe.com/#/view/CF-4211230

Here is a relevant Stack Overflow question that covers how and why SQL behaves in this manner.

As a workaround, if you want to get zero rows back from an aggregated select, you can add a GROUP BY clause to the query like so:

qry = queryNew( 'col', 'Integer' )

writedump( qry )

qoq = queryExecute(
  'SELECT sum( col )
   FROM qry
   GROUP BY col',
  [],
  { dbType = 'query' }
)

writedump( qoq )

And here’s a runnable trycf.com Gist that has both of those CF examples side-by-side:

3 Likes

You can wrap the outcome with a val() function… val(myquery.amount_local) will return zero if there are no records returned by the query.

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.

Hi, here the select statement i tested, we use a lot like this in our codes around thousands files. changing one by one will cause a lot of time.

Query 1

select sum(amount) as amount from qs_result where dnum_auto = 'xDQ1047'

Amount

Result 1: it will return as zero rows which is correct



Query 2: i will use query one (1) as source

select sum(amount) as amount from query1 where dnum_auto = 'xDQ1047'

Amount
 

Result 2: it will return 1 row which is not correct

Thanks sonny

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.

1 Like

Hi Brad,

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”)>

select *
from new_query_set

select * from new_query_set where code = 'AAB'

Thanks and Regards,

Sonny

@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).

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


In the mean time, you can update your code like so to work in both scenarios and on all current and future versions of Lucee…


if( qry.recordCount && len( qry.amount_local) ) {
   // Query has data
}

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:
itema_20210727
<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

please don’t double post to jira and here, please keep discussions here

1 Like

I explained how to accomplish this above. Please re-read my suggestions and let me know if you have any questions.