Suitecrm: 7.9.4 SQL query error in Reports using custom field from another module

[vladimirpekez]

Issue

In Accounts module I have a custom field (trzisna_vertikala_c). When creating reports with Opportunity module as a base module and using this custom field from Accounts following error occurs:

Sat Aug 12 09:30:20 2017 [31118][1][FATAL] Query Failed:
SELECT count(*) c
FROM
(SELECT opportunities.name AS 'Opportunity_Name0',
opportunities:accounts_cstm.trzisna_vertikala_c AS 'Trzisna_vertikala1',
opportunities.id AS 'opportunities_id',
opportunities:accounts.id AS 'opportunities:accounts_id'
FROM opportunities
LEFT JOIN accounts_opportunities opportunities|opportunities:accounts ON opportunities.id=opportunities|opportunities:accounts.opportunity_id
AND opportunities|opportunities:accounts.deleted=0
LEFT JOIN accounts opportunities:accounts ON opportunities:accounts.id=opportunities|opportunities:accounts.account_id
AND opportunities:accounts.deleted=0
LEFT JOIN `opportunities:accounts_cstmopportunities:accounts_cstmONopportunities:accounts.id =opportunities:accounts_cstm`.id_c
WHERE opportunities.deleted = 0 ) AS n: MySQL error 1103: Incorrect TABLE name ''

Sat Aug 12 09:30:20 2017 [31118][1][FATAL] Query Failed:
SELECT opportunities.name AS 'Opportunity_Name0',
opportunities:accounts_cstm.trzisna_vertikala_c AS 'Trzisna_vertikala1',
opportunities.id AS 'opportunities_id',
opportunities:accounts.id AS 'opportunities:accounts_id'
FROM opportunities
LEFT JOIN accounts_opportunities opportunities|opportunities:accounts ON opportunities.id=opportunities|opportunities:accounts.opportunity_id
AND opportunities|opportunities:accounts.deleted=0
LEFT JOIN accounts opportunities:accounts ON opportunities:accounts.id=opportunities|opportunities:accounts.account_id
AND opportunities:accounts.deleted=0
LEFT JOIN `opportunities:accounts_cstmopportunities:accounts_cstmONopportunities:accounts.id =opportunities:accounts_cstm`.id_c
WHERE opportunities.deleted = 0
LIMIT 0,
20: MySQL error 1103: Incorrect TABLE name ''

Expected Behavior

Expected behavior is that report creates correct query when related module custom fields are used.

Actual Behavior

Query generated by SuiteCRM is not correct.

Steps to Reproduce

1. Add a custom field to Accounts (either through vardefs or studio)
2. Create a report based on Opportunities using custom field from Accounts

Your Environment

• SuiteCRM Version used: 7.9.4.
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): latest Chrome
• Environment name and version (e.g. MySQL, PHP 7): PHP 7.1.7, 5.5.52-MariaDB
• Operating System and version (e.g Ubuntu 16.04): CentOS 7

[rcairello]

We are having exactly the same bug / SQL error message.

Just to clarify, this report DID work ok prior to upgrading to 7.9.4

On Aug 12, 2017 4:36 AM, "vladimirpekez" [e-mail] wrote:

Issue

In Accounts module I have a custom field (trzisna_vertikala_c). When
creating reports with Opportunity module as a base module and using this
custom field from Accounts following error occurs:

Sat Aug 12 09:30:20 2017 [31118][1][FATAL] Query Failed:
SELECT count(*) c
FROM
(SELECT opportunities.name AS 'Opportunity_Name0',
opportunities:accounts_cstm.trzisna_vertikala_c AS 'Trzisna_vertikala1',
opportunities.id AS 'opportunities_id',
opportunities:accounts.id AS 'opportunities:accounts_id'
FROM opportunities
LEFT JOIN accounts_opportunities opportunities|opportunities:accounts ON
opportunities.id=opportunities|opportunities:accounts.opportunity_id
AND opportunities|opportunities:accounts.deleted=0
LEFT JOIN accounts opportunities:accounts ON opportunities:accounts.id=
opportunities|opportunities:accounts.account_id
AND opportunities:accounts.deleted=0
LEFT JOIN `opportunities:accounts_cstmopportunities:accounts_cstmON opportunities:accounts.id =opportunities:accounts_cstm`.id_c
WHERE opportunities.deleted = 0 ) AS n: MySQL error 1103: Incorrect TABLE
name ''

Sat Aug 12 09:30:20 2017 [31118][1][FATAL] Query Failed:
SELECT opportunities.name AS 'Opportunity_Name0',
opportunities:accounts_cstm.trzisna_vertikala_c AS 'Trzisna_vertikala1',
opportunities.id AS 'opportunities_id',
opportunities:accounts.id AS 'opportunities:accounts_id'
FROM opportunities
LEFT JOIN accounts_opportunities opportunities|opportunities:accounts ON
opportunities.id=opportunities|opportunities:accounts.opportunity_id
AND opportunities|opportunities:accounts.deleted=0
LEFT JOIN accounts opportunities:accounts ON opportunities:accounts.id=
opportunities|opportunities:accounts.account_id
AND opportunities:accounts.deleted=0
LEFT JOIN `opportunities:accounts_cstmopportunities:accounts_cstmON opportunities:accounts.id =opportunities:accounts_cstm`.id_c
WHERE opportunities.deleted = 0
LIMIT 0,
20: MySQL error 1103: Incorrect TABLE name ''
Expected Behavior

Expected behavior is that report creates correct query when related module
custom fields are used.
Actual Behavior

Query generated by SuiteCRM is not correct.
Steps to Reproduce

1. Add a custom field to Accounts (either through vardefs or studio)
2. Create a report based on Opportunities using custom field from
   Accounts

Your Environment

• SuiteCRM Version used: 7.9.4.
• Browser name and version (e.g. Chrome Version 51.0.2704.63
  (64-bit)): latest Chrome
• Environment name and version (e.g. MySQL, PHP 7): PHP 7.1.7,
  5.5.52-MariaDB
• Operating System and version (e.g Ubuntu 16.04): CentOS 7

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/salesagility/SuiteCRM/issues/4109, or mute the thread
https://github.com/notifications/unsubscribe-auth/ANC2f4iiIBnrX6ecAHmH9LLNilzcQ-Wbks5sXVYMgaJpZM4O1V2H
.

[rcairello]

To be precise, we have the same bug in OTHER base modules (specifically: QUOTES).

So it seems to be a generic (non module dependant) bug with the construction of the SQL sentence.

[vladimirpekez]

Exactly, this feature works in 7.8.5. I hope this bug will be addressed soon and marked as high priority.

[vladimirpekez]

Thanks @Dillon-Brown, I have implemented your fix and I can confirm that it works. Ready for merge!

[rcairello]

Hey @Dillon-Brown and @vladimirpekez !

Sorry for the newbie question:

How do I get Dillon's patch source code to implement it on my (just upgraded) 7.9.5 SuiteCRM (which still produces the same error, BTW).

_Thanks a lot ! (will continue reading Github's docs to stop making this silly questions!)_

[Dillon-Brown]

Hi @rcairello, the PR for this fix was made for the "Develop" branch which differs from the regular 7.9.5 release. This fix will be included in the next SuiteCRM major release but if you want to pull it down now you can do one of the following which will take the fix from my 7.9.5 branch:

1. If your instance is initialised with git you can do: git pull https://github.com/Dillon-Brown/SuiteCRM.git pr-4120-2

2. With a non-git instance you can take the entire file from here and replace the one you have in /modules/AOR_Reports/AOR_Report.php

[rcairello]

Great! Will do as instructed!
All the best and thanks for the help!!!
Ricardo