SQL Exception on AO find with DISTINCT

[Александр Верзаков]

Hi.

I have query

Scheme schemes[] = ao.find(Scheme.class, Query.select("PROJECT_KEY").distinct());

That transforms to 

SELECT DISTINCT FIELD_ORDER,DATA_SCHEME,ID,PROJECT_KEY,ISSUE_TYPE_NAME FROM AO_D3A8FC_SCHEME

When i expect something like

SELECT DISTINCT PROJECT_KEY FROM AO_D3A8FC_SCHEME;

and finally produces error

Servlet.service() for servlet [action] in context with path [] threw exception [com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
    Database:
        - name:MySQL
        - version:5.7.17-0ubuntu0.16.04.1
        - minor version:7
        - major version:5
    Driver:
        - name:MySQL Connector Java
        - version:mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad )
    java.sql.SQLException: Invalid value for getInt() - 'IN'] with root cause
java.sql.SQLException: Invalid value for getInt() - 'IN'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
        at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2517)
        at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2583)
        at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:283)
        at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:283)
        at net.java.ao.types.IntegerType.pullFromDatabase(IntegerType.java:38)
        at net.java.ao.types.IntegerType.pullFromDatabase(IntegerType.java:13)
        at net.java.ao.EntityManager.find(EntityManager.java:678)
        at net.java.ao.EntityManager.find(EntityManager.java:621)
        at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:134)
        at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:291)
        ... 2 filtered

Is this some kind of AO bug or i doing something wrong?

Is there are  something like workaround exist?

[Florian Maupas]

Hi,

I am facing the same issue, how did you workaround this issue ?

[Tomasz Stec]

Hi, first part with producing wrong SQL query could be connected with

@Preload("*") // also @Preload and @Preload() means the same because "*" is the default value

annotation in your 'Sheme' entity. Do you use it ?

The exception though, would be connected with some incopability inside your database table. I don't know what the problem is but if i were you i would delete the whole table and let AO create it again.