Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jhartjhart 

bug: StandardSetController or QueryLocator ignores SOQL 'where' clause

The StandardSetController (or Database.QueryLocator) ignores the SOQL 'where' clause.

Because QueryLocator can only be used with StandardSetController or in managed sharing recalc, I can't test the it separately to see exactly where the bug lies.

This (simplified) example shows the bug in the StandardSetController context.

I have a custom visualforce page that lists users:

Code:
<apex:page controller="CtlBugPgr" title="Bug">
<apex:form>

<apex:pageBlock title="Users" id="UserList">

<apex:pageBlockTable value="{!data}" var="each">
  <apex:column headerValue="Active?">{!each.IsActive}</apex:column>
  <apex:column headerValue="User">{!each.Name}</apex:column>
  <apex:column headerValue="UserType">{!each.UserType}</apex:column>
</apex:pageBlockTable>

</apex:pageBlock>

</apex:form>
</apex:page>

 The controller for this page uses StandardSetController:

Code:
public class CtlBugPgr {

  //-------------------------------------------------------------------------------
  // User List
  //-------------------------------------------------------------------------------
  public User[] data { get { return (List<User>)pgr.getRecords(); } set; }

  public ApexPages.StandardSetController pgr {
    get {
      if (pgr == null) pgr = initPager();
      return pgr;
      }
    set;
    }
  
  //-------------------------------------------------------------------------------
  // privates
  //-------------------------------------------------------------------------------
  private ApexPages.StandardSetController initPager() {
    ApexPages.StandardSetController ret = new ApexPages.StandardSetController(Database.getQueryLocator(
      [select Name, UserType, IsActive from User where IsActive = true and UserType = 'Standard']
      ));
    ret.setPageSize(10); // UPDATED: necessary to reproduce bug!
    return ret;
    }
  }

 
Note the two conditions in the where clause of the SOQL.

If I run the query directly via "executeAnonymous" I get what I expect:

Code:
> System.debug([select Name, IsActive, UserType from User where IsActive = true and UserType = 'Standard']);

20081125225927.981:AnonymousBlock.i: line 1, column 1: (
        User:{UserType=Standard, IsActive=true, Name=John Hart, Id=...})

 
However, in the visualforce page, all Users are returned:






Message Edited by jhart on 11-26-2008 03:25 PM
jhartjhart
I have opened case 02268969 to track this issue.
dchasmandchasman
Looking into this now - I believe the issue might be specific to User (this object type has some very interesting peculiarities in general).
Sami CohenSami Cohen

Hi Doug et al.,

We encountered as well the same issues with the pagination and as a result have developed our own pager in visualforce.

Any idea when you believe this is expected to be resolved?

 

Sami Cohen

Architect & Technology Leader

BA-Link

 
jhartjhart
Either this bug has already been fixed, or I am going completely insane, because I just tried to reproduce it and failed.

I spent about 2 hours yesterday verifying this behavior and writing up the initial post in this thread, cutting-and-pasting directly from my page & controller into the discussion thread.

I don't think page (or server-side) caching could be the culprit, as I was continually tweaking the page & controller to make the test case more obvious.  For example, when I first noticed the bug the page had an "Email" column and didn't even have (nor select for) the "UserType" and "Active" columns; I only added those later to make the test case as clear as possible.

So, there's no way I could have even gotten the page to display as it did unless it was referencing the correct (& most recent) controller, which always had the filters in place.  I was cutting-and-pasting out of the controller into my command line for the executeAnonymous call...

Anyway, I feel like a complete wonderclown.  I wish I hadn't removed the page & controller from my build last night, so at least I would know the exact same code was still running.

My apologies for any time wasted on this.  I'm certainly glad it's not reproducing any more.

ugh.  I hate posting bugs that don't reproduce.
jhartjhart
OK, it is reproducing again.  I think perhaps the error happens only when a certain number of cursors have been opened, and the backend needs to start closing old ones to open new ones?

Glad I'm not losing my mind.
jhartjhart
OK.  If you call "setPageSize()" on the StandardSetController, that causes the "where" clause to be ignored.

Here's a video of the bug happening (& not happening) dependent on that call.

The video has a couple extra page refreshes just to show that what's seen is in fact current code.
mtbclimbermtbclimber
Thanks John.  This information has helped further isolate the issue. More to come.
dchasmandchasman
There are a couple of situations where the standard controller is not reapplying the where clause:
  • setPageSize() clears the current result set and forces a requery and the requery does not include the original where clause extracted from the QueryLocator
  • if the api cursor times out or is flushed because of too many active cursors the resulting auto requery does not include the original where clause
The fix for both is relatively simple but we are in lock down w.r.t. to changes to Winter '09 at this time so I do not anticipate being able to get the fix deployed to our servers until Spring '09 releases.

I do not have a workaround for either situation at this time. Please be aware that neither of these situations occurs when using the standard set controller directly from a page or with filters.
jhartjhart
Salesforce informs me that this bug will be fixed in the next release (Spring '09).

happy hols, all.
SaaspertSaaspert

are the issues mentioned above fixed ? I am using standardsetcontroller to build an enhancedlist view like UI using solution object returned via SOSL search. i.e. I am supplying data set to standardsetcontroller and none of the pagination is working for me as getrecords() gives out all the records , setpage size has no impact on how many records returned via getrecords()

 

Please let me know

 

Thanks

SuzanyuSuzanyu

I dont think they fixed that issue in summer 9. 'Cause I still have the same problem with QueryLocator when I use Where clause and setPageSize.

mtbclimbermtbclimber

Please post your page or a small representative example highlighting the issue you are seeing.

 

Thanks,

SaaspertSaaspert

Andrew,

 

Here is the code

 

// controller

 

public class ct7 {


    /** code for list view controller for cases **/
    private List<case> cases;

    public ApexPages.StandardSetController dynQStdSetCon {
        get {
             if(dynQStdSetCon == null) {
               
              
                String dynquery = 'Select c.CaseNumber, c.Contact.Name, c.Subject, c.status , c.priority, c.createddate From Case c'
                                    + ' where c.status != \'Closed\''
                                    + ' order by c.createddate desc';
 
                cases = (List<Case>) Database.query(dynQuery);
               
                dynQStdSetCon = new ApexPages.StandardSetController(cases);

                // stdSetCon.setFilterId(defaultFilterId);
                dynQStdSetCon.setPageSize(25);
               
            }
            return dynQStdSetCon;
        }
       
        set;
        }

    public List<Case> getCasesList () {
            return (List<Case>) dynQStdSetCon.getRecords();
 
    }
}

 

// page

<apex:page controller="ct7">
<apex:outputpanel id="caselistview" layout="block">

        <apex:dataTable value="{!CasesList}" var="c">
            <apex:column >
                <apex:facet name="header">Case Number </apex:facet>
                {!c.casenumber}
                </apex:column>
            <apex:column >
                <apex:facet name="header">Contact Name</apex:facet>
                <apex:outputText value="{!c.contact.name}"/>
            </apex:column>
            <apex:column >
                <apex:facet name="header"> Subject  </apex:facet>
                {!c.subject}
            </apex:column>
            <apex:column >
                <apex:facet name="header"> Status </apex:facet>
                <apex:outputText value="{!c.status}"/>
            </apex:column>            
            <apex:column >
                <apex:facet name="header"> Priority </apex:facet>
                <apex:outputText value="{!c.priority}"/>
            </apex:column>            
            <apex:column value="{!c.createddate}">
                <apex:facet name="header">Date / Time Opened</apex:facet>
           </apex:column>
    </apex:dataTable>

</apex:outputpanel>
</apex:page>
 

 
 
Thanks 
Message Edited by mtbclimber on  03-03-2009 02:17 PM


Supper_KentSupper_Kent
You should use the Database.getQueryLocator but not just database.query.


vbcrlfuservbcrlfuser
For what it's worth having a simlar issue brought on with SUMMER '09 ignoring the WHERE clause over night and it involves the User table and Apex deriving from ApexPages.StandardController.
 
This query runs fine for one day limiting the query to User by the WHERE clause...
 
for (User u : [SELECT Id, Name FROM User WHERE Id IN ( SELECT UserId FROM AccountTeamMember WHERE AccountId = : c.AccountId) ORDER BY Name ASC] ) {
              options.Add(new selectOption(u.Id, u.Name));
            }
 
.. then over night it is as if someone pulled the plug on the WHERE statement all together and all of the Users get returned. Very strange. Anyone know anything about on this forum from SF that knows about any caching, precompiling of SOQL, or the SOQL optimizer, that would explain this drop out? 


Scott.MScott.M
I am running into similar problems in summer 09. It now appears to be an issue with using the IN clauses in the query locator. Also if I have two conditions on the same field things go wonkey. But only if Standardsetcontroller.setPageSize is called. 
 
 
Cheers,
Scott 
 


james2000james2000
I'm running into this exact problem with Summer '09 as well. Will this be fixed in Winter '10?


TLFTLF
I'm still seeing this issue in Winter '10. For me, it happens when there are NO objects that meet my WHERE clause criteria. In that case, it seems that DatabaseQuery.getQueryLocator returns ALL records, as if the WHERE clause didn't exist. When I have objects that meet the WHERE clause criteria, it seems that DatabaseQuery.getQueryLocator returns the expected, filtered record set.


TLFTLF
I spent a little more time trying to research this and come up with a repro scenario. It only seems to occur when you explicitly call the setPageSize method on the !StandardSetController object that is instantiated via a Database.getQueryLocator call in which the specified query returns no rows. If you don't call the setPageSize method, the SSC correctly returns no records. So, I worked around the issue by calling SSC.getResultSize(). If the returned result size is 0, I don't call the setPageSize method.


Supper_KentSupper_Kent
I also used this function in our environments, and it works well, please check my codes below, and i adjusted them accordingly. 
public class tenPageSizeExt {

  public user[] data { get { return (List<user>)pgr.getRecords(); } set; }

  public ApexPages.StandardSetController pgr {
    get {
      if (pgr == null) pgr = initPager();
      return pgr;
      }
    set;
    }
  
  //-------------------------------------------------------------------------------
  // paging actions
  //-------------------------------------------------------------------------------
  public PageReference nextPage() { pgr.next(); return null;     }
  public PageReference prevPage() { pgr.previous(); return null; }
  
  //-------------------------------------------------------------------------------
  // privates
  //-------------------------------------------------------------------------------
  private ApexPages.StandardSetController initPager() {
    ApexPages.StandardSetController ret = new ApexPages.StandardSetController(Database.getQueryLocator(
      [select name, LastModifiedDate from user where IsActive = true and UserType = 'Standard']
      ));
    ret.setPageSize(10);
    return ret;
    }
  }
 
Below is the Visualforce page
<apex:page controller="tenPageSizeExt">
<apex:form > 
<apex:pageBlock title="Users" id="Con"> 
<apex:pageBlockTable value="{!data}" var="con" >
<apex:column value="{!con.name}" />
</apex:pageBlockTable>

<!--------------------Definition for Previous and Next button------------>
<apex:outputPanel styleClass="prevNext" layout="block" rendered="{!OR(pgr.hasNext,pgr.hasPrevious)}">
  <apex:outputPanel rendered="{!NOT(pgr.hasPrevious)}" styleClass="greyedLink">&lt;Previous Page</apex:outputPanel>
  <apex:commandLink rendered="{!pgr.hasPrevious}" action="{!prevPage}" rerender="Con">&lt;Previous Page</apex:commandLink>
  <span> | </span>
  <apex:outputPanel rendered="{!NOT(pgr.hasNext)}" styleClass="greyedLink">Next Page&gt;</apex:outputPanel>
  <apex:commandLink rendered="{!pgr.hasNext}" action="{!nextPage}" rerender="Con">Next Page&gt;</apex:commandLink>
</apex:outputPanel>
</apex:pageBlock> 
</apex:form>

</apex:page>
 


ShivRShivR
That's great work TLF. I implemented the same fix and works like a charm. SFDC should fix this.


BruceYueBruceYue
it's fixed now?