JPA native query with mariadb and SqlResultSetMapping throwing a weird error

Question

The relationship:

A company has an address. Thats all I am testing for this simple example.

This is the address entity with the SqlResultSetMappings.

@Entity
@Table(name="addresses")
@SqlResultSetMappings({
       @SqlResultSetMapping(name = "addresses.simpleQuery", entities = {
               @EntityResult(entityClass = Company.class,
                       fields = {
                            @FieldResult(name = "id", column = "company_id")
                       }
               ),
               @EntityResult(entityClass = Address.class)
       })
})
public class Address {
     @Id
     @GeneratedValue(strategy = GenerationType.AUTO)
     private Integer id;

      @Version
      private Integer version;
      private Long createdAt;
      private Long updatedAt;
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name="company_id")
      private Company company;

      //some other fields like city, state etc

      //getters and setters
}

And this is the company entity

@Entity
@Table(name="companies")
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Version
    private Integer version;
    private Long createdAt;
    private Long updatedAt;

    @OneToMany(mappedBy = "company", fetch = FetchType.LAZY)
    private Set<Address> addressSet;    

    //some other fields like name

    //getters and setters
}

The hope is to execute a simple native query and map the result set like so:

List queryResult = entityManager.createNativeQuery("
select c.id as company_id, name, a.id as id 
from companies c, addresses a 
where c.id=a.company_id limit 3", "addresses.simpleQuery").getResultList();

Lo and behold...I execute the query and it blows up

Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.loader.Loader.doList(Loader.java:2556)
    at org.hibernate.loader.Loader.doList(Loader.java:2539)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
    at org.hibernate.loader.Loader.list(Loader.java:2364)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    ... 65 more
Caused by: java.sql.SQLException: No such column :created_2_2_0_
    at org.mariadb.jdbc.internal.common.queryresults.ColumnNameMap.getLabelIndex(ColumnNameMap.java:78)
    at org.mariadb.jdbc.internal.common.queryresults.ColumnNameMap.getIndex(ColumnNameMap.java:26)
    at org.mariadb.jdbc.MySQLResultSet.findColumn(MySQLResultSet.java:479)
    at org.mariadb.jdbc.MySQLResultSet.getLong(MySQLResultSet.java:170)
    at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:74)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:267)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:263)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:338)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2969)
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1695)
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1627)
    at org.hibernate.loader.Loader.getRow(Loader.java:1514)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:725)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:952)
    at org.hibernate.loader.Loader.doQuery(Loader.java:920)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2553)
    ... 74 more

Now before anyone asks why I am using a native query for this, I want to add that I have a more complex use case that is blowing up in a similar fashion (a much more complicated query). So I created the test above to reproduce the issue and see if I can get some pointers.

Am I doing something wrong? Why is created_2_2_0 being selected at all? I don't even have it in my select statement. Could this be a mariadb driver bug?

JPA native query with mariadb and SqlResultSetMapping throwing a weird error

0 Answers0