Module report appear error generate query when upgrade 7.9 -> 7.11.x

[duongnk]

I started my system with version 7.9.0. I have upgrade 7.10 in 04/2019 and 7.11.3 in 05/2019.

And when i check some simple report (Module report), normal user can not see any result. All report have relationship field all die with normal user, admin still ok

With report Opportunity (Opp Name, Account Name, … some Opp fields), Log file appear error with query:

Query Failed: SELECT opportunities.id AS ‘opportunities_id’, opportunities.name AS ‘Opportunity_Name0’, opportunities_cstm.sales_stage_new_c AS ‘Sales_stage1’, opportunities:accounts.id AS ‘opportunities:accounts_id’, opportunities:accounts.name AS ‘Customer_Name2’, opportunities.opportunity_type AS ‘Opportunity_Type3’, opportunities.currency_id AS ‘opportunities_currency_id’, opportunities.amount AS ‘Opportunity_Amount4’, opportunities.assigned_user_id AS ‘Salesman5’, opportunities.probability AS ‘Probability_(%)6’ FROM opportunities LEFT JOIN opportunities_cstm opportunities_cstm ON opportunities.id = opportunities_cstm.id_c LEFT JOIN accounts_opportunities opportunities|opportunities:accounts ON opportunities.id=opportunities|opportunities:accounts.opportunity_id AND opportunities|opportunities:accounts.deleted=0

LEFT JOIN accounts opportunities:accounts ON opportunities:accounts.id=opportunities|opportunities:accounts.account_id AND opportunities:accounts.deleted=0
AND ( accounts.assigned_user_id =‘b1c16b21-c717-7d66-7795-5c948a1488b4’ or EXISTS (SELECT 1
FROM securitygroups secg
INNER JOIN securitygroups_users secu
ON secg.id = secu.securitygroup_id
AND secu.deleted = 0
AND secu.user_id = ‘b1c16b21-c717-7d66-7795-5c948a1488b4’
INNER JOIN securitygroups_records secr
ON secg.id = secr.securitygroup_id
AND secr.deleted = 0
AND secr.module = ‘Accounts’
WHERE secr.record_id = opportunities:accounts.id
AND secg.deleted = 0) ) WHERE ( opportunities.sales_stage != ‘have_bid_BTB’ AND opportunities.assigned_user_id != ‘’ ) AND opportunities.deleted = 0 AND ( opportunities.assigned_user_id =‘b1c16b21-c717-7d66-7795-5c948a1488b4’ or EXISTS (SELECT 1
FROM securitygroups secg
INNER JOIN securitygroups_users secu
ON secg.id = secu.securitygroup_id
AND secu.deleted = 0
AND secu.user_id = ‘b1c16b21-c717-7d66-7795-5c948a1488b4’
INNER JOIN securitygroups_records secr
ON secg.id = secr.securitygroup_id
AND secr.deleted = 0
AND secr.module = ‘Opportunities’
WHERE secr.record_id = opportunities.id
AND secg.deleted = 0) ) GROUP BY opportunities.name, opportunities.id, opportunities_cstm.sales_stage_new_c, opportunities:accounts.id, opportunities:accounts.name, opportunities.opportunity_type, opportunities.currency_id, opportunities.amount, opportunities.assigned_user_id, opportunities.probability LIMIT 0,20: MySQL error 1054: Unknown column ‘accounts.assigned_user_id’ in ‘on clause’

I think error in query generated: [quote]"LEFT JOIN [color=red]accounts opportunities:accounts[/color] ON opportunities:accounts.id=opportunities|opportunities:accounts.account_id AND opportunities:accounts.deleted=0
AND ( [color=red]accounts.assigned_user_id[/color] =‘b1c16b21-c717-7d66-7795-5c948a1488b4’ or "[quote]

“account” need change to allias opportunities:accounts . If i change like that, query can success to get result

Someone can help me resolve it.

[duongnk]

With report Tasks (related Opportunities), same error:

LEFT JOIN [color=red]opportunities tasks:opportunities[/color] ON tasks.parent_id=tasks:opportunities.id AND tasks:opportunities.deleted=0
AND tasks.parent_type = ‘Opportunities’
AND ( [color=red]opportunities.assigned_user_id[/color] =‘b1c16b21-c717-7d66-7795-5c948a1488b4’ or EXISTS (SELECT 1

=> MySQL error 1054: Unknown column ‘opportunities.assigned_user_id’ in ‘on clause’

[duongnk]

Help me pls!

All user non-admin can not use report. I found if in Role, I change permission “List” from Ower/Group to All:

Report can work. But I can not do that, each module must set permission List is Ower/Group.

Ex: With report Tasks (relate Opportunities (Opp Name, Account Name)), if i set permission List Opportunities to All, this report can work well.

I built new system with version 7.11.4, still error. Its not bug in upgrade.