Uploaded image for project: 'Isis'
  1. Isis
  2. ISIS-1859

Use of "timestamp" in JDOQL queries.

    Details

    • Type: Documentation
    • Status: Closed
    • Priority: Trivial
    • Resolution: Fixed
    • Affects Version/s: 1.16.0
    • Fix Version/s: 1.16.1
    • Component/s: None
    • Labels:
      None

      Description

      As posted by me on the DN gitter channel...

      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

       

       

        Attachments

          Activity

            People

            • Assignee:
              danhaywood Dan Haywood
              Reporter:
              danhaywood Dan Haywood
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: