I was getting random errors in my postgresql.log, like e.g.:

2009-09-07 07:11:56 CEST ERROR: column "e.timestamp" must appear in the GROUP BY clause or be used in an aggregate function
2009-09-07 07:11:56 CEST STATEMENT: SELECT DISTINCT e.id, e.timestamp
FROM s9y_entries e

LEFT JOIN s9y ...

While I don't have a solution yet I can report that pgsql 8.2 does not report an error for the query I mentioned. Seems only 8.3 is affected.

s9y=# SELECT count(distinct e.id) AS orderkey, max(e.timestamp) AS timestamp FROM s9y_entries AS e LEFT OUTER JOIN s9y_entryproperties ep_no_frontpage ON (e.id = ep_no_frontpage.entryid AND ep_no_frontpage.property = 'ep_no_frontpage') LEFT OUTER JOIN s9y_entryproperties ep_access ON (e.id = ep ...

Hi there,

and thanks for your quick response.

It doesn't work with this fix, either.

My knowledge of SQL is very limited, but I think the problem arises because of the two columns used as keys in the ORDER BY statement, only one (orderkey) is derived by means of an aggregate funkction (count(e.id ...

Sorry for pushing this one - is there a solution available?


Cheers
Stefan

Sorry, I didn't make myself clear: The query is changed, but it still complains about improper usage of e.timestamp.


2008-09-18 16:00:28 CEST ERROR: column "e.timestamp" must appear in the GROUP BY clause or be used in an aggregate function
2008-09-18 16:00:28 CEST STATEMENT: SELECT count ...

Yes, the code is in include/plugin_internal_inc.php, but the fix you suggested doesn't change the error message I get in the pgsql logs.


Cheers
Stefan

Yes - GET /index.php it is, i.e. the error is raised about 21 times every time the blog is accessed.

Plugin list is as follows:

@serendipity_calendar_plugin
@serendipity_quicksearch_plugin
@serendipity_archives_plugin
@serendipity_categories_plugin
serendipity_event_trackback
serendipity ...

The reported query is:

SELECT count(e.id) AS orderkey FROM s9y_entries AS e LEFT OUTER JOIN s9y_entryproperties ep_no_frontpage ON (e.id = ep_no_frontpage.entryid AND ep_no_frontpage.property = 'ep_no_frontpage') LEFT OUTER JOIN s9y_entryproperties ep_access ON (e.id = ep_access.entryid AND ep ...