On Drupal 6 and PostgreSQL with book and book_manager modules enabled I get these errors when viewing a book content:

* warning: pg_query() [function.pg-query]: Query failed: ERROR: column "n.uid" must appear in the GROUP BY clause or be used in an aggregate function in MY_SITE/includes/database.pgsql.inc on line 139.
* user warning: query: _book_manager_get_book_owner SELECT n.uid FROM book b LEFT JOIN node n ON n.nid = b.bid WHERE n.nid = 328 GROUP BY n.nid in MY_SITE/sites/all/modules/book_manager/book_manager.module on line 184.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: column "n.uid" must appear in the GROUP BY clause or be used in an aggregate function in MY_SITE/includes/database.pgsql.inc on line 139.
* user warning: query: _book_manager_get_book_owner SELECT n.uid FROM book b LEFT JOIN node n ON n.nid = b.bid WHERE n.nid = 328 GROUP BY n.nid in MY_SITE/sites/all/modules/book_manager/book_manager.module on line 184.

CommentFileSizeAuthor
#5 book_manager-546630.patch938 bytesjgraham
#4 book_manager-pgsql-546630-4.patch628 bytesVector-

Comments

marcp’s picture

marcp commented

I don't have pgsql to test against, but I suspect you'll need to change the line that says:

  if ($result = db_query("SELECT n.uid FROM {book} b LEFT JOIN {node} n ON n.nid = b.bid WHERE n.nid = %d GROUP BY n.nid", $bid)) {

to

  if ($result = db_query("SELECT n.uid, n.nid FROM {book} b LEFT JOIN {node} n ON n.nid = b.bid WHERE n.nid = %d GROUP BY n.nid", $bid)) {

If someone on pgsql could confirm that this fixes the problem I'll commit the fix. Otherwise this will probably sit around unfixed for a while [longer].

marcp’s picture

marcp commented
Version: 6.x-1.3 » 6.x-1.x-dev
Status: Active » Fixed

I committed the fix in #1 above. It doesn't break mysql and it will, hopefully, fix the problem on pgsql. Re-open this if it's still broken.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Vector-’s picture

Vector- commented
Version: 6.x-1.x-dev » 6.x-2.x-dev
Status: Closed (fixed) » Needs review
StatusFileSize
new book_manager-pgsql-546630-4.patch628 bytes

This issue is still present - the theorized fix above does not work.

I think the proper fix looks more like this:

if ($result = db_query("SELECT n.uid FROM {book} b LEFT JOIN {node} n ON n.nid = b.bid WHERE n.nid = %d GROUP BY n.nid, n.uid", $bid)) {

I've done some basic testing (in a pgsql environment) and haven't found any problems with this query...
I've got this running on a live site now, so if any users manage to find holes in it, I'll post something new up here.

So long as this doesn't break anything in MySQL (and I don't think it will?)...
Here's the patch I used against v2.1 - if it doesn't break MySQL, I think this is what you want.

jgraham’s picture

jgraham commented
StatusFileSize
new book_manager-546630.patch938 bytes

Please review the attached patch which cleans up the query and should avoid the 'group by' issue altogether.

This applies clean for MySQL, I do not have a PostgreSQL environment to test on.

jgraham’s picture

jgraham commented
Status: Needs review » Fixed

Commited: http://drupal.org/cvs?commit=458764

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.