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

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.