Bug in Seam 2.1.2 when using composite id in pageable query

[palvin]

Hi,
I've downloaded the seam 2.1.2 yesterday and found a bug about pageable query if you using composite id in your entity. I set the maxresults property and got a error(the error is count function's parameter is incorrect) sql:

select
        * 
    from
        ( select
            count((sysitem0_.item_id,
            sysitem0_.org_id)) as col_0_0_ 
        from
            mtl_system_items sysitem0_ ) 
    where
        rownum <= ?

if I reverse to 2.1.1, the correct sql is:

select
        * 
    from
        ( select
            count(*) as col_0_0_ 
        from
            mtl_system_items sysitem0_,
            tims_org_v fndorg1_ 
        where
            sysitem0_.org_id = fndorg1_.org_id  ) 
    where
        rownum <= ?

Who can help me to resolve the problem?

[palvin]

Sorry for my careless and this incorrect sql only generate when I using

<s:link view="/sysItemList.xhtml" id="lastPage" value="Last">
                          <f:param name="firstResult" value="#{sysItemQry.lastFirstResult}"/>
                     </s:link>

in my page, on the other words, if I invoke the lastFirstResult method, the sql will occur.
BTW:
my query object was extends from EntityQuery and I using the seam application framework to do my query and got that error.

[mivasko]

I believe this is the same problem, it probably doesn't work if there is a subquery.
http://www.seamframework.org/Community/OrghibernatehqlastQuerySyntaxExceptionExpectingCLOSEFoundNull

You need to override getCountEjbql()

[palvin]

I compared the source of org.jboss.seam.framework.Query version 2.1.1GA with version 2.1.2 and found that the getCountEjbql() is different:
2.1.1GA:

protected String getCountEjbql()
    {
        String ejbql = getRenderedEjbql();
        Matcher fromMatcher = FROM_PATTERN.matcher(ejbql);
        if(!fromMatcher.find())
        {
            throw new IllegalArgumentException("no from clause found in query");
        } else
        {
            int fromLoc = fromMatcher.start(2);
            Matcher orderMatcher = ORDER_PATTERN.matcher(ejbql);
            int orderLoc = orderMatcher.find() ? orderMatcher.start(1) : ejbql.length();
            Matcher whereMatcher = WHERE_PATTERN.matcher(ejbql);
            int whereLoc = whereMatcher.find() ? whereMatcher.start(1) : orderLoc;
            String subject = "*";
            return (new StringBuilder(ejbql.length() + 15)).append("select count(").append(subject).append(") ").append(ejbql.substring(fromLoc, whereLoc).replace("join fetch", "join")).append(ejbql.substring(whereLoc, orderLoc)).toString().trim();
        }
    }

2.1.2

protected String getCountEjbql()
    {
        String ejbql = getRenderedEjbql();
        Matcher fromMatcher = FROM_PATTERN.matcher(ejbql);
        if(!fromMatcher.find())
            throw new IllegalArgumentException("no from clause found in query");
        int fromLoc = fromMatcher.start(2);
        Matcher orderMatcher = ORDER_PATTERN.matcher(ejbql);
        int orderLoc = orderMatcher.find() ? orderMatcher.start(1) : ejbql.length();
        Matcher groupMatcher = GROUP_PATTERN.matcher(ejbql);
        int groupLoc = groupMatcher.find() ? groupMatcher.start(1) : orderLoc;
        Matcher whereMatcher = WHERE_PATTERN.matcher(ejbql);
        int whereLoc = whereMatcher.find() ? whereMatcher.start(1) : groupLoc;
        String subject;
        if(getGroupBy() != null)
            subject = (new StringBuilder()).append("distinct ").append(getGroupBy()).toString();
        else
        if(useWildcardAsCountQuerySubject)
        {
            subject = "*";
        } else
        {
            Matcher subjectMatcher = SUBJECT_PATTERN.matcher(ejbql);
            if(subjectMatcher.find())
                subject = subjectMatcher.group(1);
            else
                throw new IllegalStateException("invalid select clause for query");
        }
        return (new StringBuilder(ejbql.length() + 15)).append("select count(").append(subject).append(") ").append(ejbql.substring(fromLoc, whereLoc).replace("join fetch", "join")).append(ejbql.substring(whereLoc, groupLoc)).toString().trim();
    }

So, override getCountEjbql() and setUseWildcardAsCountQuerySubject(true), the problem can resolve, but who can tell me the change reseason of 2.1.2?

[jovincen]

Sorry to resurrect this but seam's EntityQuery.validate turns off the wildcard in my query which breaks the count sql.

if (!PersistenceProvider.instance().supportsFeature(Feature.WILDCARD_AS_COUNT_QUERY_SUBJECT)) {
         setUseWildcardAsCountQuerySubject(false);
      }

The SQL in question is

                " SELECT  " +
          "   a " +
          " FROM " +
          "   aTable a "

Is this a bug in Seam? Or am I doing something wrong?

Cheers,
Jon