Wrong result in QoQ

cubortea · July 31, 2023, 11:21am

I have upgraded my Lucee to the latest version, 5.4.2.17, and when I tested with my test case, it seems this QoQ still has bugs if we use IFNULL in where. Below is my test code:

<cfset qExpEmp = QueryNew(“payrollyear, amount, xhour, xday, price, clientnr”,
“Integer,Decimal,Decimal,Integer,Decimal,varchar”)>

<cfset tmp = querySetCell(qExpEmp,“payrollyear”,“2022”)>
<cfset tmp = querySetCell(qExpEmp,“amount”,“-216.81”)>
<cfset tmp = querySetCell(qExpEmp,“xhour”,“-8.25”)>
<cfset tmp = querySetCell(qExpEmp,“xday”,“1”)>
<cfset tmp = querySetCell(qExpEmp,“price”,“26.2800”)>
<cfset tmp = querySetCell(qExpEmp,“clientnr”,“5”)>

query 1:

SELECT payrollyear ,sum(amount) as amount ,sum(xhour) as vhour ,sum(xday) as vday ,sum(price) as totprice FROM qExpEmp WHERE payrollyear = '2022' AND clientnr = '5' GROUP BY payrollyear, price, clientnr

query2 (wrong result):

SELECT payrollyear
,sum(amount) as amount
,sum(xhour) as vhour
,sum(xday) as vday
,sum(price) as totprice
FROM qExpEmp
WHERE payrollyear = ‘2022’
AND IFNULL(clientnr,‘’) = ‘5’
GROUP BY payrollyear, price, clientnr

pasted_image

as you see in the dump result the result amount -1 is wrong

BK_BK · July 31, 2023, 6:13pm

Your code contains a number of ambiguities of its own. I think you should remove them before any discussion.

First of all, does ifNull() exist as a function in Lucee’s query-of-query? I have searched the web, but have been unable to find any documentation on it.

Secondly, why use AND IFNULL(clientnr,‘’) = ‘5’ and not just AND clientnr = ‘5’?

Thirdly, the clause WHERE payrollyear = '2022' compares to a string, in spite of payrollyear being defined as an integer.

Lastly, why use GROUP BY payrollyear, price, clientnr and not just GROUP BY payrollyear?

When I removed these ambiguities, I obtained the expected result.
Here goes:

<cfset qExpEmp = queryNew("payrollyear, amount, xhour, xday, price, clientnr","Integer,decimal,decimal,Integer,decimal,varchar")>

<cfset numberOfRows=queryAddRow(qExpEmp,2)>

<!--- First row --->
<cfset tmp = querySetCell(qExpEmp,"payrollyear","2022", 1)>
<cfset tmp = querySetCell(qExpEmp,"amount","-216.81", 1)>
<cfset tmp = querySetCell(qExpEmp,"xhour","-8.25", 1)>
<cfset tmp = querySetCell(qExpEmp,"xday","1", 1)>
<cfset tmp = querySetCell(qExpEmp,"price","26.2800", 1)>
<cfset tmp = querySetCell(qExpEmp,"clientnr","5", 1)>

<!--- Second row --->
<cfset tmp = querySetCell(qExpEmp,"payrollyear","2022", 2)>
<cfset tmp = querySetCell(qExpEmp,"amount","216.3975", 2)>
<cfset tmp = querySetCell(qExpEmp,"xhour","8.25", 2)>
<cfset tmp = querySetCell(qExpEmp,"xday","1", 2)>
<cfset tmp = querySetCell(qExpEmp,"price","26.2300", 2)>
<cfset tmp = querySetCell(qExpEmp,"clientnr","5", 2)>

<cfdump var="#qExpEmp#">

<cfquery name="qoq" dbtype="query">
SELECT payrollyear
,SUM(amount) as amount
,SUM(xhour) as vhour
,SUM(xday) as vday
,SUM(price) as totprice
FROM qExpEmp
WHERE payrollyear = 2022
AND clientnr = '5' 
GROUP BY payrollyear
</cfquery>

<cfdump var="#qoq#">

My system:
Lucee 5.4.2.17
Windows 10 Professional

cubortea · August 1, 2023, 4:01am

I know the IFNULL(clientnr,‘’) = ‘5’ does not make sense; this is only my test script, which is the QoQ, which has the wrong result. In lucee 5.3.xx, the result is correct, but since 5.4.2, the result is not correct anymore.

The point is that if we have IFNULL in the where clause, the SUM result will be wrong.

T_Mario · August 1, 2023, 7:53am

Yes the code can be more effective without ifnull syntax.
But the issue here is the usage of IFNULL in the QoQ causing the float/decimal type change into integer. so the calculation is incorrect.

In this sample the value cannot be null because it’s assigned from lucee queryNew. but if we get from the db directly and needs null or empty value, it’s possible to have null value. Then in this case the IFNULL syntax is needed.

If the IFNULL is not allowed in QoQ in lucee why it’s not producing error like other not allowed SQL syntax? instead it executed incorrectly.

BK_BK · August 2, 2023, 9:11am

@T_Mario : If the IFNULL is not allowed in QoQ in lucee why it’s not producing error like other not allowed SQL syntax? instead it executed incorrectly.

Like you, I think that that is the question to be asked here.

BK_BK · August 2, 2023, 4:50pm

I have looked into this some more. In particular, how query-of-query is implemented in Lucee. That led me to these seminal posts by Brad Wood:

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

Lucee’s query-of-query is based on HSQLDB (HyperSQL DataBase). IfNull() is a function in HSQLDB, So, too, is isNull(), which works as expected.

Therefore, it is clear that ifNull() is a legitimate query-of-query function in Lucee. Hence what you, @cubortea , have discovered is likely to be a bug.

In fact, you can further confirm the bug as follows. In the code I gave before, bring back the clause GROUP BY payrollyear, price, clientnr in the query-of-query… Then run each of the following 8 scenarios:

clientnr in second row is set to ‘5’, qoq where-clause ends with AND ifNULL(clientnr,'') = '5';
clientnr in second row is set to ‘5’, qoq where-clause ends with AND isNULL(clientnr,'') = '5';
clientnr in second row is set to null, qoq where-clause ends with AND ifNULL(clientnr,'') = '5';
clientnr in second row is set to null, qoq where-clause ends with AND isNULL(clientnr,'') = '5';
clientnr in second row is set to ‘5’, qoq where-clause ends with AND ifNULL(clientnr,'5') = '5';
clientnr in second row is set to ‘5’, qoq where-clause ends with AND isNULL(clientnr,'5') = '5';
clientnr in second row is set to null, qoq where-clause ends with AND ifNULL(clientnr,'5') = '5';
clientnr in second row is set to null, qoq where-clause ends with AND isNULL(clientnr,'5') = '5'.

Apparently, isNull() works as expected, but ifNull() does not.

Zackster · August 3, 2023, 8:23am

but having group by columns not in the select list isn’t normally allowed (try it with mysql etc?), native QoQ will bomb out on that as an error and then you fallback to hsqldb (which allows it)

is addition, ifNull simply isn’t supported by native QoQ, it’s falling back to hsqldb

as you can see by the datasource in you dump it out

Datasource: _queryofquerydb