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