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

[John Mazzitelli (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2832?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28051 ] 

John Mazzitelli commented on HHH-2832:
--------------------------------------

BTW: I think this also shows a completely separate bug.  Look at the HAVING's subselect WHERE clause:

and measuremen3_.SCHEDULE_ID=public.ON_MEASUREMENT_DATA_NUM_1H.SCHEDULE_ID

"public.ON_MEASUREMENT_DATA_NUM_1H.SCHEDULE_ID" ????

> 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