Hibernate issue

Recently encountered a small bug at work, related to Hibernate 3.3.2/3.4.0. It's already solved in the latest versions, but I found out after I spend some time debugging and since nobody at work generally has time/effort to upgrade libraries to the new version...

In other words, perhaps this will help someone out there.

The following java code contained the offending hql statement. It's an insert into select with a subselect in it. Not entirely trivial, in other words. Especially as JPA does not support this sort of statement.

public static final String BULKINSERT = 
                "INSERT INTO Settings " + 
                "(period, section, method) " + 
                "SELECT tp, sc, 'DUM' " + 
                "FROM SectionCode sc, TimePeriod tp " +
                "WHERE sc.sectionId = :sectionId " +
                "AND sc.deleted = false " +
                "AND tp.periodnr = :period " +
                "AND NOT EXISTS (" +
                "    SELECT '' " +
                "    FROM Settings ai " +
                "    WHERE ai.period.periodnr = :period " +
                "    AND ai.section = sc)";

Turning on Hibernate logging in jboss:

<category name="org.hibernate"> 
    <priority value="TRACE"/>  
</category>

I noticed it was translated by Hibernate into the following query.

14:02:21,934 TRACE [QueryPlanCache] located HQL query plan in cache (INSERT INTO Settings (period, section, method) SELECT tp, sc, 'DUM' FROM SectionCode sc, TimePeriod tp WHERE sc.sectionId = :sectionId AND sc.deleted = false AND tp.periodnr = :period AND NOT EXISTS (    SELECT ''     FROM Settings ai     WHERE ai.period.periodnr = :period     AND ai.section = sc))
14:02:21,934 TRACE [HQLQueryPlan] executeUpdate: INSERT INTO Settings (period, section, method) SELECT tp, sc, 'DUM' FROM SectionCode sc, TimePeriod tp WHERE sc.sectionId = :sectionId AND sc.deleted = false AND tp.periodnr = :period AND NOT EXISTS (    SELECT ''     FROM Settings ai     WHERE ai.period.periodnr = :period     AND ai.section = sc)
14:02:21,948 TRACE [QueryParameters] named parameters: {sectionId=W, period=6}
14:02:21,953 DEBUG [AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
14:02:21,953 DEBUG [ConnectionManager] opening JDBC connection
14:02:22,232 DEBUG [SQL] insert into SETTINGS ( SETTINGID, TIMEPERIODID, SECTIONCODEID, METHOD) select SETTING_SEQUENCE.nextval, timeperiod1_.TIMEPERIODID AS col_0_0_, sectioncode0_.SECTIONCODEID AS col_1_0_, 'DUM' AS col_2_0_ FROM SECTIONCODE sectioncode0_, TIMEPERIOD timeperiod1_ WHERE sectioncode0_.SECTIONCODEID = ? AND sectioncode0_.DELETED = 'N' AND timeperiod1_.TIMEPERIODID = ? AND NOT (EXISTS (SELECT '' FROM SETTINGS setting2_ WHERE setting2_.TIMEPERIODID = ? AND setting2_.SECTIONCODEID = SECTIONCODEID

insert into SETTINGS ( SETTINGID, TIMEPERIODID, SECTIONCODEID, METHOD) 
select SETTING_SEQUENCE.nextval, 
timeperiod1_.TIMEPERIODID AS col_0_0_,
       sectioncode0_.SECTIONCODEID AS col_1_0_,
       'DUM' AS col_2_0_
  FROM SECTIONCODE sectioncode0_, TIMEPERIOD timeperiod1_
 WHERE     sectioncode0_.SECTIONCODEID = 'W'
       AND sectioncode0_.DELETED = 'N'
       AND timeperiod1_.TIMEPERIODID = 6
       AND NOT (EXISTS
                   (SELECT ''
                      FROM SETTINGS setting2_
                     WHERE setting2_.TIMEPERIODID = 6
                           AND setting2_.SECTIONCODEID =
                                  SECTIONCODEID));

It's hard to tell at first, but in the output above, in the subselect, it says "AND setting2_.SECTIONCODEID = SECTIONCODEID". It should be "AND setting2_.SECTIONCODEID = sectioncode0_.SECTIONCODEID". The current implementation causes the second SECTIONCODEID to automatically refer back to settings2_ table, causing the expression to always evaluate to true. This explained why I never saw any records being added in certain cases.

In order to get the query to work properly, I was forced to reach out to native SQL instead of HQL. Upgrading our software to the latest and greatest Hibernate is a tad too involved for now.

References

HHH-5274 - HQL-Insert with Select and Sub-Select fails

https://hibernate.onjira.com/browse/HHH-5274