[My blog]

A simple SQL:

SELECT * FROM Animal AS a1

INNER JOIN Animal AS a2 on a1.mother_id = a2.ID and a1.body_weight < :aLimit

Another:

SELECT * FROM Animal AS a1 INNER JOIN Animal AS a2 on a1.mother_id = a2.ID

WHERE a1.body_weight < :aLimit

Which is the difference in term of results ? There isn’t.

Some DB engine don’t make difference, between the two SQLs, even in term of execution plan but… you know, there are some RDBMS not so smart (guess which one [:#] ).

With the new HQL parser, based on ANTLR, we are supporting a new clause named “with”.

The HQL:

from Animal a inner join a.mother as m with m.bodyWeight < :someLimit

The result SQL using this mapping :

select
        animal0_.id as id14_0_,
        animal1_.id as id14_1_,
        animal0_.description as descript2_14_0_,
        animal0_.body_weight as body3_14_0_,
        animal0_.mother_id as mother4_14_0_,
        animal0_.father_id as father5_14_0_,
        animal0_.zoo_id as zoo6_14_0_,
        animal0_.serialNumber as serialNu7_14_0_,
        animal0_1_.bodyTemperature as bodyTemp2_15_0_,
        animal0_3_.pregnant as pregnant17_0_,
        animal0_3_.birthdate as birthdate17_0_,
        animal0_4_.owner as owner18_0_,
        animal0_7_.name_first as name2_21_0_,
        animal0_7_.name_initial as name3_21_0_,
        animal0_7_.name_last as name4_21_0_,
        animal0_7_.nickName as nickName21_0_,
        animal0_7_.height as height21_0_,
        animal0_7_.intValue as intValue21_0_,
        animal0_7_.floatValue as floatValue21_0_,
        animal0_7_.bigDecimalValue as bigDecim9_21_0_,
        animal0_7_.bigIntegerValue as bigInte10_21_0_,
        case
            when animal0_2_.reptile is not null then 2
            when animal0_5_.mammal is not null then 5
            when animal0_6_.mammal is not null then 6
            when animal0_4_.mammal is not null then 4
            when animal0_7_.mammal is not null then 7
            when animal0_1_.animal is not null then 1
            when animal0_3_.animal is not null then 3
            when animal0_.id is not null then 0
        end as clazz_0_,
        animal1_.description as descript2_14_1_,
        animal1_.body_weight as body3_14_1_,
        animal1_.mother_id as mother4_14_1_,
        animal1_.father_id as father5_14_1_,
        animal1_.zoo_id as zoo6_14_1_,
        animal1_.serialNumber as serialNu7_14_1_,
        animal1_1_.bodyTemperature as bodyTemp2_15_1_,
        animal1_3_.pregnant as pregnant17_1_,
        animal1_3_.birthdate as birthdate17_1_,
        animal1_4_.owner as owner18_1_,
        animal1_7_.name_first as name2_21_1_,
        animal1_7_.name_initial as name3_21_1_,
        animal1_7_.name_last as name4_21_1_,
        animal1_7_.nickName as nickName21_1_,
        animal1_7_.height as height21_1_,
        animal1_7_.intValue as intValue21_1_,
        animal1_7_.floatValue as floatValue21_1_,
        animal1_7_.bigDecimalValue as bigDecim9_21_1_,
        animal1_7_.bigIntegerValue as bigInte10_21_1_,
        case
            when animal1_2_.reptile is not null then 2
            when animal1_5_.mammal is not null then 5
            when animal1_6_.mammal is not null then 6
            when animal1_4_.mammal is not null then 4
            when animal1_7_.mammal is not null then 7
            when animal1_1_.animal is not null then 1
            when animal1_3_.animal is not null then 3
            when animal1_.id is not null then 0
        end as clazz_1_
    from
        Animal animal0_
    left outer join
        Reptile animal0_1_
            on animal0_.id=animal0_1_.animal
    left outer join
        Lizard animal0_2_
            on animal0_.id=animal0_2_.reptile
    left outer join
        Mammal animal0_3_
            on animal0_.id=animal0_3_.animal
    left outer join
        DomesticAnimal animal0_4_
            on animal0_.id=animal0_4_.mammal
    left outer join
        Cat animal0_5_
            on animal0_.id=animal0_5_.mammal
    left outer join
        Dog animal0_6_
            on animal0_.id=animal0_6_.mammal
    left outer join
        Human animal0_7_
            on animal0_.id=animal0_7_.mammal
    inner join
        Animal animal1_
            on animal0_.mother_id=animal1_.id
            and (
                animal1_.body_weight<@p0
            )
    left outer join
        Reptile animal1_1_
            on animal1_.id=animal1_1_.animal
    left outer join
        Lizard animal1_2_
            on animal1_.id=animal1_2_.reptile
    left outer join
        Mammal animal1_3_
            on animal1_.id=animal1_3_.animal
    left outer join
        DomesticAnimal animal1_4_
            on animal1_.id=animal1_4_.mammal
    left outer join
        Cat animal1_5_
            on animal1_.id=animal1_5_.mammal
    left outer join
        Dog animal1_6_
            on animal1_.id=animal1_6_.mammal
    left outer join
        Human animal1_7_
            on animal1_.id=animal1_7_.mammal;
    @p0 = 1

First… easy HQL with complex mapping mean complex SQL but… not a big pain for NH’s users [;)]

Note this:

inner join
    Animal animal1_
        on animal0_.mother_id=animal1_.id
        and (
            animal1_.body_weight<@p0
        )

stock_stack……stumb…sfrfrfrfrfrfr (biglia, sponda e filotto).