MySQLSyntaxErrorException: Unknown column 'y2_' in 'where clause'

Exception :
MySQLSyntaxErrorException: Unknown column 'y2_' in 'where clause'

出現時機  :
JPA : Criteria : Group by

Trace sql :
   config file : jdbc.properties
   property   : hibernate.showsql=true

Description :
使用 Projections.groupProperty 時 ,  hibernate 產生的 sql 會自動設定 select alias y0_, y1_, y2_, ... , 依此類推 ; 所以如果為了可以以 map 取得 criteria results , 通常會在 projectionList.add 加入 alias , 但是如果 alias 與原 propery name 相同 , 又在 criteria有 add 同名 property  的 condition, 則 hibernate 產生的 sql 會以 yi_ alias 出現在 where clause 中 , 然後就會出現 Exception : Unkonwn column 'yi_' in 'where clause'

e.g.
          Criteria criteria = this.getSession().createCriteria(DailyActiveCalendar.class);
          ProjectionList projectionList = Projections.projectionList();
         
          projectionList.add(Projections.groupProperty("room"), "r")
                  .add(Projections.groupProperty("aDate"), "date")
                  .add(Projections.groupProperty("active"), "active");

          criteria.setProjection(projectionList);

[STDOUT] Hibernate:
select this_.room as y0_, this_.aDate as y1_, this_.active as y2_
from daily_active_calendar this_
group by this_.room, this_.aDate, this_.active

如果加入 where conditions :

           criteria.add(Restrictions.eq("room", r))
                  .add(Restrictions.le("amount", r.getMaxSection()))                    
                  .add(Restrictions.lt("aDate", maxDate))
                  .add(Restrictions.eq("active", Boolean.TRUE));

[STDOUT] Hibernate:
select this_.room as y0_, this_.aDate as y1_, this_.active as y2_
from daily_active_calendar this_
where this_.room=? and this_.amount<=? and this_.aDate<? and y2_=?
group by this_.room, this_.aDate, this_.active

Solution :
在 where clause 中如果會使用到group by的項目時 , property name 與 alias 不要相同 , 例如上例中的     .add(Projections.groupProperty("active"), "active");
改為                   .add(Projections.groupProperty("active"), "isActive");

[STDOUT] Hibernate:
select this_.room as y0_, this_.aDate as y1_, this_.active as y2_
from daily_active_calendar this_
where this_.room=? and this_.amount<=? and this_.aDate<? and active_=?
group by this_.room, this_.aDate, this_.active