Did you test #4485? Because it's not merged yet.
Also please test with current staging or a nightly build. PR #4330 isn't in Joomla 3.3.6 yet.

Yes, tested booth of them and have them acutal in my files active too. Espacially #4485, as #4330 is extended by #5009 becaus of an issue with the count(*) in postgresql database query.

can you add testing instructions how to reproduce the issue?

The unit tests don't like your changes. This it doesn't look like a correct solution to me.

How to test it:

Joomla 3.3.6 with Postgresql Database 9.0

Then make a category with, let's say 10 content items and make a menu link with blog layout, limited to 4 entries per page with shorttext, no leading article (0), no links (0).

If you let Joomla stop the line before _getListCount is called, you get following query

SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = '1970-01-01 00:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = '1970-01-01 00:00:00' THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.language, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published FROM #__content AS a LEFT JOIN #__content_frontpage AS fp ON fp.content_id = a.id LEFT JOIN #__categories AS c ON c.id = a.catid LEFT JOIN #__users AS ua ON ua.id = a.created_by LEFT JOIN #__users AS uam ON uam.id = a.modified_by LEFT JOIN #__categories as parent ON parent.id = c.parent_id LEFT JOIN #__content_rating AS v ON a.id = v.content_id LEFT OUTER JOIN (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id WHERE a.access IN (1,1,5) AND c.access IN (1,1,5) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.catid = 13 OR a.catid IN ( SELECT sub.id FROM #__categories as sub INNER JOIN #__categories as this ON sub.lft > this.lft AND sub.rgt < this.rgt WHERE this.id = 13)) AND (a.publish_up = '1970-01-01 00:00:00' OR a.publish_up <= '2014-11-07 15:01:44') AND (a.publish_down = '1970-01-01 00:00:00' OR a.publish_down >= '2014-11-07 15:01:44') ORDER BY c.lft, a.ordering, a.created LIMIT 4 OFFSET 4

As we know, in getListcount the limit and potential orders are stripped off, as well as the select is substituted with Count(*). See #4330. But the Offset still stays in the query, and that leads to the faulty result in connection with the postgres database then returning no result at all. (as described in #5010 where in combination with this change here #5010 gets obsolete).

As we know, in getListcount the limit and potential orders are stripped off, as well as the select is substituted with Count(*). See #4330. But the Offset still stays in the query, and that leads to the faulty result in connection with the postgres database then returning no result at all.

Ah, that's funny. Because JDatabaseQuery->clear('limit') will actually clear both the limit and offset. See https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query.php#L649
If you use clear('offset') it will actually clear the whole query since 'offset' isn't a valid value.

JDatabaseQueryPostgresql->clear('limit') on the other hand doesn't clear the offset. See https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query/postgresql.php#L202
It would support the 'offset' value as a separate clear argument. However as we saw above, using it would break on all other databases.
So the proper fix is probably to also clear the offset together with the limit and deprecate the value for offset.

What do you think?

Sounds like a propert solution to me. Or if you want to keep the offset value, split the two up for all databases. As whatever is the easier way with having fewer implications on the rest of joomla.

See #5014

I opted to add the offset as valid value to the other databases since it may be a useful feature anyway.
And in PostgreSQL it's valid to have an OFFSET without a LIMIT.

Can you test the proposed code if it fixes your issue?

Testet, and it works. Thank you.