Home » Download » Modules » Site map » Issues

Group By Error

WISEOZ - February 9, 2008 - 20:59
Project:Site map
Version:5.x-1.x-dev
Component:Code
Category:bug report
Priority:normal
Assigned:frjo
Status:active
Description

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.

» Login or register to post comments

#1

frjo - February 10, 2008 - 10:36
Assigned to:Anonymous» 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.

Login or register to post comments

#2

WISEOZ - February 10, 2008 - 21:15

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.

Login or register to post comments

#3

frjo - February 13, 2008 - 07:00
Version:5.x-1.2» 5.x-1.x-dev

Please try:

      GROUP BY u.uid, u.name

Login or register to post comments

#4

WISEOZ - February 13, 2008 - 19:53

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.

Login or register to post comments

#5

frjo - February 13, 2008 - 20:03

Less errors is good :-).

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

      ORDER BY numitems DESC, u.uid, u.name";

Login or register to post comments

#6

WISEOZ - February 14, 2008 - 01:16

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.

Login or register to post comments
 
 

Drupal is a registered trademark of Dries Buytaert.