[jira] [Created] (ISIS-1859) Use of "timestamp" in JDOQL queries.

["Dan Haywood (JIRA)" <ji...@apache.org>]

Dan Haywood created ISIS-1859:
---------------------------------

             Summary: Use of "timestamp" in JDOQL queries.
                 Key: ISIS-1859
                 URL: https://issues.apache.org/jira/browse/ISIS-1859
             Project: Isis
          Issue Type: Documentation
    Affects Versions: 1.16.0
            Reporter: Dan Haywood
             Fix For: 1.16.1


As posted by me on the DN gitter channel...
{code:java}
I have an entity CommandJdo that has a property called "timestamp", of type java.sql.Timestamp

My JDOQL for this is:

SELECT
FROM org.isisaddons.module.command.dom.CommandJdo
WHERE executeIn == 'FOREGROUND'
&& timestamp > :timestamp
&& startedAt != null
&& completedAt != null
ORDER BY timestamp ASC

Running against SQL Server 2016 this gave me a different result first time it was run compared to subsequent times.
Running SQL Profiler I found the underlying SQL as:

exec sp_prepexec @p1 output,N'@P0 datetime2',N'SELECT ''org.isisaddons.module.command.dom.CommandJdo'' AS NUCLEUS_TYPE,A0.arguments,A0.completedAt,A0."exception",A0.executeIn,A0.memberIdentifier,A0.memento,A0.replayState,A0.replayStateFailureReason,A0."result",A0.startedAt,A0.targetAction,A0.targetClass,A0.target,A0."timestamp",A0.transactionId,A0."user",''2018-01-24 17:29:18.3'' AS NUCORDER0 FROM isiscommand.Command A0 WHERE A0.executeIn = ''FOREGROUND'' AND A0."timestamp" > @P0 AND A0.startedAt IS NOT NULL AND A0.completedAt IS NOT NULL ORDER BY NUCORDER0 OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY ','2018-01-24 17:29:18.3000000'

To unpick this, the key bit is: '

'2018-01-24 17:29:18.3'' AS NUCORDER0
... ORDER BY NUCORDER0

So in the JDOQL the "ORDER BY timestamp ASC", the "timestamp" is being evaluated as the current time, I suppose as a sort-of built-in function.

Which was surprising.

My fix was to change the JDOQL to be:
ORDER BY this.timestamp ASC{code}
 

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)