Error in export template

[rmcdonald]

We are getting the following error in an export template that is using a common report to build the table when we try to have the report sort by a date field in the report, report works fine when we remove the sorting

Database Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'and ((select count(*) as total from app_entity_88_values cv where cv.items_i...' at line 1
Query: select e.* , (IFNULL(e.field_1870,0)+IFNULL(e.field_1871,0)) as field_1872, (IFNULL(e.field_1792,0)+IFNULL(e.field_1793,0)+IFNULL(e.field_1796,0)) as field_1873, (if(e.field_1809=646,e.field_1663,0)) as field_1905, (if(e.field_1809=645,e.field_1663,0)) as field_1906 from app_entity_88 e where e.id>0 and e.parent_item_id in (select id from app_entity_1 where parent_item_id in (5)) and ((FROM_UNIXTIME(field_1661,'%Y-%m')=date_format(DATE_ADD(now(),INTERVAL 0 MONTH),'%Y-%m')) and e.field_1809 in (646) ) and e.parent_item_id in (select item_id from (select e.id as item_id , ( (select MAX(func100.field_1661+0) from app_entity_88 func100 where func100.id>0 and func100.parent_item_id=e.id ) ) as field_1810, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) ) as field_1747, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1272 in (478) ) and func100.parent_item_id=e.id ) ) as field_1748, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and ((select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1279' and cv.value in (334)) >0) and func100.parent_item_id=e.id ) ) as field_1749, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and ((select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1279' and cv.value in (333)) >0) and func100.parent_item_id=e.id ) ) as field_1750, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and ((select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1279' and cv.value in (333)) >0 and func100.field_1272 in (478) ) and func100.parent_item_id=e.id ) ) as field_1751, (if( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1282 in (486) ) and func100.parent_item_id=e.id ) =0,NULL, (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1282 in (486) ) and func100.parent_item_id=e.id ) )) as field_1752, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and ((select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1279' and cv.value in (336)) >0) and func100.parent_item_id=e.id ) ) as field_1753, (FROM_UNIXTIME( (select MAX(func100.field_1270+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) ,'%d/%m/%Y')) as field_1754, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1272 in (478) and (select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1277' and cv.value in (474)) >0) and func100.parent_item_id=e.id ) ) as field_1756, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and ((select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1279' and cv.value in (334)) >0 and func100.field_1272 in (478) ) and func100.parent_item_id=e.id ) ) as field_1763, (if( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1272 in (478) and func100.field_1282 in (486) ) and func100.parent_item_id=e.id ) =0,NULL, (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1272 in (478) and func100.field_1282 in (486) ) and func100.parent_item_id=e.id ) )) as field_1764, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and ((select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1277' and cv.value in (490)) >0) and func100.parent_item_id=e.id ) ) as field_1765, ( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1272 in (478) and (select count(*) from app_entity_76_values as cv where cv.items_id=func100.id and cv.fields_id='1277' and cv.value in (490)) >0) and func100.parent_item_id=e.id ) ) as field_1766, ( (select SUM(func100.field_1539+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) ) as field_1767, ( (select SUM(func100.field_1539+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1272 in (478) ) and func100.parent_item_id=e.id ) ) as field_1768, (if( (select SUM((IFNULL(func100.field_1284,0)+IFNULL(func100.field_1286,0))+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) =0,NULL, (select SUM((IFNULL(func100.field_1284,0)+IFNULL(func100.field_1286,0))+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) )) as field_1771, (if( (select SUM(func100.field_1286+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) =0,NULL, (select SUM(func100.field_1286+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) )) as field_1772, (if( (select SUM(func100.field_1291+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) =0,NULL, (select SUM(func100.field_1291+0) from app_entity_76 func100 where func100.id>0 and func100.parent_item_id=e.id ) )) as field_1773, (if( (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1282 in (486) and (FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_ADD(now(),INTERVAL 0 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 90 DAY),'%Y-%m-%d'))) and func100.parent_item_id=e.id ) =0,NULL, (select SUM((IFNULL(func100.field_1294,0)+IFNULL(func100.field_1539,0)+IFNULL(func100.field_1291,0))+0) from app_entity_76 func100 where func100.id>0 and (func100.field_1282 in (486) and (FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_ADD(now(),INTERVAL 0 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-0.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 73 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 74 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 90 DAY),'%Y-%m-%d'))) and func100.parent_item_id=e.id ) )) as field_1774, (if( (select SUM(func100.field_1292+0) from app_entity_76 func100 where func100.id>0 and ((FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_ADD(now(),INTERVAL 0 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 1 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 2 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 41 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 42 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 43 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 44 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 45 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 46 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 47 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 48 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 90 DAY),'%Y-%m-%d'))) and func100.parent_item_id=e.id ) )) as field_1776, (if( (select SUM(func100.field_1291+0) from app_entity_76 func100 where func100.id>0 and ((FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_ADD(now(),INTERVAL 0 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 1 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 90 DAY),'%Y-%m-%d'))) and func100.parent_item_id=e.id ) =0,NULL, (select SUM(func100.field_1291+0) from app_entity_76 func100 where func100.id>0 and ((FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_ADD(now(),INTERVAL 0 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 1 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 2 DAY),'%Y-%m-%d') or -%d')=date_format(DATE_SUB(now(),INTERVAL 89 DAY),'%Y-%m-%d') or FROM_UNIXTIME(func100.field_1270,'%Y-%m-%d')=date_format(DATE_SUB(now(),INTERVAL 90 DAY),'%Y-%m-%d'))) and func100.parent_item_id=e.id ) )) as field_1779,IFNULL((select (select SUM(func100.field_1663+0) from app_entity_88 func100 where func100.id>0 and ((FROM_UNIXTIME(func100.field_1661,'%Y-%m')=date_format(DATE_SUB(now(),INTERVAL 1 MONTH),'%Y-%m')) and func100.field_1809 in (646) )) from app_entity_88 msq where msq.field_1808=id limit 1),0) as field_1919 from app_entity_1 e where e.id>0 and e.parent_item_id in (select e.id from app_entity_55 e where e.id>0 and ((select count(*) as total from app_entity_55_values cv where cv.items_id=e.id and cv.fields_id='1014' and cv.value='119')>0 or (select count(*) as total from app_entity_55_values cv where cv.items_id=e.id and cv.fields_id='1691' and cv.value='119')>0 or (select count(*) as total from app_entity_55_values cv where cv.items_id=e.id and cv.fields_id='1829' and cv.value='119')>0 or e.created_by='119') )) as parent_entity_1 ) order by (e.field_1661+0) asc and ((select count(*) as total from app_entity_88_values cv where cv.items_id=e.id and cv.fields_id='1808' and cv.value='119')>0 or e.created_by='119')
Page: /index.php?module=items/export_template&path=55-5&templates_id=13

[support]

If you can then send me backup of your database in PM because I can't reproduce it.