HQL is generating incomplete 'cross join' on executeUpdate

Question

I am working on a grails project. I have following query that I am trying to execute

String CHECK_FOR_HIGH_TRADE_VOLUME_QUERY = "Update LocationTrade lt set lt.hasVeryHighVolume=true where lt.locationIndices=? AND lt.trade.volume>20000";

...

LocationTrade.executeUpdate(CHECK_FOR_HIGH_TRADE_VOLUME_QUERY, [indices]);

The relationship between LocationTrade and Trade is unidirectional many-to-one. So, LocationTrade has a reference to Trade but Trade class does not have reference to the List of LocationTrade.

On execution, I get the following exception.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute update query; SQL [update location_trade cross join  set has_very_high_volume=1 where location_indices_id=? and volume>20000]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute update query

and

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'set has_very_high_volume=1 where location_indices_id=997 and volume>20000' at line 1

It seems that generated query is wrong. There should have been a join with the Trade table, but that is missing. I am unable to identify the error that I made here. Can some of you help me?

Creation Script for the two tables (I have stripped some of the uninteresting columns)

CREATE TABLE `location_trade` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `auto_status` varchar(255) DEFAULT NULL,
  `exclusion_reason_description` varchar(255) DEFAULT NULL,
  `exclusion_reason_id` bigint(20) DEFAULT NULL,
  `exclusion_reason_title` varchar(255) DEFAULT NULL,
  `location_indices_id` bigint(20) DEFAULT NULL,
  `manual_status` varchar(255) DEFAULT NULL,
  `trade_id` bigint(20) DEFAULT NULL,
  `absolute_price` decimal(19,6) DEFAULT NULL,
  `flag` varchar(255) DEFAULT NULL,
  `auto_exclusion_reason` varchar(255) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `exclusion_reason_text` varchar(255) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `has_very_high_volume` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK858985A90CAA966` (`location_indices_id`),
  KEY `FK858985AB5FA6A69` (`trade_id`),
  CONSTRAINT `FK858985A90CAA966` FOREIGN KEY (`location_indices_id`) REFERENCES `location_indices` (`id`),
  CONSTRAINT `FK858985AB5FA6A69` FOREIGN KEY (`trade_id`) REFERENCES `trade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25405 DEFAULT CHARSET=latin1;




CREATE TABLE `trade` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `comments` varchar(1020) DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `price` decimal(19,6) DEFAULT NULL,
  `price_type` varchar(255) DEFAULT NULL,
  `source_id` bigint(20) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `trade_date` datetime DEFAULT NULL,
  `trade_name` varchar(255) DEFAULT NULL,
  `volume` decimal(19,6) DEFAULT NULL,
  `volume_units` varchar(255) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK697F1642D085935` (`source_id`),
  CONSTRAINT `FK697F1642D085935` FOREIGN KEY (`source_id`) REFERENCES `job_source` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=26567 DEFAULT CHARSET=latin1;

Thanks

Answer 1

The Hibernate documentation says:

No join, 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.

lt.trade.volume is an implicit inner join between LocationTrade and Trade, so the query is invalid. You'll have to rewrite it to something like the following:

update LocationTrade lt set lt.hasVeryHighVolume=true where lt.locationIndices=? 
and lt.id in (
    select lt2.id from LocationTrade lt2 where lt2.trade.volume > 20000)

Or you'll have to use a SQL query instead.