Error 500 : Expression #1 of ORDER BY clause is not in SELECT list,

[enricorossa]

hi, I have updated to version 5.7.11 but when I do a custom search I get this error :

Espo.ERROR: (HY000) SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1364696_1.emailAddresses.lower' which is not in SELECT list; this is incompatible with DISTINCT;

[esforim]

Can't help with my knowledge. But I think this should be in Installation and Upgrade Help

[enricorossa]

Thanks for the reply, would you have a link to that guide?
where does it explain how to solve the problem?

[esforim]

I mean it should be posted in this section: https://forum.espocrm.com/forum/inst...d-upgrade-help

You might get more help that way. As as per normal, remember to provide server environment, version and error logs.

[Maximus]

Hello,
Please provide the steps with screenshots to reproduce this issue.
From what version you upgraded?

[robson]

jest do this (works on Ubuntu 18):

sudo nano /etc/mysql/my.cnf >> add sql-mode="STRICT_ALL_TABLES"
sudo reboot

#espoo team, you are doing great job but you could fix this thing in code, looks kind of obvious...

General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'crm.lead.created_at' which is not in SELECT list; this is incompatible with DISTINCT; POST /api/v1/TargetList/5eb18809b2974a957/leads; line: 564, file: application/Espo/ORM/DB/Mapper.php strict

source: https://github.com/Piwigo/Piwigo/issues/376

[yuri]

Try to change order by from 'email' to another field. You must have set it in Entity Manager as a default order.

Here's the fix: https://github.com/espocrm/espocrm/issues/1695

But it depends on the fix made in 5.8.3: https://github.com/espocrm/espocrm/issues/1622

[yuri]

> #espoo team, you are doing great job but you could fix this thing in code, looks kind of obvious...

It's not such obvious as it may seem. Nobody reported it before. It seems it appeared recently.

Here's the fix: https://github.com/espocrm/espocrm/c...c0a3dde3fa6ea6

[manny18]

Upgraded from 5.8.5 to 5.9.0. Still has the same SQL error when do a global search.

I think it is caused by this ORDER BY clause
ROUND(
(
MATCH (contact.first_name, contact.last_name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) / '5'
)
) DESC
as it does not exist in SELECT.


If I only select 1 field in the entity manager for search and enable fulltext , then no error.
If I disable fulltext search, then no error.


Here is the full sql in my log:
(
SELECT
DISTINCT contact.id AS `id`,
TRIM(
CONCAT(
IFNULL(contact.first_name, ''),
' ',
IFNULL(contact.last_name, '')
)
) AS `name`,
'Contact' AS `entityType`,
MATCH (contact.first_name, contact.last_name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) AS `_relevance`,
contact.first_name AS `firstName`,
contact.last_name AS `lastName`
FROM
`contact`
LEFT JOIN `entity_team` AS `teamsAccessMiddle` ON contact.id = teamsAccessMiddle.entity_id
AND teamsAccessMiddle.deleted = '0'
AND teamsAccessMiddle.entity_type = 'Contact'
LEFT JOIN `team` AS `teamsAccess` ON teamsAccess.id = teamsAccessMiddle.team_id
AND teamsAccess.deleted = '0'
WHERE
(
(
teamsAccess.id IN ('5ebb93bc33ccb2e49')
OR contact.assigned_user_id = '5ebb928c865ea6872'
)
)
AND (
(
contact.id IN (
SELECT
entity_id
FROM
entity_email_address
JOIN email_address ON email_address.id = entity_email_address.email_address_id
WHERE
entity_email_address.deleted = 0
AND entity_email_address.entity_type = 'Contact'
AND email_address.deleted = 0
AND email_address.lower LIKE 'mouse%'
)
OR 0
OR contact.id IN (
SELECT
entity_id
FROM
entity_phone_number
JOIN phone_number ON phone_number.id = entity_phone_number.phone_number_id
WHERE
entity_phone_number.deleted = 0
AND entity_phone_number.entity_type = 'Contact'
AND phone_number.deleted = 0
AND phone_number.name LIKE 'mouse%'
)
OR (
MATCH (contact.first_name, contact.last_name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE)
)
)
)
AND contact.deleted = '0'
ORDER BY
ROUND(
(
MATCH (contact.first_name, contact.last_name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) / '5'
)
) DESC,
MATCH (contact.first_name, contact.last_name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) DESC,
contact.first_name ASC,
contact.last_name
LIMIT
0, 11
)
UNION ALL
(
SELECT
DISTINCT phone_call.id AS `id`,
phone_call.name AS `name`,
'PhoneCall' AS `entityType`,
MATCH (phone_call.name, phone_call.call_notes) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) AS `_relevance`,
'' AS `firstName`,
'' AS `lastName`
FROM
`phone_call`
LEFT JOIN `entity_team` AS `teamsAccessMiddle` ON phone_call.id = teamsAccessMiddle.entity_id
AND teamsAccessMiddle.deleted = '0'
AND teamsAccessMiddle.entity_type = 'PhoneCall'
LEFT JOIN `team` AS `teamsAccess` ON teamsAccess.id = teamsAccessMiddle.team_id
AND teamsAccess.deleted = '0'
WHERE
(
(
teamsAccess.id IN ('5ebb93bc33ccb2e49')
OR phone_call.assigned_user_id = '5ebb928c865ea6872'
)
)
AND (
(
(
MATCH (phone_call.name, phone_call.call_notes) AGAINST ('mouse' IN NATURAL LANGUAGE MODE)
)
)
)
AND phone_call.deleted = '0'
ORDER BY
ROUND(
(
MATCH (phone_call.name, phone_call.call_notes) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) / '5'
)
) DESC,
MATCH (phone_call.name, phone_call.call_notes) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) DESC,
phone_call.name ASC
LIMIT
0, 11
)
UNION ALL
(
SELECT
DISTINCT case.id AS `id`,
case.name AS `name`,
'Case' AS `entityType`,
MATCH (case.name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) AS `_relevance`,
'' AS `firstName`,
'' AS `lastName`
FROM
`case`
LEFT JOIN `entity_team` AS `teamsAccessMiddle` ON case.id = teamsAccessMiddle.entity_id
AND teamsAccessMiddle.deleted = '0'
AND teamsAccessMiddle.entity_type = 'Case'
LEFT JOIN `team` AS `teamsAccess` ON teamsAccess.id = teamsAccessMiddle.team_id
AND teamsAccess.deleted = '0'
WHERE
(
(
teamsAccess.id IN ('5ebb93bc33ccb2e49')
OR case.assigned_user_id = '5ebb928c865ea6872'
)
)
AND (
(
(
MATCH (case.name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE)
)
)
)
AND case.deleted = '0'
ORDER BY
ROUND(
(
MATCH (case.name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) / '5'
)
) DESC,
MATCH (case.name) AGAINST ('mouse' IN NATURAL LANGUAGE MODE) DESC,
case.name ASC
LIMIT
0, 11
)
ORDER BY
4 DESC,
FIELD(entityType, 'Contact', 'PhoneCall', 'Case'),
name
LIMIT
0, 11

[yuri]

This fix should help: https://github.com/espocrm/espocrm/c...f88c3945e8f838 ?