Issue Summary

Retrieving deleted worklog with REST API causes 500 Error regarding Datatype conversion if using SQL Server:

java.sql.SQLException: Explicit conversion from data type ntext to bigint is not allowed

Steps to reproduce

1. install JIRA 7 with SQL Server 2012
2. create an issue and log work on that issue
3. delete the worklog
4. access the following URL in the browser: JIRA_Base_URL/rest/api/2/worklog/deleted

Expected behavior

The REST API call should be able to retrieve the deleted worklog successfully from changeitem table in the database

Actual behavior

500 Error:

• message

  Caught SQLException for select top 1000 cast(CHANGE_ITEM.oldvalue as bigint), max(CHANGE_GROUP.created) from dbo.changeitem CHANGE_ITEM left join dbo.changegroup CHANGE_GROUP on CHANGE_ITEM.groupid = CHANGE_GROUP.id where CHANGE_ITEM.field = ? and (CHANGE_GROUP.created >= ? and CHANGE_GROUP.created <= ?) and (cast(CHANGE_ITEM.oldvalue as bigint) not in (select WORKLOG.id from dbo.worklog WORKLOG)) group by cast(CHANGE_ITEM.oldvalue as bigint) order by max(CHANGE_GROUP.created) asc
  

• stacktrace

  com.querydsl.core.QueryException: Caught SQLException for select top 1000 cast(CHANGE_ITEM.oldvalue as bigint), max(CHANGE_GROUP.created) from dbo.changeitem CHANGE_ITEM left join dbo.changegroup CHANGE_GROUP on CHANGE_ITEM.groupid = CHANGE_GROUP.id where CHANGE_ITEM.field = ? and (CHANGE_GROUP.created >= ? and CHANGE_GROUP.created <= ?) and (cast(CHANGE_ITEM.oldvalue as bigint) not in (select WORKLOG.id from dbo.worklog WORKLOG)) group by cast(CHANGE_ITEM.oldvalue as bigint) order by max(CHANGE_GROUP.created) asc
   at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
   at com.querydsl.sql.Configuration.translate(Configuration.java:453)
   at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
   at com.atlassian.jira.issue.worklog.DatabaseWorklogStore.lambda$getRemovedWorklogs$15(DatabaseWorklogStore.java:318)
   at com.atlassian.jira.issue.worklog.DatabaseWorklogStore$$Lambda$909/1462798570.runQuery(Unknown Source)
   at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:62)
   at com.atlassian.jira.database.DefaultQueryDslAccessor$$Lambda$709/998458594.apply(Unknown Source)
   at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:98)
   at com.atlassian.jira.database.DatabaseAccessorImpl$$Lambda$710/632973456.run(Unknown Source)
   at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:67)
   at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:93)
   at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:61)
   at com.atlassian.jira.issue.worklog.DatabaseWorklogStore.getRemovedWorklogs(DatabaseWorklogStore.java:301)
   at com.atlassian.jira.issue.worklog.DatabaseWorklogStore.getWorklogsDeletedSince(DatabaseWorklogStore.java:269)
   at com.atlassian.jira.issue.worklog.DefaultWorklogManager.getWorklogsDeletedSince(DefaultWorklogManager.java:170)
   at com.atlassian.jira.bc.issue.worklog.DefaultWorklogService.getWorklogsDeletedSince(DefaultWorklogService.java:630)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   ...
  Caused by: java.sql.SQLException: Explicit conversion from data type ntext to bigint is not allowed.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
   at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
   at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
   ... 270 more
  </stack-trace>
  </status>
  

Notes

This doesn't happen if using the other supported databases e.g. PostgreSQL

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available