Re: [eclipselink-users] EL building select count() instead of select cou

[Anuj Lal <alal@xxxxxxxxxxx>]

                Eclipselink    11.1.1.3.0 (eclipse link  2.0.3-r7973 ) not building addCount sql correctly    

                Building select count()…   instead of select count(*)….

 

                Same code wokrs fine with 11.1.1.2.0 (  eclipse link 1.2.1-r7082)

 

   Code in the red causing issue

 

log.fine("registering PurchaseFraudJdo queries with session");

 

        // find purchases pending fraud review

        ExpressionBuilder builder = new ExpressionBuilder();

        ReadAllQuery readAllQuery = new ReadAllQuery(PurchaseFraudJdo.class);

        readAllQuery.addArgument("pGoodCode");

        readAllQuery.addJoinedAttribute("po");

 

        // look for statuses 'pending review', 'blocked' or 'follow up'

        _expression_ pendingExp =

                builder.get("reviewStatusCode").

                        equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_PENDING_REVIEW).

                        or(builder.get("reviewStatusCode").

                                equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_BLOCKED_BY_RULES)).

                        or(builder.get("reviewStatusCode").

                                equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_BLOCKED_BY_SCORE)).

                        or(builder.get("reviewStatusCode").equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_FOLLOW_UP));

 

        _expression_ exp = builder.get("po").get("pGoodCode").equal(builder.getParameter("pGoodCode"));

        exp = exp.and(builder.get("po").get("date").

                greaterThanEqual(builder.postfixSQL("SYSDATE - " + DAYS_TO_LOOK_BACK)));

        exp = exp.and(builder.getField("CREATE_DATE").

                greaterThanEqual(builder.postfixSQL("SYSDATE - " + DAYS_TO_LOOK_BACK)));

        exp = exp.and(pendingExp);

 

        readAllQuery.setSelectionCriteria(exp);

        readAllQuery.setMaxRows(MAX_FRAUD_SEARCH_RESULTS);

        readAllQuery.addDescendingOrdering("fraudScore");

        readAllQuery.refreshIdentityMapResult();

        readAllQuery.bindAllParameters();

        server.registerToplinkQuery(FIND_ALL_PENDING_BY_PGOOD, readAllQuery);

 

        // count all purchases pending fraud review

        ReportQuery rptQuery = new ReportQuery(PurchaseFraudJdo.class, pendingExp);

        rptQuery.addCount();

        rptQuery.bindAllParameters();

        rptQuery.setShouldReturnSingleValue(true);

        server.registerToplinkQuery(FIND_PENDING_COUNT, rptQuery);

 

From: Anuj Lal
Sent: Thursday, November 11, 2010 12:58 AM
To: 'eclipselink-users@xxxxxxxxxxx'
Subject: EL building select count() instead of select count(*)
Importance: High

 

                Eclipselink    11.1.1.3.0 (eclipse link  2.0.3-r7973 ) not building addCount sql correctly    

                Building select count()…   instead of select count(*)….

 

                Same code wokrs fine with 11.1.1.2.0 (  eclipse link 1.2.1-r7082)

 

 

  

 

_expression_ pendingExp =

                builder.get("reviewStatusCode").

                        equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_PENDING_REVIEW).

                        or(builder.get("reviewStatusCode").

                                equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_BLOCKED_BY_RULES)).

                        or(builder.get("reviewStatusCode").

                                equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_BLOCKED_BY_SCORE)).

                        or(builder.get("reviewStatusCode").equal(DatabaseCodes.PURCHASE_FRAUD_REVIEW_STATUS_FOLLOW_UP));

 

// count all purchases pending fraud review

        ReportQuery rptQuery = new ReportQuery(PurchaseFraudJdo.class, pendingExp);

        rptQuery.addCount();

        rptQuery.bindAllParameters();

        rptQuery.setShouldReturnSingleValue(true);

        server.registerToplinkQuery(FIND_PENDING_COUNT, rptQuery);

 

 

building a query like this

SELECT COUNT() FROM PURCHASE_ORDER t0, PURCHASE_FRAUD t1 WHERE (((((t1.REVIEW_STATUS = ?) OR (t1.REVIEW_STATUS = ?)) OR (t1.REVIEW_STATUS = ?)) OR (t1.REVIEW_STATUS = ?)) AND (t0.PURCHASE_ORDER_ID = t1.PURCHASE_ORDER_ID))

        bind => [P, R, S, F]

 

 

giving parsing _expression_

 

it building COUNT() instead of COUNT(*)