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

Atlassian JIRA | Andy Nguyen [Atlassian] | 11 months ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  1. 0

    h4. +Summary+ Retrieving deleted worklog with REST API causes 500 Error regarding *Datatype conversion* if using SQL Server: {code} java.sql.SQLException: Explicit conversion from data type ntext to bigint is not allowed {code} h4. +Steps to reproduce+ # install JIRA 7 with SQL Server 2012 # create an issue and log work on that issue # delete the worklog # access the following URL in the browser: * JIRA_Base_URL/rest/api/2/worklog/deleted h4. +Expected behavior+ The REST API call should be able to retrieve the deleted worklog successfully from *changeitem* table in the database h4. +Actual behavior+ 500 Error: * _*message*_ {noformat} 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 {noformat} * _*stacktrace*_ {noformat} 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> {noformat} h4. +Notes+ This doesn't happen if using the other supported databases e.g. Postgre

    Atlassian JIRA | 11 months ago | Andy Nguyen [Atlassian]
    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
  2. 0

    h4. +Summary+ Retrieving deleted worklog with REST API causes 500 Error regarding *Datatype conversion* if using SQL Server: {code} java.sql.SQLException: Explicit conversion from data type ntext to bigint is not allowed {code} h4. +Steps to reproduce+ # install JIRA 7 with SQL Server 2012 # create an issue and log work on that issue # delete the worklog # access the following URL in the browser: * JIRA_Base_URL/rest/api/2/worklog/deleted h4. +Expected behavior+ The REST API call should be able to retrieve the deleted worklog successfully from *changeitem* table in the database h4. +Actual behavior+ 500 Error: * _*message*_ {noformat} 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 {noformat} * _*stacktrace*_ {noformat} 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> {noformat} h4. +Notes+ This doesn't happen if using the other supported databases e.g. Postgre

    Atlassian JIRA | 11 months ago | Andy Nguyen [Atlassian]
    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
  3. 0

    QueryDSL generates JPQL instead of SQL for SQLDialect

    GitHub | 11 months ago | somasundaramsekar
    com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for from count(CUSTOMER.ID)
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    QueryDSL generates JPQL instead of SQL for SQLDialect

    Stack Overflow | 11 months ago | Somasundaram Sekar
    com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for from count(CUSTOMER.ID)

    Root Cause Analysis

    1. 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()
    2. com.querydsl.sql
      AbstractSQLQuery.fetch
      1. com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
      2. com.querydsl.sql.Configuration.translate(Configuration.java:453)
      3. com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
      3 frames
    3. com.atlassian.jira
      DatabaseWorklogStore.lambda$getRemovedWorklogs$15
      1. com.atlassian.jira.issue.worklog.DatabaseWorklogStore.lambda$getRemovedWorklogs$15(DatabaseWorklogStore.java:318)
      1 frame