SQL error when a contact detail view is opened

[Harshad]

In my v8 version instance, there is an error logged when I open a contact detail view. No error is on the UI but in the log file (suitecrm.log).

SELECT  emails.`date_sent_received`   FROM notes   
LEFT JOIN  contacts contacts ON notes.contact_id=contacts.id AND contacts.deleted=0 AND contacts.deleted=0  
LEFT JOIN  users jt1 ON notes.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 
INNER JOIN  contacts notes_rel ON notes.contact_id=notes_rel.id AND notes_rel.deleted=0
where ( notes.contact_id='768607bc-83ad-11ef-8040-78726430b17c') AND notes.deleted=0   
ORDER BY  emails.`date_sent_received` DESC LIMIT 1

MySQL error 1054: Unknown column ‘emails.date_sent_received’ in ‘field list’. Has anyone faced the same issue in version 8. Thanks!

[pgr]

That “FROM” is clearly wrong. It seems to be a part of the ContactsLastTouchPoint statistic… but I don’t know why it’s broken…

[Harshad]

It’s in 8.7 fresh installation.

[rsp]

@Harshad

``date_sent_received

Maybe you don’t need those quotes. Check where is code for it.

[suitecrm_developer]

Hi,
The issues seems to be in subpanel definition in Notes Module. Can you please check subpanel definition file

modules/Notes/metadata/subpanels/ForHistory.php

. The Query has no join to EMAILS table and the select statement and order by statement is referring to a column belonging to emails table.

[Harshad]

The issue is fixed by changing the indexes of $queries in ContactLastTouchPoint in 8.7 similar to 8.6.2.

$parts = $queries[5];
.....
$parts = $queries[6];

There were indexes difference in this file in 8.6.2 and 8.7 . The $queries list has different indexes 8.7 and 8.6.2 in ContactLastTouchPoint.php but the order of subpanels in subpaneldefs.php in two versions is not different.

8.7
Core\core\modules\Contacts\Statistics\ContactLastTouchPoint.php
$queries[4] & $queries[5]

        $parts = $queries[4];
        $parts['select'] = 'SELECT  emails.`date_sent_received` ';
        $parts['order_by'] = ' ORDER BY  emails.`date_sent_received` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult1 = $this->fetchRow($innerQuery);

        $parts = $queries[5];
        $parts['select'] = 'SELECT  emails.`date_sent_received` as `emails_date_sent` ';
        $parts['order_by'] = ' ORDER BY  `emails_date_sent` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult2 = $this->fetchRow($innerQuery);

8.6.2
core\modules\Contacts\Statistics\ContactLastTouchPoint.php
$queries[5] & $queries[6]

        $parts = $queries[5];
        $parts['select'] = 'SELECT  emails.`date_sent_received` ';
        $parts['order_by'] = ' ORDER BY  emails.`date_sent_received` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult1 = $this->fetchRow($innerQuery);

        $parts = $queries[6];
        $parts['select'] = 'SELECT  emails.`date_sent_received` as `emails_date_sent` ';
        $parts['order_by'] = ' ORDER BY  `emails_date_sent` DESC LIMIT 1';
        $innerQuery = $this->joinQueryParts($parts);
        $emailsResult2 = $this->fetchRow($innerQuery);

[pgr]

Nice detective work!

Can you open a PR on Github with this fix?

[Harshad]

Thanks. It looks this change was made for fixing a bug in 8.7. Raised a new issue for this.

[pgr]

That would be this issue, I believe

Contact last touch point select SQL has a column (emails.date_sent_received) on a wrong table (notes) instead of emails.

and it should be fixed in v8.8