[[jdoql-and-timestamps]]
= JDOQL and Timestamps

:Notice: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at. http://www.apache.org/licenses/LICENSE-2.0 . Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR  CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.


Beware of entities with a property called "timestamp": you run the risk of "timestamp" being treated as a keyword in certain contexts, probably not as you intended.

By way of example, the xref:userguide:commandlog:about.adoc[Command Log] module has an entity called `CommandJdo`.
This has a property called "timestamp", of type `java.sql.Timestamp`.

This defines a query using JDOQL:

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

This is declared using a JDO `@Query`; no errors are thrown at any stage.

However, running this query against SQL Server 2016 produced a different result first time it was run compared to subsequent times.

Running SQL Profiler showed the underlying SQL as:

[source,sql]
----
exec sp_prepexec @p1 output,N'@P0 datetime2',
N'SELECT ''org.isisaddons.module.command.dom.CommandJdo'' AS NUCLEUS_TYPE,
  A0.arguments,
  ...,
  A0.target,
  A0."timestamp",
  A0.transactionId,
  A0."user",
  ''2018-01-24 17:29:18.3'' AS NUCORDER0    // <1>
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 ',   // <2>
'2018-01-24 17:29:18.3000000'               // <3>
----
<1> discussed below ... this is the issue
<2> because the query is submitted with max rows programmatically set to 2.
<3> argument for @P0 (the timestamp parametr)


To unpick this, the key issue is the `NUCORDER0` column, which is then used in the `ORDER BY`.
However, because this is a literal value, the effect is no defined ordering.

The problem therefore is that in the JDOQL the "ORDER BY timestamp ASC", the "timestamp" is being evaluated as the current time - a built-in function.

My fix was to change the JDOQL to be:

[source,sql]
----
SELECT
FROM org.isisaddons.module.command.dom.CommandJdo
WHERE executeIn == 'FOREGROUND'
   && timestamp > :timestamp
   && startedAt != null
   && completedAt != null
ORDER BY this.timestamp ASC             // <1>
----
<1> Use "this." to qualify the timestamp

It wasn't necessary to qualify the other occurances of "timestamp" (though it would be no harm to do so, either).
