Page 1 of 1
S9Y 1.3.1: Missing GROUP BY statement (pgsql)?
Posted: Wed Sep 17, 2008 6:53 pm
by cite
The reported query is:
Code: Select all
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_access.property = 'ep_access') WHERE e.timestamp >= 1220220000 AND e.timestamp <= 1222811999 AND isdraft = 'false' AND e.timestamp <= 1221669900 AND (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND (ep_no_frontpage.property IS NULL OR ep_no_frontpage.value != 'true') ORDER BY orderkey ASC, timestamp DESC;
The log entry in postgresql-8.3-main.log:
Code: Select all
2008-09-17 18:44:27 CEST ERROR: column "e.timestamp" must appear in the GROUP BY clause or be used in an aggregate function
2008-09-17 18:44:27 CEST STATEMENT: 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_access.property = 'ep_access')
WHERE e.timestamp >= 1214863200 AND e.timestamp <= 1217541599 AND isdraft = 'false' AND e.timestamp <= 1221669900 AND (ep_access
.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND (ep_no_frontpage.prop
erty IS NULL OR ep_no_frontpage.value != 'true')
ORDER BY orderkey ASC, timestamp DESC
Where do I find that query in the source to add the missing "GROUP BY" statement?
Cheers
Stefan
Re: S9Y 1.3.1: Missing GROUP BY statement (pgsql)?
Posted: Thu Sep 18, 2008 11:33 am
by garvinhicking
Hi!
Phew, this query could happen on a lot of placeS: Plugins, archive views etc.
Do you know a specific URL on how to provoke this pgsql error?
Regards,
Garvin
Posted: Thu Sep 18, 2008 11:53 am
by cite
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:
Code: Select all
@serendipity_calendar_plugin
@serendipity_quicksearch_plugin
@serendipity_archives_plugin
@serendipity_categories_plugin
serendipity_event_trackback
serendipity_event_spartacus
serendipity_event_s9ymarkup
serendipity_event_emoticate
serendipity_event_nl2br
serendipity_plugin_flickrbadge
serendipity_event_spamblock
serendipity_event_staticpage:
serendipity_event_weblogping
serendipity_event_google_sitemap
serendipity_event_entryproperties
serendipity_plugin_staticpage
serendipity_event_head_nugget
serendipity_event_htmlvalidator
serendipity_event_autosave
serendipity_event_typesetbuttons
serendipity_event_xhtmlcleanup
serendipity_event_bbcode
serendipity_event_emoticonchooser
serendipity_plugin_google_last_query
Cheers
Stefan
Posted: Thu Sep 18, 2008 1:47 pm
by garvinhicking
Hi!
I believe the reason for that query comes frmo include/plugin_internal.inc.php. You should find this query:
Code: Select all
$ec = serendipity_fetchEntries(
array($current_ts, $end_ts),
false,
'',
false,
false,
'timestamp DESC',
'',
false,
true,
'count(e.id) AS orderkey',
'',
'single',
false, $category_set // the joins used
);
This query is only sent when you enable the option to count entries.
If you change "count(e.id) AS ordey" to "count(distinct e.id) AS orderkey", does it work then?
HTH,
Garvin
Posted: Thu Sep 18, 2008 4:04 pm
by cite
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
Posted: Thu Sep 18, 2008 4:21 pm
by garvinhicking
Hi!
Hm, so if the Query stays the same this would mean that this is not the place it comes from. I'm stumped, because a fulltextsearch on the code yielded no other occurences of a count(e.id) anywhere
Regards,
Garvin
Posted: Thu Sep 18, 2008 4:25 pm
by cite
Sorry, I didn't make myself clear: The query is changed, but it still complains about improper usage of e.timestamp.
Code: Select all
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(distinct 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_access.property = 'ep_access')
WHERE e.timestamp >= 1214863200 AND e.timestamp <= 1217541599 AND isdraft = 'false' AND e.timestamp <= 1221746700 AND (ep_access.property IS NULL OR ep_access.value = 'public') AND (ep_no_frontp
age.property IS NULL OR ep_no_frontpage.value != 'true')
ORDER BY orderkey ASC, timestamp DESC
Posted: Wed Sep 24, 2008 11:36 am
by cite
Sorry for pushing this one - is there a solution available?
Cheers
Stefan
Posted: Wed Sep 24, 2008 12:04 pm
by garvinhicking
Hi!
And if you change this:
to
Code: Select all
'count(DISTINCT e.id) AS orderkey, e.timestamp',
?
Regards,
Garvin
Posted: Wed Sep 24, 2008 1:51 pm
by cite
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)).
I don't understand 100% what that query is supposed to return - if you could tell me what the expected result is, I could perhaps help you to find a fix.
Cheers
Stefan
Posted: Wed Sep 24, 2008 3:06 pm
by garvinhicking
Hi!
And how about count(dISTINCT e.id), max(e.timestamp) AS timestamp?
The idea of the query (and how it works on mysql) is that it returns unique entry IDs (because of the joins there might be multiple of the same entries) and orders the list of entries for a given timestamp by their respective unix timestamp...
HTH,
Garvin
Posted: Wed Sep 24, 2008 5:22 pm
by cite
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_access.entryid AND ep_access.property = 'ep_access') WHERE e.timestamp >= 1220220000 AND e.timestamp <= 1222811999 AND isdraft = 'false' AND e.timestamp <= 1221669900 AND (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND (ep_no_frontpage.property IS NULL OR ep_no_frontpage.value != 'true') ORDER BY e.id ASC, timestamp DESC;
ERROR: column "e.id" must appear in the GROUP BY clause or be used in an aggregate function
I'll try and figure this out on my own until Friday - and while I'm at it, I'll double check my results against our Oracle server. Just to be on the safe side, SQL-wise
Cheers
Stefan
Posted: Wed Sep 24, 2008 5:29 pm
by garvinhicking
Hi!
Better check it against SQLite or MySQL. Oracle has an alltogether different syntax that s9y doesn't support at all
Thanks,
Garvin
Posted: Thu Sep 25, 2008 5:11 am
by cite
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.