Workflow function aggregation error

[radu]

I noticed when i have a condition in the aggregation function the whole function fails. For example i have the following function:

Code:

aggregation('count','SalesOrder','pl_grand_total','[sostatus,e,Approved,or],[sostatus,e,Delivered,or]')

which should count the sales orders that have the status 'Approved' or 'Delivered'. If I remove the condition from the expression, the function retunrs the correct count number of existing SO's but if i put any condition it craps out and return empty. This exact expression is in use on a older install and it works fine.
The log inspection reveals some clues. Here is what the query and result looks like on a new install:


query select count(vtiger_salesorder.pl_grand_total) as aggop  from vtiger_salesorder    inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid    left outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid    left outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid    left outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid    left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid    left join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid     where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = 25326 ORDER BY vtiger_crmentity.createdtime DESC and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Approved') OR vtiger_salesorder.sostatus = 'Approved')  or ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Delivered') OR vtiger_salesorder.sostatus = 'Delivered')  AND vtiger_salesorder.salesorderid > 0)

and it fails with:

Code:

DB DB >ADODB error  Query Failed:select count(vtiger_salesorder.pl_grand_total) as aggop  from vtiger_salesorder    inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid    left outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid    left outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid    left outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid    left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid    left join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid     where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = 25326 ORDER BY vtiger_crmentity.createdtime DESC and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Approved') OR vtiger_salesorder.sostatus = 'Approved')  or ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Delivered') OR vtiger_salesorder.sostatus = 'Delivered')  AND vtiger_salesorder.salesorderid > 0)::->[1064]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
se' at line 1

And this is how the query looks on the older system where is working ok:

Code:

select count(vtiger_salesorder.pl_grand_total) as aggop  from vtiger_salesorder    inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid    left outer join vtiger_quotes on vtiger_quotes.quoteid=vtiger_salesorder.quoteid    left outer join vtiger_account on vtiger_account.accountid=vtiger_salesorder.accountid    left outer join vtiger_potential on vtiger_potential.potentialid=vtiger_salesorder.potentialid    left join vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid    left join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid     where vtiger_crmentity.deleted=0 and vtiger_potential.potentialid = 519508 and (vtiger_crmentity.deleted=0 AND ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Approved') OR vtiger_salesorder.sostatus = 'Approved')  or ( vtiger_salesorder.sostatus IN (
select translation_key
from vtiger_cbtranslation
where locale="en_us" and forpicklist="SalesOrder::sostatus" and i18n = 'Delivered') OR vtiger_salesorder.sostatus = 'Delivered')  AND vtiger_salesorder.salesorderid > 0)

I have marked with red the difference between the two which is ORDER BY vtiger_crmentity.createdtime DESC and
.
The failed query gives syntax errors when launched in the sql engine. However if I remove the red part which does not used to be present before, the engine executes the query just fine. I am not well versed in sql but from my point of view, that select query has logic with or without the 'red addition'. The syntax itself, i tried to count the parentheses and other symbols to see if i can spot any obvious mistake(with my limited knowledge of the language), i could not find any but the engine will not execute if that 'sorting/order' is present. If i remove it and launch that query in the engine, it runs ok.
Maybe someone can get a clue about what is going on. Could be something with my own install.