I receive the following error on my Site Map page after upgrading to the 5.x-1.2 version. I am using Drupal 5.5 with PostgreSQL.

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "u.uid" must appear in the GROUP BY clause or be used in an aggregate function in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT u.uid, u.name, count(u.uid) AS numitems FROM node n INNER JOIN users u ON u.uid = n.uid WHERE n.type = 'blog' and n.status = 1 GROUP BY n.uid ORDER BY numitems DESC, u.name LIMIT 10 OFFSET 0 in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.

Comments

frjo’s picture

frjo commented
Assigned: Unassigned » frjo

This we need to fix. I don't use PostgreSQL myself so I hope you have time to help me with some testing.

One line 332 in site_map.module you find this, "GROUP BY n.uid". What happens if you change that to

      GROUP BY u.uid

If we lucky it's that simple.

WISEOZ’s picture

WISEOZ commented

I'm certainly willing to help test!

I made that change and receive the following error now:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "u.name" must appear in the GROUP BY clause or be used in an aggregate function in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT u.uid, u.name, count(u.uid) AS numitems FROM node n INNER JOIN users u ON u.uid = n.uid WHERE n.type = 'blog' and n.status = 1 GROUP BY u.uid ORDER BY numitems DESC, u.name LIMIT 10 OFFSET 0 in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 37 AND na.realm = 'og_subscriber'))) AND ( n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 37 AND na.realm = 'og_subscriber'))) AND ( n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.

Let me know if you want me to keep that change or try something else.

frjo’s picture

frjo commented
Version: 5.x-1.2 » 5.x-1.x-dev

Please try:

      GROUP BY u.uid, u.name
WISEOZ’s picture

WISEOZ commented

The errors are get lesser!

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
frjo’s picture

frjo commented

Less errors is good :-).

Try this (different line, just below the "GROUP BY")

      ORDER BY numitems DESC, u.uid, u.name";
WISEOZ’s picture

WISEOZ commented

Looks about the same ...

Keep in mind that I've been adding these changes and not taking any of them away. I assumed that's what you intended. If not, let me know and I can try them one by one.

* warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT (n.nid), if((w.weight IS NULL), 0, w.weight) as weight FROM node n LEFT JOIN faq_weights w ON w.nid = n.nid WHERE n.type='faq' AND n.status = 1 AND (w.tid = 0 OR w.tid IS NULL) ORDER BY n.nid, weight, n.sticky DESC, n.created DESC in /usr/local/apache2/htdocs/drupal/includes/database.pgsql.inc on line 144.
frjo’s picture

frjo commented
Status: Active » Closed (fixed)