opened 03:33PM - 12 Oct 22 UTC
Area: Module
<!--- Please be aware that as of the 31st January 2022 we no longer support 7.10….x.
New issues referring to 7.10.x will only be valid if applicable to 7.12.x and above.
If your issue is still applicable in 7.12.x, please create the issue following the template below -->
#### Issue
Database error in subpanels with the error: ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause. The issue pertains to use the Table name in the sort clause. This seemed to work fine until today (Oct. 10. 2022). So must have been a recent update in mySQL that no longer permits this syntax. I was able to get the subpanel to display contacts again by sorting on a different column. Example below (screenshots) is in the Cases module and Contacts Subpanel. However, this issue is throughout SuiteCRM and not just this module, and not just contacts.
When I sort on "Accounts" it works:
#### Expected Behavior
The contact in the subpanel should display without error.
#### Actual Behavior
Contacts in subpanel display with database error.
```Wed Oct 12 14:08:16 2022 [16187][1][FATAL] Error retrieving Case list: Query Failed: (SELECT contacts.id , contacts.first_name , contacts.last_name , contacts.salutation , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as name, ' ' account_name , ' ' account_id , contacts.phone_work , contacts.assigned_user_id , 'contacts' panel_name FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c INNER JOIN contacts_cases ON contacts.id=contacts_cases.contact_id AND contacts_cases.case_id='9e5568fd-3fd7-5130-0a83-6345f29aafeb' AND contacts_cases.deleted=0
where contacts.deleted=0) ORDER BY contacts.last_name, contacts.first_name asc LIMIT 0,10: MySQL error 1250: Table 'contacts' from one of the SELECTs cannot be used in global ORDER clause ```
#### Possible Fix
As a work around, I can sort by another column to avoid the error.
The fix is to remove the table name from "sort by" I think, but this is throughout all the subpanels not sure where to start. the ORDERBY contacts.last_name I think is what's causing the problem and should be just ORDERBY last_name.
#### Steps to Reproduce
1. Go into any module that has Contacts subpanel, see it doesn't work. Also Project Tasks have same issue so its more than just contacts. See screen shots provided.
#### Context
This is a huge issue, I imagine affects every installation on MySQL 8.0.31
#### Your Environment
* SuiteCRM Version used: 7.12.7
* Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Chrome
* Environment name and version (e.g. MySQL, PHP 7): MySQL 8.0.31
* Operating System and version (e.g Ubuntu 16.04): WHM/Cpanel, Centoi v7.9.2009