S9Y 1.3.1: Missing GROUP BY statement (pgsql)?

[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

[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

[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

[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

[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

[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

[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

[cite]

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


Cheers
Stefan

[garvinhicking]

Hi!

And if you change this:

Code: Select all

'count(e.id) AS orderkey', 

to

Code: Select all

'count(DISTINCT e.id) AS orderkey, e.timestamp', 

?

Regards,
Garvin

[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

[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

[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

[garvinhicking]

Hi!

Better check it against SQLite or MySQL. Oracle has an alltogether different syntax that s9y doesn't support at all

Thanks,
Garvin

[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.