[patch] GROUP BY in PostgreSQL 8.3

[cite]

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

Code: Select all

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_entrycat ec
                      ON e.id = ec.entryid
               LEFT JOIN s9y_category c
                      ON ec.categoryid = c.categoryid
                   WHERE isdraft = 'false' AND timestamp <= 1252300500 AND 
                             ( (c.category_left  BETWEEN 9 AND 10)) GROUP BY

I've found only one place where this query might be issued in include/functions_entries.inc.php:

Code: Select all

--- functions_entries.inc.php.old	2009-09-12 12:18:34.000000000 +0200
+++ functions_entries.inc.php.new	2009-09-12 12:29:07.000000000 +0200
@@ -1534,7 +1534,7 @@ function serendipity_printArchives() {
                 . (!serendipity_db_bool($serendipity['showFutureEntries']) ? " AND timestamp <= " . serendipity_db_time() : '')
                 . (!empty($cat_sql) ? ' AND ' . $cat_sql : '')
                 . (!empty($serendipity['GET']['viewAuthor']) ? ' AND e.authorid = ' . (int)$serendipity['GET']['viewAuthor'] : '') 
-                . (!empty($cat_sql) ? " GROUP BY e.id" : '');
+                . (!empty($cat_sql) ? " GROUP BY e.id, e.timestamp" : '');
     $entries =& serendipity_db_query($q, false, 'assoc');
 
     $group = array();
@@ -1644,4 +1644,4 @@ function serendipity_getCategoryRoot($id
                                   AND n.categoryid = " . (int)$id . "
                              ORDER BY n.category_left DESC, p.category_left ASC");
     return $r;
-}
\ No newline at end of file
+}

Until now, nothing breaked and the error message vanished.

[garvinhicking]

Hi!

Thanks a lot, indeed for postgresql this is neccessary. I've committed your patch!

Regards,
Garvin