Reports causing fatal sql error under certain conditions #4949

New issue

Open

opened 2026-02-20 16:26:47 -05:00 by deekerman · 0 comments

deekerman commented

2026-02-20 16:26:47 -05:00

Owner

Originally created by @ojs87 on GitHub (Feb 8, 2023).

Issue

When the Filter User List security groups setting is checked, Reports containing User related fields such as "Cases: Assigned To User - First Name" are returning no results due to an sql error. This only happens for non-admin users.

Query Failed: SELECT `cases`.assigned_user_id AS 'Assigned_to0', `cases`.case_number AS 'Number1', `cases:assigned_user_link`.last_name AS 'Last_Name2' FROM `cases` LEFT JOIN users `cases:assigned_user_link` ON `cases`.assigned_user_id=`cases:assigned_user_link`.id AND `cases:assigned_user_link`.deleted=0

 AND  users.id in (
            select sec.user_id from securitygroups_users sec
            inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0
                and secu.user_id = 'dbbc549a-4s88-37e7-cdde-627928601837'
            where sec.deleted = 0
        ) WHERE ( `cases`.type = 'Project' AND (`cases`.status = 'Open_Assigned') ) AND  cases.deleted = 0  AND  ( cases.assigned_user_id ='dbbc549a-4s88-37e7-cdde-627928601837'  or  cases.id in (
                    select secr.record_id from securitygroups secg
                    inner join securitygroups_users secu on secg.id = secu.securitygroup_id and secu.deleted = 0
                        and secu.user_id = 'dbbc549a-4s88-37e7-cdde-627928601837'
                    inner join securitygroups_records secr on secg.id = secr.securitygroup_id and secr.deleted = 0
                        and secr.module = 'Cases'
                    where secg.deleted = 0
                ))  GROUP BY `cases`.assigned_user_id, `cases`.case_number, `cases:assigned_user_link`.last_name LIMIT 0,20: MySQL error 1054: Unknown column 'users.id' in 'IN/ALL/ANY subquery'

Expected Behavior

The report previously returned results without the User related fields due to ACL permissions, but the rest of the report was intact.

Actual Behavior

No results are returned.

Possible Fix

A few possible fixes:

The new function in 7.13, buildAccessWhere(), should contain an exception for when it is called by Reports to prevent the sql error.

build_report_access_query() should revert to the previous iteration without calling buildAccessWhere()

Steps to Reproduce

Check Filter User List in Admin->Security Group Settings
Create a Report for Cases and add a field from Cases:Assigned To User
Login as a non-admin User
Attempt to run the report

Context

Some reports that previously worked(at least partially for non-admin users) no longer work.

Your Environment

SuiteCRM Version used: SuiteCRM 7.13.1
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 109
Environment name and version (e.g. MySQL, PHP 7): PHP 7.4
Operating System and version (e.g Ubuntu 16.04): Ubuntu 20.04

Originally created by @ojs87 on GitHub (Feb 8, 2023).     #### Issue When the Filter User List security groups setting is checked, Reports containing User related fields such as "Cases: Assigned To User - First Name" are returning no results due to an sql error. This only happens for non-admin users. ```` Query Failed: SELECT `cases`.assigned_user_id AS 'Assigned_to0', `cases`.case_number AS 'Number1', `cases:assigned_user_link`.last_name AS 'Last_Name2' FROM `cases` LEFT JOIN users `cases:assigned_user_link` ON `cases`.assigned_user_id=`cases:assigned_user_link`.id AND `cases:assigned_user_link`.deleted=0 AND users.id in ( select sec.user_id from securitygroups_users sec inner join securitygroups_users secu on sec.securitygroup_id = secu.securitygroup_id and secu.deleted = 0 and secu.user_id = 'dbbc549a-4s88-37e7-cdde-627928601837' where sec.deleted = 0 ) WHERE ( `cases`.type = 'Project' AND (`cases`.status = 'Open_Assigned') ) AND cases.deleted = 0 AND ( cases.assigned_user_id ='dbbc549a-4s88-37e7-cdde-627928601837' or cases.id in ( select secr.record_id from securitygroups secg inner join securitygroups_users secu on secg.id = secu.securitygroup_id and secu.deleted = 0 and secu.user_id = 'dbbc549a-4s88-37e7-cdde-627928601837' inner join securitygroups_records secr on secg.id = secr.securitygroup_id and secr.deleted = 0 and secr.module = 'Cases' where secg.deleted = 0 )) GROUP BY `cases`.assigned_user_id, `cases`.case_number, `cases:assigned_user_link`.last_name LIMIT 0,20: MySQL error 1054: Unknown column 'users.id' in 'IN/ALL/ANY subquery' ```` #### Expected Behavior The report previously returned results without the User related fields due to ACL permissions, but the rest of the report was intact. #### Actual Behavior No results are returned. #### Possible Fix A few possible fixes: The new function in 7.13, buildAccessWhere(), should contain an exception for when it is called by Reports to prevent the sql error. build_report_access_query() should revert to the previous iteration without calling buildAccessWhere() #### Steps to Reproduce 1. Check Filter User List in Admin->Security Group Settings 2. Create a Report for Cases and add a field from Cases:Assigned To User 3. Login as a non-admin User 4. Attempt to run the report #### Context Some reports that previously worked(at least partially for non-admin users) no longer work. #### Your Environment  * SuiteCRM Version used: SuiteCRM 7.13.1 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 109 * Environment name and version (e.g. MySQL, PHP 7): PHP 7.4 * Operating System and version (e.g Ubuntu 16.04): Ubuntu 20.04