Hibernate Exception on MySQL Cross Join Query

Question

I'm trying to perform a bulk delete of an object, Feature, which has a birdirectional ManyToOne relationship with another class, FeaturesMetadata. I'm having a SQLGrammerException thrown.

The hql I'm using:

String hql = "delete from Feature F where F.featuresMetadata.stateGeoId = :stateGeoId";

Turning on show SQL, the following is generated:

 delete from FEATURE cross join FEATURESMETADATA featuresme1_ where STATEGEOID=?

Running the SQL directly in the db client gives this exception:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cross join FEATURESMETADATA featuresme1_ where stategeoid='01'' at line 1

Since the generated SQL is throwing the Exception, I tried changing dialects from MySQL5InnoDBDialect to MySQLInnoDBDialect, but no change.

Can anyone assist?

Does FeaturesMetaData have a FK to Feature? – Mindfulgeek Aug 30 '11 at 16:33 — Mindfulgeek, Aug 30 '11 at 16:33
Feature has a FK to FeaturesMetadata. – Jason Aug 30 '11 at 16:37 — Jason, Aug 30 '11 at 16:37

score 50 · Accepted Answer · answered Aug 30 '11 at 17:14

50

You may not have joins in such a HQL query. Quote from the reference documentation:

No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

So I guess something like this should work:

delete from Feature F where F.id in 
    (select f2.id from Feature f2 where f2.featuresMetadata.stateGeoId = :stateGeoId)

answered Aug 30 '11 at 17:14

JB Nizet

678,734
91
1,224
1,255

Doh! I've read that several times and still didn't click. Answer accepted. – Jason Aug 30 '11 at 17:49
14

Hi, I'm surprised it worked for you since I'm getting: org.hibernate.exception.GenericJDBCException: You can't specify target table 'Users' for update in FROM clause. It happens since you can't modify the same table which you use in the SELECT part (in your case - the Feature table). This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html. Any suggestions? – forhas Dec 21 '12 at 09:09
11

Wow. I thought HQL was about making queries more simplified and object-oriented than SQL. I don't see why Hibernate can't figure out on its own how to translate the relationship between the objects into an SQL query without an explicit join. – Eyal Roth Feb 10 '14 at 09:00
Can't understand how this answers to the main question: in the HQL he's not making any join... – Phate Jul 03 '20 at 15:47
1

@Phate He's doing an **implicit** join, you can see that Hibernate translates the query to a cross join. – 3VYZkz7t May 10 '21 at 12:44

score 1 · Answer 2 · answered Apr 04 '19 at 12:25

I had the same issue and struggled to find a sensible answer. It seems that, even if you get this approach to work, the SQL generated is highly inefficient (according to what I have read).

So I took a step back and did the following:

    List<Properties> props = propDao.findPropertiesByHotelCode(hotel.getCode());
    propDao.deleteInBatch(props);
    propDao.flush();

Basically rather tan trying to 'delete where', I'm doing a select where and then deleting in batch the set that I retrieved.

Hope this helps.

score 0 · Answer 3 · answered Jun 15 '21 at 04:10

This is indeed rather poor from Hibernate. But you can solve it like this in a repo: (at least in PostgreSQL, not sure if this syntax should be modified for MySql)

@Modifying
@Query(nativeQuery = true, value = """
    DELETE FROM feature f
    USING features_metadata fd
    WHERE f.features_metadata_id = fd.id AND fd.state_geo_id = :stateGeoId
    """)
void deleteByStateGeoIdId(@Param("stateGeoId") UUID stateGeoId);

Hibernate Exception on MySQL Cross Join Query

3 Answers3

Linked