Report with domain data source querying Apache Calcite jdbc driver failing on parsing timestamp

Issue:

Running a report that queries a domain with a data source setup as a JDBC connection to Apache Calcite database fails with following error: 

Caused by: java.sql.SQLException: Error while preparing statement [select XXX from TABLE where "CREATEDTIME" between timestamp('2021-10-08 05:00:00') and timestamp('2021-10-10 04:59:59.999')]
    at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
    at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:203)
    ...
Caused by: java.lang.RuntimeException: parse failed: Encountered "timestamp (" at line 6, column 56.
Was expecting one of:
        "SYMMETRIC" ...
        "ASYMMETRIC" ...
        "+" ...
        "-" ...
        <UNSIGNED_INTEGER_LITERAL> ...
        <DECIMAL_NUMERIC_LITERAL> ...
    ...

Solution:

The Apache Calcite jdbc driver does not recognize the timestamp() function. This timestamp function exists at the domain semantic layer through the defaultSQLGenerator located inside the configuraton file ..WEB-INFapplicationContext-semanticLayer.xml.

The bean id="defaultSQLGenerator" has this function:

    <entry key="Timestamp">
        <value>"timestamp(" + sqlArgs[0] + ")"</value>
    </entry>

To resolve this issue, a custom SQL generator can be added and used instead of the defaultSQLGenerator. 

For example, you can add a custom defined 'calciteSQLGenerator' :

<bean id="calciteSQLGenerator" parent="defaultSQLGenerator" scope="prototype">

and remove the 'timestamp' keyword from the Timestamp entry and just have :

    <entry key="Timestamp">
        <value>sqlArgs[0]</value>
    </entry>

You need to ensure that you add this to the 'serverTypes' section similar to the other database types:

    <bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
        <property name="productName" value="Apache calcite"/>
        <property name="sqlGenerator" ref="calciteSQLGenerator"/>
    </bean>

and also ensure the productName and sqlGenerator in the section bean id dbServerTypeConfigFactory maps to your custom generator:

<!--
this maps from a database name to a SQLGenerator instance and other properties
-->

    <bean id="dbServerTypeConfigFactory" class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfigFactory">
        <property name="defaultConfig">
    <bean class="com.jaspersoft.commons.semantic.dsimpl.DBServerConfig">
        <property name="productName" value="Apache calcite"/>
        <property name="sqlGenerator" ref="calciteSQLGenerator"/>
        <property name="properties">
        ...
        ...

Note: Changes to this file requires a restart of the TIBCO JasperReports® Server.