[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2832) getting erroneous behavior when specifying alias without column in WHERE

[John Mazzitelli (JIRA)]

getting erroneous behavior when specifying alias without column in WHERE
------------------------------------------------------------------------

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


This HQL works and generates proper SQL:

        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 IS NULL
           AND d.id.timestamp BETWEEN 500 AND 1000
      GROUP BY d.id.scheduleId
        HAVING d.id.scheduleId IN (SELECT d1.id.scheduleId
                                     FROM MeasurementDataNumeric1H d1
                                    WHERE d1.id.timestamp <= 500
                                      AND d1.id.scheduleId = d.id.scheduleId)

The first condition in the where-clause that is generated looks like this:

where
  (
   measuremen2_.id is null
  )

Now, simply add this line to the beginning of that HQL - just insert it at the top and do NOT change anything else within that main SELECT (you are in effect going to change it so it inserts the selected data that you got from the above HQL):

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

and that first where-condition now looks like this:

where
   (
    id is null
   )

and this causes problems.

Workaround is to change the WHERE clause so it uses "b.id" instead of just "b":

WHERE b.id IS NULL

In case you care, here is the full SQL generated by that original SELECT (as taken from the Eclipse Hibernate Tools Dynamic SQL Preview view):

==============================

SQL #0 types: double, double, double, timestamp, integer
-----------------
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 500 and 1000
  ) 
 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<=500 
    and measuremen3_.SCHEDULE_ID=measuremen0_.SCHEDULE_ID
  )


=====================================

and here is the generated SQL when I make it an INSERT INTO SELECT (you'll notice the HAVING's WHERE clause is also different):

DML #0 tables: public.ON_MEASUREMENT_BASELINE, public.MeasurementData, public.ON_MEASUREMENT_DATA_NUM_1H, public.ON_MEASUREMENT_SCHEDULE
-----------------
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
   (
    id is null
   ) 
   and (
    measuremen0_.TIME_STAMP between 500 and 1000
   ) 
  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<=500 
     and measuremen3_.SCHEDULE_ID=public.ON_MEASUREMENT_DATA_NUM_1H.SCHEDULE_ID
   )

I'm not sure what other information you'll need.

-- 
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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira