Error In Native Query where column is mapped twice

Hibernate ORM
1

I’m in the process of upgrading a project using Hibernate ORM from 5.3.14 to 6.1.3. I’ve got a number of SQLQuery that I’ve moved to NativeQuery, and am now getting the error:

jakarta.persistence.PersistenceException: Converting org.hibernate.exception.GenericJDBCExceptionto JPAPersistenceException : Unable to find column position by name: user_id27_190_0_

            NativeQuery query = getSession().createNativeQuery(sql);
...
            query.addEntity("job", TMDBJob.class);
            results = query.list();

The SQL starts with

"select /*+ index(job IDX_JOB_RUNNABLE) */ {job.*} from Job_t  job where ..."

where the comment /* …*/ is just there to identify it in the logs. When I look at the actual SQL in those logs, I see (after pretty printing), the following. Note the duplicated aliases for job_group_fk8_190_0_, causing subsequent columns to have aliases that are off by 1. The same goes for study_fk24_190_0_:

SELECT /*+ index(job IDX_JOB_RUNNABLE) */
    job.job_pk                         job_pk1_190_0_,
    job.completed_units                completed_units2_190_0_,
...
    job.job_group_fk                   job_group_fk8_190_0_,
    job.mgr_id                         job_group_fk8_190_0_,
    job.job_state                      mgr_id9_190_0_,
...
    job.total_units                    study_fk24_190_0_,
    job.update_sequence                study_fk24_190_0_,
    job.user_id                        total_units25_190_0_,
    job.worker                         update_sequence26_190_0_

So, when it comes around to user_id, it does not find any column by that name.

I suspect this is because we have a couple of unusual mapping in our Job Entity, where both an entity and its foreign key are mapped to the same column, so that we can use the FK without having to first fetch the entity just to get its PK, Here’s the one for study:

@ManyToOne(targetEntity = TMDBStudy.class, fetch = FetchType.LAZY)
@JoinColumn(name = "STUDY_FK")
private TMStudy study;

@Column(name = "STUDY_FK", nullable = false, insertable = false, updatable = false)
@Access(AccessType.FIELD)
private Long studyFk;

The other is for job_group.

This works without issue in 5.3.24 and has for some time.
Is this a bug? And/or is there a better way to achieve this, now?

Thanks.

2

Yeah, this looks like a bug. Please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue.

3

Sorry, I forgot to note here that I created the issue, [HHH-15608] - Hibernate JIRA.

1 Like
4

Is this bug back? I’m upgrading to Spring boot 3.0.6 with hibernate-core version 6.1.7.Final from Spring boot 2.7.1 with hibernate-core 5.6.9.Final.

Here is the setup:

Query q = ...;

List rList = q.getResultList(); // <- Exception is thrown here
ArrayList<StockItemEvent> result = new ArrayList<>();
result.addAll(rList);

The query:

SELECT
    	sie.*
    FROM
    	stock_item_events sie
    	INNER JOIN stock_items si ON si.id = sie.stockItemId
    WHERE
        ...

The exception:

2023-05-09 18:24:08 - Column 'classification' not found.
2023-05-09 18:24:08 - Servlet.service() for servlet [dispatcherServlet] in context with path [/snapi] threw exception [Request processing failed: jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.SQLGrammarException` to JPA `PersistenceException` : Unable to find column position by name: classification] with root cause
java.sql.SQLException: Column 'classification' not found.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
	at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:584)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.findColumn(HikariProxyResultSet.java)
...

What’s expected:

I’m selecting some rows from stock_item_events table (mapped to object StockItemEvent), neither have the classification field. However, StockItemEvent has the following link (using foreign key) to StockItem object (mapped to stock_items table):

    @ManyToOne
    @NotFound(action = NotFoundAction.IGNORE)
    @JoinColumn(name = "stockItemId", referencedColumnName = "id")
    private StockItem stockItem;

and StockItem does have the classification field.

The query worked in 5.6.9.Final from the start, but throws that strange exception on 6.1.7.Final.

Is this not the same issue?

5

Try Hibernate 6.2.2.Final and if the problem persists, create a new JIRA issue please and attach a reproducer.

1 Like
6

Thank you, yes, that fixed it (still org.springframework.boot:3.0.6)! Steps needed:

  1. In build.gradle:
    implementation('org.springframework.boot:spring-boot-starter-data-jpa') {
        exclude group: 'org.hibernate.orm', module: 'hibernate-core'
    }
    implementation "org.hibernate.orm:hibernate-core:6.2.2.Final"
  1. In application.yml

Make sure jpa.hibernate.properties.hibernate.dialect is set to org.hibernate.dialect.MySQLDialect (see Hibernate Tutorial: Dialects in Hibernate - javatpoint. for correct values). I had org.hibernate.dialect.MySQL5Dialect, which caused server crash because it’s no longer there. Here is the exception:

Cannot resolve reference to bean 'jpaSharedEM_entityManagerFactory' while setting bean property 'entityManager'
...
Suppressed: java.lang.ClassNotFoundException: org.hibernate.dialect.MySQL5Dialect
  1. Prepare for a lot of these:
Schema-validation: wrong column type encountered in column [id] in table [...]; found [tinyint (Types#TINYINT)], but expecting [tinyint unsigned (Types#INTEGER)]
7

Please create a JIRA with a reproducer for 3. I don’t think the validation error is correct.

8

Created: [HV-1948] - Hibernate JIRA

9

The HV project is for the Hibernate Validator project, not ORM. I’ll migrate the issue.