Database Failures in Subpanels throughout SuiteCRM (MySQL Error)

[pstevens71]

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

[SuiteBot]

This issue has been mentioned on SuiteCRM. There might be relevant details there:

https://community.suitecrm.com/t/mysql-error-1250-table-contacts-from-one-of-the-selects-cannot-be-used-in-global-order-clause/86551/12

[SuiteBot]

This issue has been mentioned on SuiteCRM. There might be relevant details there:

https://community.suitecrm.com/t/weird-behavior-sorting-on-subpanels/86554/5

[lefred]

Hello,

I am working for MySQL, with 8.0.31, we have made some changes in the optimizer for a better handling of parenthesis to follow even better the SQL standards.

So in case of the following query:

(SELECT table1.LastInvoiceDate, table2.LastInvoiceDate from table1 inner join table2 using (id) ) 
ORDER BY table1.LastInvoiceDate ;
ERROR: 1250 (42000): Table 'table1' from one of the SELECTs cannot be used in global ORDER clause

Now the ORDER BY is related to the content of the query in the parenthesis and this can be used:

if the order by field is unique in the query, we just remove the table name reference like this:

SELECT table1.LastInvoiceDate, table2.id from table1 inner join table2 using (id)) ORDER BY id ;
+-----------------+----+
| LastInvoiceDate | id |
+-----------------+----+
| 2020-01-01      |  1 |
| 2021-04-21      |  2 |
| 2022-03-02      |  3 |
+-----------------+----+

if the column name is identical but from 2 different tables (like my first query), then you need to alias it or use the column's number like this :

(SELECT table1.LastInvoiceDate tb1LastInvoiceDate, table2.LastInvoiceDate tbl2LastInvoiceDate 
from table1 inner join table2 using (id)) ORDER BY tb1LastInvoiceDate;
+--------------------+---------------------+
| tb1LastInvoiceDate | tbl2LastInvoiceDate |
+--------------------+---------------------+
| 2020-01-01         | 2022-05-01          |
| 2021-04-21         | 2022-05-21          |
| 2022-03-02         | 2022-05-02          |
+--------------------+---------------------+

or

(SELECT table1.LastInvoiceDate, table2.LastInvoiceDate from table1 inner join table2 using (id)) ORDER BY 1 ;
+-----------------+-----------------+
| LastInvoiceDate | LastInvoiceDate |
+-----------------+-----------------+
| 2020-01-01      | 2022-05-01      |
| 2021-04-21      | 2022-05-21      |
| 2022-03-02      | 2022-05-02      |
+-----------------+-----------------+

Without the parenthesis it works as previously:

 SELECT table1.LastInvoiceDate, table2.LastInvoiceDate from table1 
inner join table2 using (id) ORDER BY table1.LastInvoiceDate ;
+-----------------+-----------------+
| LastInvoiceDate | LastInvoiceDate |
+-----------------+-----------------+
| 2020-01-01      | 2022-05-01      |
| 2021-04-21      | 2022-05-21      |
| 2022-03-02      | 2022-05-02      |
+-----------------+-----------------+

Cheers,

[lowbouncerate]

Hi,
Where I can update the Query - I am not a Pro User of SuiteCRM

[lefred]

I've submitted a bug in our system too.

[lowbouncerate]

you can try to set both values as
'sort_order' => false,
'sort_by' => false,

it is working for me.

[iias12]

you can try to set both values as 'sort_order' => false, 'sort_by' => false,

it is working for me.

Where did you change these settings?

[lowbouncerate]

I am using 7.10.x and we are using custom panels
custom/modules/Leads/Ext/Layoutdefs

[hbartel]

Has anyone been able to trace down what file is building this SQL query?
The fix should be as simple as removing the outer ( ) from the query:
(SELECT ... contacts.deleted=0) ORDER BY ...

But I have not been able to determine what file is building the query.

[pgorod]

You can ask PHP to give you a full stack trace on that error, this should be enough to determine where it's coming from.

[pstevens71]

I set level to ERROR and Enable stack trace to checked and it doesn't give me any further info. This is the output.....

Thu Oct 20 14:12:58 2022 [20457][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='6a66b4d6-34a3-ef97-0de0-634878effa1e' 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
Thu Oct 20 14:12:58 2022 [20457][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Thu Oct 20 14:12:59 2022 [20457][1][ERROR] Unable to find relationship emails_email_templates
Thu Oct 20 14:12:59 2022 [20457][1][ERROR] Unable to find relationship emails_email_templates
Thu Oct 20 14:13:54 2022 [20629][1][FATAL] Mysqli_query failed.
Thu Oct 20 14:13:54 2022 [20629][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='6a66b4d6-34a3-ef97-0de0-634878effa1e' 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
Thu Oct 20 14:13:54 2022 [20629][1][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

There's nothing in the suitecrm.log

[lefred]

I'm not a SuiteCRM developer (or even user), but to me it seems the stack trace is clear a refers to

SuiteCRM/include/ListView/ListView.php

Line 1077 in d57e913

public function processUnionBeans($sugarbean, $subpanel_def, $html_var = 'CELL', $countOnly = false)

[hbartel]

Found it!

SuiteCRM/data/SugarBean.php

Line 930 in 7b6ac1b

$query = '(' . $query . ')';

To fix comment out the line:
//$query = '(' . $query . ')';