Content: State (= Pending) views filter does not work for unpublished nodes with multiple revisions.

To Reproduce:
Create a content type with the following default publishing options:
-Not Published
-New revision in draft, pending moderation
-Create a new revision every time content_type is updated, even when saving content in draft/pending moderation

Create a node of your new type, then edit it. Check the revisions page. You should have two revisions, the older one with a state of "archived" and the newer one "in draft/pending publication"

Create a view
-remove the default Published filter
-add a filter for State (= Pending)

Your node won't show up in the view! This is because the filter adds the following SQL to your query:

(
  node.status=1 
  AND node.vid<
  (
    SELECT MAX(vid) 
    FROM {node_revision} 
    WHERE nid=node.nid
  )
) 
OR 
(
  node.status=0 
  AND 
  (
    SELECT COUNT(vid) 
    FROM {node_revision} 
    WHERE nid=node.nid
  )
  =1
)

Our node is falling into the second half of the OR, because its status = 0, it's unpublished, but then the Count query finds two revisions, which it apparently thinks means the state of the node isn't pending. It totally is, though.

I think, but haven't tested, that a more appropriate condition after the OR would be:

(
  node.status=0 
  AND node.vid =
  (
    SELECT MAX(vid) 
    FROM {node_revision} 
    WHERE nid=node.nid
  )
)