insert-select query fails with NPE when select includes join

Description

I'm getting the following NPE when I convert a valid SELECT query into an INSERT SELECT query.

Here's the stack:

==================================================
Caused by: java.lang.NullPointerException
at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:310)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3275)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3067)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2945)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:482)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:253)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:92)
at org.jboss.ejb3.entity.TransactionScopedEntityManager.createQuery(TransactionScopedEntityManager.java:127)
at org.jboss.on.domain.util.PersistenceUtility.createInsertSelectQuery(PersistenceUtility.java:225)
at org.jboss.on.server.measurement.MeasurementBaselineManagerBean.calculateAutoBaselines(MeasurementBaselineManagerBean.java:80)
... 60 more
==================================================

I first start out with this valid SELECT query (I can run this in the Hibernate Tools in Eclipse - it works and returns valid data as expected):

---------- SELECT
min(d.min) AS baselineMin,
max(d.max) AS baselineMax,
avg(d.value) AS baselineMean,
CURRENT_TIMESTAMP AS computeTime,
d.id.scheduleId AS scheduleId
FROM
MeasurementDataNumeric1H d
JOIN
d.schedule s
LEFT JOIN
s.baseline b
WHERE
b.id IS NULL
AND d.id.timestamp BETWEEN :startTime AND :endTime
GROUP BY
d.id.scheduleId
HAVING
d.id.scheduleId IN (
SELECT
d1.id.scheduleId
FROM
MeasurementDataNumeric1H d1
WHERE
d1.id.timestamp <= :startTime
)
----------

Here is what the Hibernate Tools Dynamic SQL Preview view shows me is the actual, generated SQL:

---------- select
min(measuremen0_.minvalue) as col_0_0_,
max(measuremen0_.maxvalue) as col_1_0_,
avg(measuremen0_.value) as col_2_0_,
CURRENT_TIMESTAMP as col_3_0_,
measuremen0_.SCHEDULE_ID as col_4_0_
from
public.ON_MEASUREMENT_DATA_NUM_1H measuremen0_
inner join
public.ON_MEASUREMENT_SCHEDULE measuremen1_
on measuremen0_.SCHEDULE_ID=measuremen1_.id
left outer join
public.ON_MEASUREMENT_BASELINE measuremen2_
on measuremen1_.id=measuremen2_.SCHEDULE_ID
where
(
measuremen2_.id is null
)
and (
measuremen0_.TIME_STAMP between ? and ?
)
group by
measuremen0_.SCHEDULE_ID
having
measuremen0_.SCHEDULE_ID in (
select
measuremen3_.SCHEDULE_ID
from
public.ON_MEASUREMENT_DATA_NUM_1H measuremen3_
where
measuremen3_.TIME_STAMP<=?
)
----------

Now that I can see this works, I simply add this line to the beginning of that SELECT query:

---------- INSERT INTO MeasurementBaseline (baselineMin,baselineMax,baselineMean,computeTime,scheduleId)
----------

Here is what the Hibernate Tools Dynamic SQL Preview view shows me is the actual, generated SQL:

---------- insert
into
public.ON_MEASUREMENT_BASELINE
( id, BL_MIN, BL_MAX, BL_MEAN, BL_COMPUTE_TIME, SCHEDULE_ID )
select
nextval ('public.ON_MEASUREMENT_BASELINE_ID_SEQ'),
min(measuremen0_.minvalue) as col_0_0_,
max(measuremen0_.maxvalue) as col_1_0_,
avg(measuremen0_.value) as col_2_0_,
CURRENT_TIMESTAMP as col_3_0_,
measuremen0_.SCHEDULE_ID as col_4_0_
from
public.ON_MEASUREMENT_DATA_NUM_1H measuremen0_
inner join
public.ON_MEASUREMENT_SCHEDULE measuremen1_
on measuremen0_.SCHEDULE_ID=measuremen1_.id
left outer join
public.ON_MEASUREMENT_BASELINE measuremen2_
on measuremen1_.id=measuremen2_.SCHEDULE_ID
where
(
measuremen2_.id is null
)
and (
measuremen0_.TIME_STAMP between ? and ?
)
group by
measuremen0_.SCHEDULE_ID
having
measuremen0_.SCHEDULE_ID in (
select
measuremen3_.SCHEDULE_ID
from
public.ON_MEASUREMENT_DATA_NUM_1H measuremen3_
where
measuremen3_.TIME_STAMP<=?
)
----------
If I were to pass in the HQL (the INSERT INTO...SELECT) via:

entityManager.createQuery("INSERT INTO...SELECT...and the rest...")

I get the NPE.

Environment

None

Activity

Show:
Diego Plentz
September 24, 2007, 5:48 AM

John, I'm not a Jboss employee, but I'm a part of the community that does(or try to) support for Hibernate. I also have svn commit access, so I can (and wanna) help you. I'm just asking you to minimize as much as you can (minimal classes/entity's) to isolate at the maximum level the problem, making it easier to solve

Steve Ebersole
November 16, 2007, 6:29 PM

I deleted john's previous comment because it is unbecoming a JBoss employee in my mind to question the motives of people helping on this project of their own volition like you Diego.

I think what he really meant to say Diego was that he, as a person who writes a piece of software because he is paid to do so, really appreciates your volunteered time and effort on a project he uses extensively to achieve what he gets paid to do. So for him, and me, thank you for your work.

John Mazzitelli
November 16, 2007, 6:39 PM

Yup - apologies if I came across as snippy. In more delicate terms, I was trying to say that I don't think Diego can help here because a) I'm not sure how I can reduce this problem to a small, standalone app and b) the code that I wrote that DOES replicate this is not sitting in an open-source repo that Diego can run.

Steve Ebersole
November 16, 2007, 10:18 PM

The issue is in DotNode, when it tries to decide whether to render the join org.hibernate.hql.ast.tree.DotNode#dereferenceEntity

see org.hibernate.test.hql.BulkManipulationTest#testInsertWithSelectListUsingJoins

John Mazzitelli
November 17, 2007, 4:20 AM

I ran my TestNG tests against the latest hibernate build (r14201 of hibernate branch_3_2 which includes this fix) and can confirm that this problem is fixed along with another issue that I had that I didn't mention in here specifically. So, all looks well.

Assignee

Steve Ebersole

Reporter

John Mazzitelli

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure