[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2833) NullPointerException in insert-select query

Sunday, 23 September 2007

    [
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2833?page=c...
] 

John Mazzitelli commented on HHH-2833:
--------------------------------------

...
 If you don't have time to digg into your code... we don't
have too 
I'm assuming non-JBoss employees won't give a crap about this bug, hence, I
don't expect you to care Diego.

I actually put together a very small TestNG unit test, but you would not only need the
test, but also you would need to svn co a couple maven modules that we have.  Again, its
internal to JBoss, I've ask Charles Crouch to pass a message to Steve to discuss how
we can work if possible (I'm assuming Diego does not have access to the internal Jboss
corp SVN, so Diego I'm not asking you to do any work - continue on fixing the other
1000 bugs ;-).  

Its actually simple to replicate using my small TestNG test and our maven modules (I can
send replication procedures when appropriate - it is essentially a couple "svn
co" checkouts and a "mvn install" command or two).  I could even set it up
on my box and let you VNC over to it or connect remotely via JPDA.  Here's the testng
test:

-------------
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.testng.annotations.Test;
import org.jboss.on.domain.test.AbstractEJB3Test;

@Test
public class MeasurementBaselineHHH2833Test
extends AbstractEJB3Test
{
   public void testSelectQueryThatSucceeds()
   throws Exception
   {
      getTransactionManager().begin();
      try
      {
         EntityManager entityManager = getEntityManager();
         Query q = entityManager.createQuery( getSelectQueryThatSucceeds() );
         assert q != null;
         q.setParameter( "startTime", 500L ).setParameter( "endTime",
1000L );
         q.getResultList();
      }
      finally
      {
         getTransactionManager().rollback();
      }
   }

   public void testHibernateBug_HHH_2833_WithInsert()
   throws Exception
   {
      getTransactionManager().begin();
      try
      {
         EntityManager entityManager = getEntityManager();
         Query q  = entityManager.createQuery( getInsertQueryThatFails() );
         assert q != null;
         q.setParameter( "startTime", 500L ).setParameter( "endTime",
1000L );
         q.executeUpdate();
      }
      finally
      {
         getTransactionManager().rollback();
      }
   }

   public void testHibernateBug_HHH_2833_WithDelete()
   throws Exception
   {
      getTransactionManager().begin();
      try
      {
         EntityManager entityManager = getEntityManager();
         Query q = entityManager.createQuery( getDeleteQueryThatFails() );
         assert q != null;
         q.setParameter( "startTime", 500L ).setParameter( "endTime",
1000L );
         q.executeUpdate();
      }
      finally
      {
         getTransactionManager().rollback();
      }
   }

   private String getSelectQueryThatSucceeds()
   {
      return "      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) ";
   }

   private String getInsertQueryThatFails()
   {
      return "INSERT INTO MeasurementBaseline
(baselineMin,baselineMax,baselineMean,computeTime,scheduleId) "
             + getSelectQueryThatSucceeds();
   }

   private String getDeleteQueryThatFails()
   {
      // only the SELECT clause is different - the FROM on down is the same as the
getSelectQueryThatSucceeds query
      return "DELETE MeasurementBaseline AS doomed WHERE doomed.scheduleId IN "
             + "( "
             + "   SELECT d.id.scheduleId AS scheduleId "
             + "     FROM MeasurementDataNumeric1H d "
             + "          JOIN d.schedule s "
             + "          LEFT JOIN s.baseline b "
             + "    WHERE b.id IS NOT NULL "
             + "      AND d.id.timestamp BETWEEN :startTime AND :endTime "
             + "      AND b.userEntered = FALSE "
             + " GROUP BY d.id.scheduleId "
             + "   HAVING d.id.scheduleId IN (SELECT d1.id.scheduleId "
             + "                                FROM MeasurementDataNumeric1H d1
"
             + "                               WHERE d1.id.timestamp <=
:startTime) "
             + ") ";
   }
}

...
 NullPointerException in insert-select query
 -------------------------------------------

                 Key: HHH-2833
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2833
             Project: Hibernate3
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.2.3
            Reporter: John Mazzitelli

 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. 
-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2833) NullPointerException in insert-select query