After upgrading from 7.4.x to 7.5.1 some of my reports broke, I tracked it down to the following conditions no longer working and why.
CONDITION: Lead Modified Date GREATER THAN Date TODAY - 30 Days
the following SQL error is thrown:
Query Failed: SELECT count(*) c FROM (SELECT leads.id AS ‘leads_id’, leads.date_modified AS ‘Date_Modified0’, leads.assigned_user_id AS ‘Assigned_to1’ FROM leads WHERE ( DATE(leads.date_modified) > DATE_ADD(Curdate, INTERVAL - 30 day) ) AND leads.deleted = 0 ) as n: MySQL error 1054: Unknown column ‘Curdate’ in 'where clause
I encountered the same problem。After upgrading from 7.4.x to 7.5.1。 and I install new 7.5.1 ,The problem is still 。
CONDITION:
Field ： Date Created
Operator： GREATER （ or Less) THAN
Type: Date
Value: Now ( or Today) - 30 Days
(BUT CONDITION: Date Created GREATER THAN Period TODAY The result is correct）
the following SQL error is thrown:
Sun Feb 14 01:55:36 2016 [4383][1][FATAL] Query Failed: SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Name0’ FROM accounts WHERE ( accounts.date_entered <= DATE_ADD(NOW, INTERVAL - 1 hour) ) AND accounts.deleted = 0 : MySQL error 1054: Unknown column ‘NOW’ in ‘where clause’
Sun Feb 14 01:55:44 2016 [4385][1][FATAL] Query Failed: SELECT count(*) c FROM (SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Name0’ FROM accounts WHERE ( accounts.date_entered <= DATE_ADD(accounts., INTERVAL ) ) AND accounts.deleted = 0 ) as n: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ INTERVAL ) ) AND accounts.deleted = 0 ) as n’ at line 1
Sun Feb 14 01:55:44 2016 [4385][1][FATAL] Query Failed: SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Name0’ FROM accounts WHERE ( accounts.date_entered <= DATE_ADD(accounts., INTERVAL ) ) AND accounts.deleted = 0 LIMIT 0,20: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ INTERVAL ) ) AND accounts.deleted = 0 LIMIT 0,20’ at line 1
Sun Feb 14 01:55:44 2016 [4385][1][FATAL] Query Failed: SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Name0’ FROM accounts WHERE ( accounts.date_entered <= DATE_ADD(accounts., INTERVAL ) ) AND accounts.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ INTERVAL ) ) AND accounts.deleted = 0’ at line 1
Hi,
I’m experiencing the same issue.
It looks like the function queryWhereRepair under modules/AOR_Reports/AOR_Report.php is corrupting the sql queries.
In my case, it is removing the () of the Curdate function.
I removed ( ) but the problem is someone has solved ?
LEFT JOIN manzo_auto accounts:accounts_manzo_auto_1 ON accounts:accounts_manzo_auto_1.id=accounts|accounts:accounts_manzo_auto_1.accounts_manzo_auto_1manzo_auto_idb AND accounts:accounts_manzo_auto_1.deleted=0
WHERE ( accounts:accounts_manzo_auto_1.scamanu >= DATE_ADD(NOW, INTERVAL + 50 day) ) AND accounts.deleted = 0 ) as n: MySQL error 1054: Unknown column ‘NOW’ in ‘where clause’
02/29/16 12:40:45 [1836][1][FATAL] Query Failed: SELECT accounts.id AS ‘accounts_id’, accounts.name AS ‘Nome0’, accounts.name AS ‘Nome1’, accounts:accounts_manzo_auto_1.id AS ‘accounts:accounts_manzo_auto_1_id’ FROM accounts LEFT JOIN accounts_manzo_auto_1_c accounts|accounts:accounts_manzo_auto_1 ON accounts.id=accounts|accounts:accounts_manzo_auto_1.accounts_manzo_auto_1accounts_ida AND accounts|accounts:accounts_manzo_auto_1.deleted=0
Is anybody still experiencing an issue with this after the 7.5.2 upgrade? I can’t seem to use a date field as a parameter on any report, I get zero results. I was able to verify the behavior on the demo site.