Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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;

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

    Comment


    • #3

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

      Comment


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

      Comment


      • #5
        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

        Comment


        • #6
          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
          Last edited by yuri; 05-06-2020, 08:06 AM.

          Comment


          • #7
            > #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
            Last edited by yuri; 05-07-2020, 05:42 AM.

            Comment


            • #8
              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

              Comment


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

                Comment

                Working...
                X