Yes, I know... you will say that I have coded something incorrectly. But I am not sure based on the debug statements from Hibernate how this is possible. Basically, I create two criterias with *exactly* the same restrictions. However, on the Count criteria, I set a projection Projectsions.rowCount() to see how many are available.

Now here is the kicker. The sql for both of the queries is exactly the same except one has a count(*) and the other does not. However, the count criteria returns a different result than the other.

There is an inner join going on, but I am not sure why a count() would change the expected results.

COUNT Criteria:
DEBUG - select count(*) as y0_ from levelInstances this_ inner join levelMetaDataValues lmdv1_ on this_.id=lmdv1_.levelId inner join levelMetaDataTypes lmdt2_ on lmdv1_.levelMetaDataType=lmdt2_.id where this_.levelType=? and (this_.name like ?) and 1=1
Hibernate: select count(*) as y0_ from levelInstances this_ inner join levelMetaDataValues lmdv1_ on this_.id=lmdv1_.levelId inner join levelMetaDataTypes lmdt2_ on lmdv1_.levelMetaDataType=lmdt2_.id where this_.levelType=? and (this_.name like ?) and 1=1
DEBUG - binding 'tes%' to parameter: 2
DEBUG - returning '49' as column: y0_


ALL Criteria:
select this_.id as id0_2_, this_.VERSION as VERSION0_2_, this_.name as name0_2_, this_.startDate as startDate0_2_, this_.dueDate as dueDate0_2_, this_.path as path0_2_, this_.depth as depth0_2_, this_.levelType as levelType0_2_, lmdv1_.id as id4_0_, lmdv1_.value as value4_0_, lmdv1_.levelInstanceId as levelIns3_4_0_, lmdv1_.levelMetaDataType as levelMet4_4_0_, lmdt2_.id as id3_1_, lmdt2_.HTMLWidgetId as HTMLWidg2_3_1_, lmdt2_.uniqueName as uniqueName3_1_, lmdt2_.labelKey as labelKey3_1_, lmdt2_.name as name3_1_, lmdt2_.display_column as display6_3_1_, lmdt2_.position as position3_1_, lmdt2_.defaultValue as defaultV8_3_1_, lmdt2_.levelTypeId as levelTyp9_3_1_ from levelInstances this_ inner join levelMetaDataValues lmdv1_ on this_.id=lmdv1_.levelId inner join levelMetaDataTypes lmdt2_ on lmdv1_.levelMetaDataType=lmdt2_.id where this_.levelType=? and (this_.name like ?) and 1=1 limit ?
DEBUG - binding 'tes%' to parameter: 2
DEBUG - returning null as column: value4_0_
DEBUG - returning 'printer' as column: name3_1_
DEBUG - returning '0' as column: display6_3_1_
DEBUG - returning '0' as column: position3_1_
DEBUG - returning '' as column: defaultV8_3_1_
DEBUG - returning '0' as column: VERSION0_2_
DEBUG - returning 'test' as column: name0_2_
DEBUG - returning '2007-03-06 14:35:00' as column: startDate0_2_
DEBUG - returning '2007-03-09 14:35:00' as column: dueDate0_2_
... more ...


Pulling out the individual fields, you can see that the queries line up: (top one is count, bottom one is no projection)
select ... from levelInstances this_ inner join levelMetaDataValues lmdv1_ on this_.id=lmdv1_.levelId inner join levelMetaDataTypes lmdt2_ on lmdv1_.levelMetaDataType=lmdt2_.id where this_.levelType=? and (this_.name like ?) and 1=1
select ... from levelInstances this_ inner join levelMetaDataValues lmdv1_ on this_.id=lmdv1_.levelId inner join levelMetaDataTypes lmdt2_ on lmdv1_.levelMetaDataType=lmdt2_.id where this_.levelType=? and (this_.name like ?) and 1=1

But the count select returns more rows than the second select returns.

Any help greatly appreciated.

ah-ha! It would appear that I had to change my projection!

From:
maxCriteria.setProjection(Projections.rowCount());

To:
maxCriteria.setProjection(Projections.countDistinct("id"));

Because the outer join was returning non-distinct results in its count(*) when I did the projection.

Hope this helps someone.