Select in Criteria using SQL

In an earlier post we saw how Criteria provided us with the ability to add restrictions in the where clause using pure SQL. The next question would then be if Criteria allows SQL in select clause ?
I tested the below code:

public static void testSqlSelect() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    int id = 4;
    criteria.add(Restrictions.eq("id", id));
    criteria.setProjection(Projections
        .projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master"))
            .add(Projections
                .sqlProjection(
                    "(select count(c.id) from CHILD_ENTITY c INNER JOIN ENTITY e on c.ENTITY_ID = e.id "
                    + "where e.id = " + id + ") as childCount",
                    new String[] { "childCount" },
                    new Type[] { Hibernate.LONG }
                )
            )
        );
    List<Object[]> rows = criteria.list();
    for (Object[] row : rows) {
        System.out.println(" Entity is " + row[0] + " and " + row[1] + " no of kids : " + row[2]);
    }
    session.close();
}

In this the normal Criteria returns me the details of Entity and its Master. But I have added a separate SQL query that will return me the number of ChildEntities associated with the particular record. The output of the code is as below:

    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_,
        (select
            count(c.id) 
        from
            CHILD_ENTITY c 
        INNER JOIN
            ENTITY e 
                on c.ENTITY_ID = e.id 
        where
            e.id = 4) as childCount 
    from
        ENTITY this_ 
    where
        this_.ID like ?
Hibernate: 
    /* load com.model.Master */ select
        master0_.ID as ID2_0_,
        master0_.DATA as DATA2_0_ 
    from
        ENTITY_MASTER master0_ 
    where
        master0_.ID=?
 Entity is entity100 and [Master] : ( id 2 , data : master No 2 )] no of kids : 1

As can be seen the SQL query was embedded within the select clause. The query being SQL, it could refer to any table or column whether known or unknown to Hibernate. The other parameters are used to update Hibernate with details of the return type from the statement. As it is an array, the inner select is capable of returning multiple columns.
An important thing to keep in mind though is that the sub-query is not allowed to return more than one row. This will result in an SQL exception if my entity row has more than one Entity_Child associations:

    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_,
        (select
            c.id 
        from
            CHILD_ENTITY c 
        INNER JOIN
            ENTITY e 
                on c.ENTITY_ID = e.id 
        where
            e.id = 2) as childCount 
    from
        ENTITY this_ 
    where
        this_.ID=?