Distinct query on an entity having an unlimited length text field results in exception on MS SQL Server

Description

I have an entity containing - beside other fields - one field with an unlimited text length:

1 2 3 4 5 6 7 8 9 10 11 @Preload @Table("UserActivity") public interface AoUserActivity extends Entity { @NotNull @Indexed Integer getUserId(); @Accessor(COMMENT_TEXT_COLUMN) @StringLength(StringLength.UNLIMITED) String getCommentText();

When I execute the following query on it

1 ao.find(AoUserActivity.class, Query.select("USER_ID").distinct());

it gets translated to

1 net.java.ao.sql SELECT DISTINCT * FROM AO_37A34A_USER_ACTIVITY

resulting in the following exception on Microsoft SQL server:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: - name:Microsoft SQL Server - version:10.50.2500 - minor version:50 - major version:10 Driver: - name:Microsoft JDBC Driver 4.0 for SQL Server - version:4.0.2206.100 com.microsoft.sqlserver.jdbc.SQLServerException: The ntext data type cannot be selected as DISTINCT because it is not comparable. at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:185) ~[na:na] at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:335) ~[na:na] at ch.mibex.stash.activities.db.LatestActivitiesDaoImpl.find(LatestActivitiesDaoImpl.scala:90) ~[na:na] at ch.mibex.stash.activities.db.LatestActivitiesDaoImpl.findUserIds(LatestActivitiesDaoImpl.scala:104) ~[na:na] at com.atlassian.activeobjects.tx.TransactionalProxy.invoke(TransactionalProxy.java:79) ~[na:na] at com.atlassian.activeobjects.tx.TransactionalProxy.access$000(TransactionalProxy.java:18) ~[na:na] at com.atlassian.activeobjects.tx.TransactionalProxy$1.doInTransaction(TransactionalProxy.java:63) ~[na:na] at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:25) ~[sal-core-2.13.4.jar:na] at com.atlassian.stash.internal.sal.spi.HostContextAccessorImpl.doInTransaction(HostContextAccessorImpl.java:27) ~[stash-platform-3.11.2.jar:na] at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:21) ~[sal-core-2.13.4.jar:na] at com.atlassian.activeobjects.internal.SalTransactionManager.inTransaction(SalTransactionManager.java:48) ~[na:na] at com.atlassian.activeobjects.internal.AbstractLoggingTransactionManager.doInTransaction(AbstractLoggingTransactionManager.java:19) ~[na:na] at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.executeInTransaction(EntityManagedActiveObjects.java:276) ~[na:na] at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.executeInTransaction(TenantAwareActiveObjects.java:383) ~[na:na] at com.atlassian.activeobjects.tx.TransactionalProxy.executeInTransaction(TransactionalProxy.java:57) ~[na:na] at com.atlassian.activeobjects.tx.TransactionalProxy.invokeInTransaction(TransactionalProxy.java:47) ~[na:na] at com.atlassian.activeobjects.tx.TransactionalProxy.invoke(TransactionalProxy.java:35) ~[na:na] at ch.mibex.stash.gc.ActivityGcJobRunner.gcUserActivities(ActivityGcJobRunner.scala:45) ~[na:na] at ch.mibex.stash.gc.ActivityGcJobRunner.runJob(ActivityGcJobRunner.scala:35) ~[na:na] at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153) [atlassian-scheduler-core-1.6.0.jar:na] at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118) [atlassian-scheduler-core-1.6.0.jar:na] at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97) [atlassian-scheduler-core-1.6.0.jar:na] at com.atlassian.scheduler.quartz2.Quartz2Job.execute(Quartz2Job.java:32) [atlassian-scheduler-quartz2-1.6.0.jar:na] at org.quartz.core.JobRunShell.run(JobRunShell.java:202) [quartz-2.2.1.jar:na] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) [quartz-2.2.1.jar:na] ... 102 frames trimmed Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The ntext data type cannot be selected as DISTINCT because it is not comparable. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) ~[sqljdbc-4.0.2206.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) ~[sqljdbc-4.0.2206.jar:na] at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172) ~[bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE] at net.java.ao.EntityManager.find(EntityManager.java:765) ~[na:na] at net.java.ao.EntityManager.find(EntityManager.java:675) ~[na:na] at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:181) ~[na:na] ... 25 common frames omitted

Shouldn't the query get translated to

1 SELECT DISTINCT USER_ID FROM AO_37A34A_USER_ACTIVITY

?

Environment

None

Testing Notes

Add notes...

Status

Assignee

Unassigned

Reporter

Mibex Software

Add-on Type

None

Team

None

CC

None

Risk factor

None

QA Kickoff Status

None

QA Demo Status

None

Story Points

0.25

Components

Affects versions

0.28.13

Priority

Critical
Configure