Server Side | Velocity Param | WhereClause Bug

[EISENMANN]

1. Version: smartGWT Power Edition 4.1-p20150224
2. all Browsers
3. Problem definition: Bug in WhereClause of Server side

The velocity Variables are not set Correctly in whereClause:

The velocity Variable are set as String in the whereClause. They are not converted tho the correct time format or to the correct local time.


a. The whereClause is this

<whereClause>
<![CDATA[
ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME
AND (
ARC_ALARM.TSBEGIN
BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO
OR
ARC_ALARM.TSEND
BETWEEN
$advancedCriteria.TSFROM AND $advancedCriteria.TSTO
OR (ARC_ALARM.TSBEGIN < $advancedCriteria.TSFROM AND ARC_ALARM.TSEND > $advancedCriteria.TSTO ))
AND ARC_ALARM.ACTIVE=0
AND ($defaultWhereClause)
]]>
</whereClause>

b. it send following Query to the database and Cause Sql Error due to wrong date Format:

WHERE
(
ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME
AND (
ARC_ALARM.TSBEGIN
BETWEEN '2015-08-06T10:59:00.000' AND '2015-08-11T10:59:14.574'
OR
ARC_ALARM.TSEND
BETWEEN
'2015-08-06T10:59:00.000' AND '2015-08-11T10:59:14.574'
OR (ARC_ALARM.TSBEGIN > '2015-08-06T10:59:00.000' AND ARC_ALARM.TSEND < '2015-08-11T10:59:14.574')
)
AND ARC_ALARM.ACTIVE=0
)
AND (((ARC_ALARM_VAR.MESSAGEGROUP = 'FM' AND ARC_ALARM_VAR.MESSAGEGROUP IS NOT NULL)))


c. The Date should be normaly like this and its work every where else in the Query but not in the WhereClause
TO_DATE('2015-08-06 12:59:00', 'YYYY-MM-DD HH24:MI:SS')

d. on the client side work everythings correct. I need the part in Server side to create PDF Report using "Jsasper Report"

f. here is the Datasource


<DataSource
ID="alarm_top_ten_range"
serverType="sql"
tableName="ARC_ALARM"
qualifyColumnNames="false">
<!-- requiresAuthentication="true" -->
<fields>
<field name="CNT" type="integer" customSelectExpression="count(*)" />
<field name="SUM" type="text" customSelectExpression="regexp_substr(numtodsinterval(sum((sysdate + (TSEND - TSBEGIN)) - sysdate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\')" />
<field name="AVG" type="text" customSelectExpression="regexp_substr(numtodsinterval(avg(($currentDate + (TSEND - TSBEGIN)) - $currentDate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\')" />
<field name="MIN" type="text" customSelectExpression="regexp_substr(numtodsinterval(min(($currentDate + (TSEND - TSBEGIN)) - $currentDate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\')" />
<field name="MAX" type="text" customSelectExpression="regexp_substr(numtodsinterval(max(($currentDate + (TSEND - TSBEGIN)) - $currentDate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\')" />
<field name="TEXT" type="text" tableName="DIC_TRANSLATIONS_MT" customSelectExpression="REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1)" nativeName="TEXT"/>
<field name="MESSAGETYPE" type="text" tableName="ARC_ALARM_VAR" />
<field name="ISWARNING" type="integer" tableName="ARC_ALARM_VAR" />
<field name="MESSAGEGROUP" type="text" tableName="ARC_ALARM_VAR" />
<field name="PAD" type="text" tableName="ARC_ALARM_VAR" />
<field name="PADNAME" type="text" tableName="DIC_TRANSLATIONS_PAD" nativeName="DIC_TRANSLATIONS_PAD.TEXT" />
<field name="ESTOPAREA" type="text" tableName="ARC_ALARM_VAR" />
<field name="PANELAREA" type="text" tableName="ARC_ALARM_VAR" />
<field name="CONSOLEAREA" type="text" tableName="ARC_ALARM_VAR" />
<field name="FUNCTIONGROUP" type="text" tableName="ARC_ALARM_VAR" />
<field name="FUNCTIONGROUPDESCRIPTION" type="text" tableName="DIC_TRANSLATIONS_FGD" nativeName="DIC_TRANSLATIONS_FGD.TEXT"/>
<field name="DEVICE" type="text" tableName="ARC_ALARM_VAR" />
<field name="DEVICEDESCRIPTION" type="text" tableName="DIC_TRANSLATIONS_DD" nativeName="DIC_TRANSLATIONS_DD.TEXT"/>
<field name="VARIABLE_NAME" type="text" tableName="ARC_ALARM"/>
<field name="ADDRESS" type="text" tableName="ARC_ALARM_VAR" />
<field name="INTERVALBEGIN_ID" type="integer" customSelectExpression="min(INTERVALBEGIN_ID)"/>
<field name="INTERVALEND_ID" type="integer" customSelectExpression="max(INTERVALEND_ID)"/>
<field name="TSFIRSTBEGIN" type="datetime" customSelectExpression="min(TSBEGIN)" />
<field name="TSLASTEND" type="datetime" customSelectExpression="max(TSEND)" />
<field name="TSFROM" type="datetime" customSQL="true" />
<field name="TSTO" type="datetime" customSQL="true" />
</fields>

<operationBindings>
<operationBinding operationType="fetch">
<tableClause>
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_PAD
ON (ARC_ALARM_VAR.PADNAME_GROUPID = DIC_TRANSLATIONS_PAD.GROUPID AND ARC_ALARM_VAR.PADNAME = DIC_TRANSLATIONS_PAD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
</tableClause>
<whereClause>
<![CDATA[
ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR ARC_ALARM.TSEND BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR (ARC_ALARM.TSBEGIN < $advancedCriteria.TSFROM AND ARC_ALARM.TSEND > $advancedCriteria.TSTO )) AND ARC_ALARM.ACTIVE=0 AND ($defaultWhereClause)
]]>
</whereClause>
<groupClause>
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PAD, DIC_TRANSLATIONS_PAD.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS
</groupClause>
</operationBinding>
</operationBindings>
</DataSource>

6. here the sql send to databse causing Oracle Error due to wrong Date Format

SELECT count(*) AS CNT, regexp_substr(numtodsinterval(sum((sysdate + (TSEND - TSBEGIN)) - sysdate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS SUM, regexp_substr(numtodsinterval(avg((TO_DATE('2015-08-11 12:59:30','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-11 12:59:30','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS AVG, regexp_substr(numtodsinterval(min((TO_DATE('2015-08-11 12:59:30','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-11 12:59:30','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MIN, regexp_substr(numtodsinterval(max((TO_DATE('2015-08-11 12:59:30','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-11 12:59:30','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MAX, REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1) AS TEXT, MESSAGETYPE, , DIC_TRANSLATIONS_FGD.TEXT AS FUNCTIONGROUPDESCRIPTION, DEVICE, DIC_TRANSLATIONS_DD.TEXT AS DEVICEDESCRIPTION, VARIABLE_NAME, ADDRESS, min(INTERVALBEGIN_ID) AS INTERVALBEGIN_ID, max(INTERVALEND_ID) AS INTERVALEND_ID, min(TSBEGIN) AS TSFIRSTBEGIN, max(TSEND) AS TSLASTEND FROM
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_PAD
ON (ARC_ALARM_VAR.PADNAME_GROUPID = DIC_TRANSLATIONS_PAD.GROUPID AND ARC_ALARM_VAR.PADNAME = DIC_TRANSLATIONS_PAD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
WHERE
(
ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME
AND (
ARC_ALARM.TSBEGIN
BETWEEN '2015-08-06T10:59:00.000' AND '2015-08-11T10:59:14.574'
OR
ARC_ALARM.TSEND
BETWEEN
'2015-08-06T10:59:00.000' AND '2015-08-11T10:59:14.574'
OR (ARC_ALARM.TSBEGIN > '2015-08-06T10:59:00.000' AND ARC_ALARM.TSEND < '2015-08-11T10:59:14.574')
)
AND ARC_ALARM.ACTIVE=0
)
AND (((ARC_ALARM_VAR.MESSAGEGROUP = 'FM' AND ARC_ALARM_VAR.MESSAGEGROUP IS NOT NULL)))

[Isomorphic]

Most likely, you have code that is submitting Strings to the server where Dates are expected. Or, similarly, you may have server-side logic which is causing Date to String conversion.

You can look at the server logs to figure out where this conversion is taking place. Oddly, you posting the forums reminder to post server logs... but not the server logs.

[EISENMANN]

Server Call and Logs

1. The Json Data which sent to Server Server

criterion: "{
"_constructor":"AdvancedCriteria",
"operator":"and",
"criteria":[
{
"fieldName":"TSFROM",
"operator":"equals",
"value":"2015-08-10T22:00:00.000"
},
{
"fieldName":"TSTO",
"operator":"equals",
"value":"2015-08-16T22:00:00.000"
},
{
"fieldName":"MESSAGEGROUP",
"operator":"equals",
"value":"FM"
}
]
}"


2. On the server side I use this function to trace Json String and convert it to map
Evaluator.parseAdvancedCriteria(map) working fine and its return the Date Variable. The local time


public AdvancedCriteria getAdvancedCriteria(String criterion)
{
JsonReader reader = Json.createReader(new StringReader(criterion));
Map<String, Object> map = this.parseObject(reader.readObject());
AdvancedCriteria advancedCriteria =
Evaluator.parseAdvancedCriteria(map);

Criterion asCriterion = advancedCriteria.asCriterion();
return new AdvancedCriteria(asCriterion);
}

3. calling fetch. The Variable from AdvancedCriteria working fine for all fields except making WhereClause

public ReportDSResponse fetch()
{
DSRequest req = new DSRequest(datasource, "fetch");

if ( this.velocity != null )
{
for ( Entry<String, Object> item : this.velocity.entrySet() )
{
req.addToTemplateContext(item.getKey(), item.getValue());
}
}

ServerAdvancedCriteria advCriteria =
new ServerAdvancedCriteria(getClientLocale());
req.setAdvancedCriteria(advCriteria
.getAdvancedCriteria(this.criterion));

if ( sortBy.size() > 0 )
{
req.setSortBy(sortBy);
}

if ( this.getStartRow() >= 0 )
{
req.setStartRow(this.startRow);
}

if ( this.getEndRow() >= 0 )
{
req.setEndRow(this.endRow + 1);
}

DSResponse resp;
try
{
resp = req.execute();
}
catch ( Exception e )
{
ReportLogger.LOGGER.severe("Failed to fetch data from data source: "
+ e.toString());
throw new ReportExportException("Failed to fetch data from data source: ",
e);
}
return new ReportDSResponse(resp);
}

4. Here is the logs. As you see it cause ORA-01843. in the where clause,
every where else for example in TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') work fine,
and create Date object with correct local time.
(Sorry that I copy the whole logs here. the whole server Debug log was 280k byte for the call. I couldnt upload the whole file. allowed upload size exceeded)


=== 2015-08-17 17:16:30,667 [1(5)] DEBUG Validation - Value provided for unknown field: Object.types: value is: {type={ID=DSProtocol, inheritsFrom=enum, valueMap={value=[getParams, postParams, postXML, soap, postMessage]}}}
=== 2015-08-17 17:16:30,667 [1(5)] DEBUG BasicDataSource - Done validating a 'Object' at path '': 48ms
=== 2015-08-17 17:16:30,667 [1(5)] DEBUG Timing - DataSource record created from XML with document element: 'Object': 48ms
=== 2015-08-17 17:16:30,669 [1(5)] DEBUG Validation - Validating field:
/DataSource[@ID=alarm_top_ten_range]/operationBindings as DataSource.operationBindings type: OperationBinding
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG BasicDataSource - Validating 1 'OperationBinding's at path '/DataSource[@ID=alarm_top_ten_range]/operationBindings'
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG BasicDataSource - Validating a 'OperationBinding' at path '/DataSource[@ID=alarm_top_ten_range]/operationBindings/0'
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG DataStructCache - getInstanceFile (failure): 'operationBinding' instance of datasources: 0ms
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG Validation - Validating field:
/DataSource[@ID=alarm_top_ten_range]/operationBindings/0/operationBinding/operationType as OperationBinding.operationType type: string
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG Validation - Value provided for unknown field: OperationBinding.tableClause: value is:
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_PLC
ON (ARC_ALARM_VAR.PLCNAME_GROUPID = DIC_TRANSLATIONS_PLC.GROUPID AND ARC_ALARM_VAR.PLCNAME = DIC_TRANSLATIONS_PLC.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)

=== 2015-08-17 17:16:30,691 [1(5)] DEBUG Validation - Value provided for unknown field: OperationBinding.whereClause: value is:

ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR ARC_ALARM.TSEND BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR (ARC_ALARM.TSBEGIN < $advancedCriteria.TSFROM AND ARC_ALARM.TSEND > $advancedCriteria.TSTO )) AND ARC_ALARM.ACTIVE=0 AND ($defaultWhereClause)


=== 2015-08-17 17:16:30,691 [1(5)] DEBUG Validation - Value provided for unknown field: OperationBinding.groupClause: value is:
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS

=== 2015-08-17 17:16:30,691 [1(5)] DEBUG BasicDataSource - Done validating 1 'OperationBinding's at path '/DataSource[@ID=alarm_top_ten_range]/operationBindings': 0ms (avg 0)
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG BasicDataSource - Done validating a 'DataSource' at path '': 269ms
=== 2015-08-17 17:16:30,691 [1(5)] DEBUG Timing - DataSource record created from XML with document element: 'DataSource': 393ms
=== 2015-08-17 17:16:30,695 [1(5)] DEBUG InterfaceProvider - Instantiating com.isomorphic.sql.SQLDataSource to satisfy request for an SQLDataSource
=== 2015-08-17 17:16:30,761 [1(5)] DEBUG DSRequest - Caching instance 8 of DS alarm_top_ten_range from DSRequest.getDataSource()
=== 2015-08-17 17:16:30,771 [1(5)] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-08-17 17:16:30,781 [1(5)] DEBUG AppBase - [builtinApplication.report] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-08-17 17:16:30,784 [1(5)] DEBUG AppBase - [builtinApplication.report] No public zero-argument method named '_report' found, performing generic datasource operation
=== 2015-08-17 17:16:30,796 [1(5)] DEBUG Timing - [builtinApplication.report] Time to convert java.util.HashMap to JS Object: 12ms
=== 2015-08-17 17:16:30,797 [1(5)] DEBUG Timing - [builtinApplication.report] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-08-17 17:16:30,797 [1(5)] INFO SQLDataSource - [builtinApplication.report] Performing fetch operation with
criteria: {_constructor:"AdvancedCriteria",criteria:[{fieldName:"TSFROM",value:"2015-08-10T22:00:00.000",operator:"equals"},{fieldName:"TSTO",value:"2015-08-16T22:00:00.000",operator:"equals"},{fieldName:"MESSAGEGROUP",value:"FM",operator:"equals"}],operator:"and"} values: {_constructor:"AdvancedCriteria",criteria:[{fieldName:"TSFROM",value:"2015-08-10T22:00:00.000",operator:"equals"},{fieldName:"TSTO",value:"2015-08-16T22:00:00.000",operator:"equals"},{fieldName:"MESSAGEGROUP",value:"FM",operator:"equals"}],operator:"and"}
=== 2015-08-17 17:16:31,160 [1(5)] INFO SQLDataSource - [builtinApplication.report] derived query: SELECT $defaultSelectClause FROM
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_PLC
ON (ARC_ALARM_VAR.PLCNAME_GROUPID = DIC_TRANSLATIONS_PLC.GROUPID AND ARC_ALARM_VAR.PLCNAME = DIC_TRANSLATIONS_PLC.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
WHERE

ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR ARC_ALARM.TSEND BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR (ARC_ALARM.TSBEGIN < $advancedCriteria.TSFROM AND ARC_ALARM.TSEND > $advancedCriteria.TSTO )) AND ARC_ALARM.ACTIVE=0 AND ($defaultWhereClause)

GROUP BY
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS
ORDER BY $defaultOrderClause
=== 2015-08-17 17:16:31,181 [1(5)] DEBUG SQLDataSource - [builtinApplication.report] Executing row count query: SELECT COUNT(*) FROM (SELECT $defaultSelectClause FROM
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_PLC
ON (ARC_ALARM_VAR.PLCNAME_GROUPID = DIC_TRANSLATIONS_PLC.GROUPID AND ARC_ALARM_VAR.PLCNAME = DIC_TRANSLATIONS_PLC.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ($session.locale.language)) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
WHERE

ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR ARC_ALARM.TSEND BETWEEN $advancedCriteria.TSFROM AND $advancedCriteria.TSTO OR (ARC_ALARM.TSBEGIN < $advancedCriteria.TSFROM AND ARC_ALARM.TSEND > $advancedCriteria.TSTO )) AND ARC_ALARM.ACTIVE=0 AND ($defaultWhereClause)

GROUP BY
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS
) work
=== 2015-08-17 17:16:31,184 [1(5)] DEBUG SQLDataSource - [builtinApplication.report] Eval'd row count query: SELECT COUNT(*) FROM (SELECT count(*) AS CNT, regexp_substr(numtodsinterval(sum((sysdate + (TSEND - TSBEGIN)) - sysdate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS SUM, regexp_substr(numtodsinterval(avg((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS AVG, regexp_substr(numtodsinterval(min((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MIN, regexp_substr(numtodsinterval(max((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MAX, REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1) AS TEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT AS PLCNAME, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT AS FUNCTIONGROUPDESCRIPTION, DEVICE, DIC_TRANSLATIONS_DD.TEXT AS DEVICEDESCRIPTION, VARIABLE_NAME, ADDRESS, min(INTERVALBEGIN_ID) AS INTERVALBEGIN_ID, max(INTERVALEND_ID) AS INTERVALEND_ID, min(TSBEGIN) AS TSFIRSTBEGIN, max(TSEND) AS TSLASTEND FROM
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_PLC
ON (ARC_ALARM_VAR.PLCNAME_GROUPID = DIC_TRANSLATIONS_PLC.GROUPID AND ARC_ALARM_VAR.PLCNAME = DIC_TRANSLATIONS_PLC.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
WHERE

ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN '2015-08-10T22:00:00.000' AND '2015-08-16T22:00:00.000' OR ARC_ALARM.TSEND BETWEEN '2015-08-10T22:00:00.000' AND '2015-08-16T22:00:00.000' OR (ARC_ALARM.TSBEGIN < '2015-08-10T22:00:00.000' AND ARC_ALARM.TSEND > '2015-08-16T22:00:00.000' )) AND ARC_ALARM.ACTIVE=0 AND (((ARC_ALARM_VAR.MESSAGEGROUP = 'FM' AND ARC_ALARM_VAR.MESSAGEGROUP IS NOT NULL)))

GROUP BY
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS
) work
=== 2015-08-17 17:16:31,205 [1(5)] INFO PoolManager - [builtinApplication.report] SmartClient pooling disabled for 'Oracle' objects
=== 2015-08-17 17:16:31,206 [1(5)] DEBUG PoolableSQLConnectionFactory - [builtinApplication.report] Initializing SQL config for 'Oracle' via JNDI
=== 2015-08-17 17:16:31,213 [1(5)] DEBUG JNDI - [builtinApplication.report] No configuration for JNDI context '' - assuming default initialContext
=== 2015-08-17 17:16:31,215 [1(5)] DEBUG PoolableSQLConnectionFactory - [builtinApplication.report] makeObject() created an unpooled Connection '1011726774'
=== 2015-08-17 17:16:31,216 [1(5)] DEBUG SQLConnectionManager - [builtinApplication.report] Borrowed connection '1011726774'
=== 2015-08-17 17:16:31,217 [1(5)] DEBUG SQLDriver - [builtinApplication.report] About to execute SQL query in 'Oracle' using connection '1011726774'
=== 2015-08-17 17:16:31,218 [1(5)] INFO SQLDriver - [builtinApplication.report] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM (SELECT count(*) AS CNT, regexp_substr(numtodsinterval(sum((sysdate + (TSEND - TSBEGIN)) - sysdate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS SUM, regexp_substr(numtodsinterval(avg((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS AVG, regexp_substr(numtodsinterval(min((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MIN, regexp_substr(numtodsinterval(max((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MAX, REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1) AS TEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT AS PLCNAME, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT AS FUNCTIONGROUPDESCRIPTION, DEVICE, DIC_TRANSLATIONS_DD.TEXT AS DEVICEDESCRIPTION, VARIABLE_NAME, ADDRESS, min(INTERVALBEGIN_ID) AS INTERVALBEGIN_ID, max(INTERVALEND_ID) AS INTERVALEND_ID, min(TSBEGIN) AS TSFIRSTBEGIN, max(TSEND) AS TSLASTEND FROM
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_PLC
ON (ARC_ALARM_VAR.PLCNAME_GROUPID = DIC_TRANSLATIONS_PLC.GROUPID AND ARC_ALARM_VAR.PLCNAME = DIC_TRANSLATIONS_PLC.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
WHERE

ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN '2015-08-10T22:00:00.000' AND '2015-08-16T22:00:00.000' OR ARC_ALARM.TSEND BETWEEN '2015-08-10T22:00:00.000' AND '2015-08-16T22:00:00.000' OR (ARC_ALARM.TSBEGIN < '2015-08-10T22:00:00.000' AND ARC_ALARM.TSEND > '2015-08-16T22:00:00.000' )) AND ARC_ALARM.ACTIVE=0 AND (((ARC_ALARM_VAR.MESSAGEGROUP = 'FM' AND ARC_ALARM_VAR.MESSAGEGROUP IS NOT NULL)))

GROUP BY
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS
) work
=== 2015-08-17 17:16:31,388 [1(5)] INFO SQLDriver - [builtinApplication.report] Execute of select: SELECT COUNT(*) FROM (SELECT count(*) AS CNT, regexp_substr(numtodsinterval(sum((sysdate + (TSEND - TSBEGIN)) - sysdate),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS SUM, regexp_substr(numtodsinterval(avg((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS AVG, regexp_substr(numtodsinterval(min((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MIN, regexp_substr(numtodsinterval(max((TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS') + (TSEND - TSBEGIN)) - TO_DATE('2015-08-17 17:16:31','YYYY-MM-DD HH24:MI:SS')),'day'), '\d{3} \d{2}:\d{2}:\d{2}\') AS MAX, REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1) AS TEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT AS PLCNAME, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT AS FUNCTIONGROUPDESCRIPTION, DEVICE, DIC_TRANSLATIONS_DD.TEXT AS DEVICEDESCRIPTION, VARIABLE_NAME, ADDRESS, min(INTERVALBEGIN_ID) AS INTERVALBEGIN_ID, max(INTERVALEND_ID) AS INTERVALEND_ID, min(TSBEGIN) AS TSFIRSTBEGIN, max(TSEND) AS TSLASTEND FROM
ARC_ALARM, ARC_ALARM_VAR
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_MT
ON (ARC_ALARM_VAR.MESSAGETEXT_GROUPID = DIC_TRANSLATIONS_MT.GROUPID AND ARC_ALARM_VAR.MESSAGETEXT = DIC_TRANSLATIONS_MT.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_FGD
ON (ARC_ALARM_VAR.FGDESCRIPTION_GROUPID = DIC_TRANSLATIONS_FGD.GROUPID AND ARC_ALARM_VAR.FUNCTIONGROUPDESCRIPTION = DIC_TRANSLATIONS_FGD.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_PLC
ON (ARC_ALARM_VAR.PLCNAME_GROUPID = DIC_TRANSLATIONS_PLC.GROUPID AND ARC_ALARM_VAR.PLCNAME = DIC_TRANSLATIONS_PLC.TEXTID)
LEFT JOIN (SELECT * FROM DIC_TRANSLATIONS WHERE DIC_TRANSLATIONS.LANGUAGE = ('de')) DIC_TRANSLATIONS_DD
ON (ARC_ALARM_VAR.DEVICEDESCRIPTIONGROUPID = DIC_TRANSLATIONS_DD.GROUPID AND ARC_ALARM_VAR.DEVICEDESCRIPTION = DIC_TRANSLATIONS_DD.TEXTID)
WHERE

ARC_ALARM.VARIABLE_NAME = ARC_ALARM_VAR.VARIABLENAME AND (ARC_ALARM.TSBEGIN BETWEEN '2015-08-10T22:00:00.000' AND '2015-08-16T22:00:00.000' OR ARC_ALARM.TSEND BETWEEN '2015-08-10T22:00:00.000' AND '2015-08-16T22:00:00.000' OR (ARC_ALARM.TSBEGIN < '2015-08-10T22:00:00.000' AND ARC_ALARM.TSEND > '2015-08-16T22:00:00.000' )) AND ARC_ALARM.ACTIVE=0 AND (((ARC_ALARM_VAR.MESSAGEGROUP = 'FM' AND ARC_ALARM_VAR.MESSAGEGROUP IS NOT NULL)))

GROUP BY
REPLACE(DIC_TRANSLATIONS_MT.TEXT,'%f1',P1), MESSAGETEXT, MESSAGETYPE, ISWARNING, MESSAGEGROUP, PLC, DIC_TRANSLATIONS_PLC.TEXT, ESTOPAREA, PANELAREA, CONSOLEAREA, FUNCTIONGROUP, DIC_TRANSLATIONS_FGD.TEXT, DEVICE, DIC_TRANSLATIONS_DD.TEXT, ARC_ALARM.VARIABLE_NAME, ADDRESS
) work on db: Oracle threw exception: java.sql.SQLDataException: ORA-01843: not a valid month
- assuming stale connection and retrying query.
=== 2015-08-17 17:16:31,389 [1(5)] DEBUG SQLConnectionManager - [builtinApplication.report] About to close ConnectionWrapper40 with hashcode "1011726774"
=== 2015-08-17 17:16:31,389 [1(5)] DEBUG PoolableSQLConnectionFactory - [builtinApplication.report] makeObject() created an unpooled Connection '1301737965'
=== 2015-08-17 17:16:31,396 [1(5)] DEBUG SQLDriver - Freeing SQLDriver dbConnection 1301737965
=== 2015-08-17 17:16:31,397 [1(5)] DEBUG SQLConnectionManager - About to close ConnectionWrapper40 with hashcode "1301737965"

[Isomorphic]

We didn't ask for a Debug-level log. Unless we specifically request something special, just use the default settings we ship with the product. That will produce logs with the key information we need, and which are small enough to upload here (as well as easier to read, hence more useful..).

However, it looks like the problem is what we guessed right off - you didn't say where that "Json Data" has been taken from, but if you copied that from the server log, it shows dates that have already been converted to Strings on the client side, which explains your issue.