Using the archive module in drupal-4.7.5 with posgresql as the database throws the following errors:

pg_query() [function.pg-query]: Query failed: ERROR: column "n.created" must appear in the GROUP BY clause or be used in an aggregate function in /home/magnus/apache-php/drupal-4.7.5/includes/database.pgsql.inc on line 94.

query: SELECT count(n.nid) FROM node n WHERE n.status = 1 AND n.created > 1168250400 AND n.created < 1168336800 ORDER BY n.created in /home/magnus/apache-php/drupal-4.7.5/includes/database.pgsql.inc on line 113.

This can be fixed by changing line 232 in archive.module to:
$countsql = db_rewrite_sql('SELECT count(n.nid) FROM {node} n WHERE n.status = 1 AND n.created > %d AND n.created < %d');

I.e. delete the "ORDER BY n.created" clause, which makes no sense for "select count()"

Comments

kecsi’s picture

kecsi commented

Same here with archive-5.x-1.0.tar.gz aka drupal5 and postgressql backend.
Got several sql error messages when I turned on this module and went /archive.

kecsi’s picture

kecsi commented
Version: 4.7.x-1.0 » master

Here are the errror messages from HEAD(2007-Jan-22) version:
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "min_date" at character 21 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT MIN(created) min_date, MAX(created) max_date FROM node n WHERE n.status = 1 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1167606000 AND n.created < 1170284400 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1170284400 AND n.created < 1172703600 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1172703600 AND n.created < 1175382000 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1175382000 AND n.created < 1177974000 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1177974000 AND n.created < 1180652400 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1180652400 AND n.created < 1183244400 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1183244400 AND n.created < 1185922800 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1185922800 AND n.created < 1188601200 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1188601200 AND n.created < 1191193200 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1191193200 AND n.created < 1193871600 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1193871600 AND n.created < 1196463600 in /includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "count" at character 17 in /includes/database.pgsql.inc on line 125.
* user warning: query: SELECT COUNT(*) count FROM node n WHERE n.status = 1 AND n.created >= 1196463600 AND n.created < 1199142000 in /includes/database.pgsql.inc on line 144.

yagel’s picture

yagel commented

the same
just remove ORDER

--- archive.module.orig Thu Nov 16 01:00:29 2006
+++ archive.module Thu Feb 15 01:37:23 2007
@@ -229,7 +229,7 @@
if ($year && $month && $day) {
// Fetch nodes for the selected date, if one was specified.
$sql = db_rewrite_sql('SELECT n.nid, n.created FROM {node} n WHERE n.status = 1 AND n.created > %d AND n.created < %d ORDER BY n.created');
- $countsql = db_rewrite_sql('SELECT count(n.nid) FROM {node} n WHERE n.status = 1 AND n.created > %d AND n.created < %d ORDER BY n.created');
+ $countsql = db_rewrite_sql('SELECT count(n.nid) FROM {node} n WHERE n.status = 1 AND n.created > %d AND n.created < %d ');
$result = pager_query($sql, variable_get('default_nodes_main', 10), 0, $countsql, $date, $date_end);
if (db_num_rows($result) > 0) {
while ($nid = db_fetch_object($result)) {

kecsi’s picture

kecsi commented

and use AS.
kecsi@rivendel:/var/www/XXXX/modules/archive$ diff archive.inc archive.inc-orig
123c123
< $result = db_query(db_rewrite_sql('SELECT MIN(created) AS min_date, MAX(created) AS max_date FROM {node} n WHERE n.status = 1'));
---
> $result = db_query(db_rewrite_sql('SELECT MIN(created) min_date, MAX(created) max_date FROM {node} n WHERE n.status = 1'));
142c142
< $result = db_query(db_rewrite_sql('SELECT COUNT(*) AS count FROM {node} n WHERE n.status = 1 AND n.created >= %d AND n.created < %d'), $start, $end);
---
> $result = db_query(db_rewrite_sql('SELECT COUNT(*) count FROM {node} n WHERE n.status = 1 AND n.created >= %d AND n.created < %d'), $start, $end);

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Status: Active » Fixed

OK, committed #4 as a patch in Drupal 5, but the count() vs. "order by" issue is nonexistent in Drupal 5.
I also backported the fix to Drupal 4.7, and it did not have the count() issue either as far as I see.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)