8.2.2 mysql error in clause ORDER

Hello,
when I import a mail and I use “related to” linking that e-mail to an opportunity, apparently it works.
Then when I open again the imported mail, go down to the bottom, I see in “attachment” and “opportunity” the database failure error.

The fatl error is:

[FATAL] Error retrieving Email list: Query Failed: (SELECT opportunities.id , opportunities_cstm.stadio_pipeline_c, opportunities_cstm.pipeline_list_c, opportunities.name , ’ ’ account_name , ’ ’ account_id , opportunities.currency_id , opportunities.assigned_user_id , ‘opportunities’ panel_name FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c INNER JOIN emails_beans ON opportunities.id=emails_beans.bean_id AND emails_beans.email_id=‘dd125d6b-ce31-e704-af0c-63bc11bf74a8’ AND emails_beans.deleted=0
AND emails_beans.bean_module = ‘Opportunities’
where opportunities.deleted=0) ORDER BY opportunities.name asc LIMIT 0,10: MySQL error 1250: Table ‘opportunities’ from one of the SELECTs cannot be used in global ORDER clause

The problem is within the Order clause; Indeed removing the order and launching the query within mysqli the query is ok.

My config is mysql server 8.0.31-0ubuntu0.20.04.2 and php 7.4

Also when relating the mail to an account, even if the GUI don’t give any error, in the log appears many FATAL:

Error retrieving Email list: Query Failed: (SELECT notes.id , notes.contact_id , notes.name , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,’’),’ ‘,IFNULL(contacts.last_name,’’)))) contact_name , contacts.assigned_user_id contact_name_owner , ‘Contacts’ contact_name_mod, notes.date_modified , notes.filename , notes.assigned_user_id , ‘notes’ panel_name FROM notes LEFT JOIN contacts contacts ON notes.contact_id=contacts.id AND contacts.deleted=0

AND contacts.deleted=0 INNER JOIN emails notes_rel ON notes.parent_id=notes_rel.id AND notes_rel.deleted=0

where ( notes.parent_id=‘936e16da-30b9-0e5e-0f27-63bc1871e962’) AND notes.deleted=0) ORDER BY notes.name asc LIMIT 0,10: MySQL error 1250: Table ‘notes’ from one of the SELECTs cannot be used in global ORDER clause
Mon Jan 9 14:37:24 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Mon Jan 9 14:37:24 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] Mysqli_query failed.
Mon Jan 9 14:37:24 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] Error retrieving Email list: Query Failed: (SELECT accounts.id , accounts.name , accounts.billing_address_city , accounts.phone_office , accounts.assigned_user_id , ‘accounts’ panel_name FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c INNER JOIN emails_beans ON accounts.id=emails_beans.bean_id AND emails_beans.email_id=‘936e16da-30b9-0e5e-0f27-63bc1871e962’ AND emails_beans.deleted=0
AND emails_beans.bean_module = ‘Accounts’
where accounts.deleted=0) ORDER BY accounts.name asc LIMIT 0,10: MySQL error 1250: Table ‘accounts’ from one of the SELECTs cannot be used in global ORDER clause
Mon Jan 9 14:37:24 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Mon Jan 9 14:37:25 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] Mysqli_query failed.
Mon Jan 9 14:37:25 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] Error retrieving Email list: Query Failed: (SELECT notes.id , notes.contact_id , notes.name , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,’’),’ ‘,IFNULL(contacts.last_name,’’)))) contact_name , contacts.assigned_user_id contact_name_owner , ‘Contacts’ contact_name_mod, notes.date_modified , notes.filename , notes.assigned_user_id , ‘notes’ panel_name FROM notes LEFT JOIN contacts contacts ON notes.contact_id=contacts.id AND contacts.deleted=0

AND contacts.deleted=0 INNER JOIN emails notes_rel ON notes.parent_id=notes_rel.id AND notes_rel.deleted=0

where ( notes.parent_id=‘936e16da-30b9-0e5e-0f27-63bc1871e962’) AND notes.deleted=0) ORDER BY notes.name asc LIMIT 0,10: MySQL error 1250: Table ‘notes’ from one of the SELECTs cannot be used in global ORDER clause
Mon Jan 9 14:37:25 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.
Mon Jan 9 14:37:25 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] Mysqli_query failed.
Mon Jan 9 14:37:25 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] Error retrieving Email list: Query Failed: (SELECT accounts.id , accounts.name , accounts.billing_address_city , accounts.phone_office , accounts.assigned_user_id , ‘accounts’ panel_name FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c INNER JOIN emails_beans ON accounts.id=emails_beans.bean_id AND emails_beans.email_id=‘936e16da-30b9-0e5e-0f27-63bc1871e962’ AND emails_beans.deleted=0
AND emails_beans.bean_module = ‘Accounts’
where accounts.deleted=0) ORDER BY accounts.name asc LIMIT 0,10: MySQL error 1250: Table ‘accounts’ from one of the SELECTs cannot be used in global ORDER clause
Mon Jan 9 14:37:25 2023 [1448700][59cba452-84e3-e974-cc30-5febbe208a92][FATAL] [ListView::processUnionBeans] . Database failure. Please refer to suitecrm.log for details.

Also, removing the parentheses () it works!

Apparently there is a fix.

In any case, for 8.2.2 lines are wrong: the correct lines are
939 and 885