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] | 7 months ago
  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 | 7 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 | 7 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 | 7 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 | 7 months ago | Somasundaram Sekar
    com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for from count(CUSTOMER.ID)
  6. 0

    SQLQueryFactory .list() with offset and limit causes SQLSyntaxErrorException in Oracle11g

    GitHub | 1 year ago | sarababaei
    com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for select * from ( select a.*, rownum rn from ( select TB_CLEARING_SETUP.CODE, TB_CLEARING_SETUP.CODE, TB_CLEARING_SETUP.TITLE, TB_CLEARING_SETUP.PARAMETER_VALUE from CHK.TB_CLEARING_SETUP TB_CLEARING_SETUP order by TB_CLEARING_SETUP.CODE desc ) a) where rn > 2 and rownum <= 3

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    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