Two SQL Statements in uc_attribute/uc_attribute.admin.inc have a wrong GROUP BY clause: The columns one wants to be listed in the "SELECT ... FROM" List have to be
written also in the "GROUP BY" clause (if not using aggregate functions). This
is not specific to a certain postgresql server version, but I give information about it anyway:

Platform: FreeBSD Unix, PostgreSQL 8.2.3 on amd64-portbld-freebsd6.2,
drupal 6.10, ubercart-6.x-2.0-rc2

Here is the difference of the distributed ubercart module and my fix, which works fine.
In short: add "a.label" into the GROUP BY clause:

diff dist/ubercart/uc_attribute/uc_attribute.admin.inc ./ubercart/uc_attribute/uc_attribute.admin.inc
24c24,26
< $result = pager_query("SELECT a.aid, a.name, a.label, a.required, a.ordering, a.display, COUNT(ao.oid) AS options FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid, a.name, a.ordering, a.required, a.display". tablesort_sql($header), 30, 0, "SELECT COUNT(aid) FROM {uc_attributes}");
---
> // np@bsn.com changed 1 line:
> $result = pager_query("SELECT a.aid, a.name, a.label, a.required, a.ordering, a.display, COUNT(ao.oid) AS options FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid
> GROUP BY a.aid, a.name, a.label, a.ordering, a.required, a.display". tablesort_sql($header), 30, 0, "SELECT COUNT(aid) FROM {uc_attributes}");
526c528,529
< $result = db_query("SELECT a.aid, a.name, a.label FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid, a.name ORDER BY a.name");
---
> // np@bsn.com changed 1 line:
> $result = db_query("SELECT a.aid, a.name, a.label FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid, a.name, a.label ORDER BY a.name");

CommentFileSizeAuthor
#2 group_by_label.patch1.86 KBNorbert Poellmann

Comments

Island Usurper’s picture

Status: Active » Fixed

Thanks for this. I tend to forget all of the little idiosyncrasies that Postgres needs when old queries get tweaked.

I don't know why, but unified diff format is a lot easier for me to read, and it's the convention that other Drupal developers use as well. Attaching the diff as a file is also convenient since it saves space on the issue page. Example:

diff -up dist/ubercart/uc_attribute/uc_attribute.admin.inc ./ubercart/uc_attribute/uc_attribute.admin.inc > group_by_label.patch

Changes are committed.

Norbert Poellmann’s picture

StatusFileSize
new1.86 KB

yes ... I missed to create a unified diff. Now it's attached.
BTW: I prepare drupal 6.11 for Postgres - At least in 6.10 there was some code, which
didn't do it with postgres. My contribution will contain small modifications to ease massive
virtual drupal'ing under Postgres: a postgres schema for each drupal host
-- a kind of "HOME" schema for each customer.
It:

  • avoids getting thousands of SQL tables intermixed in one schema,
  • enables an easy "drop schema ..." when destroying a virtual drupal site
  • helps with backing up virtual drupal hosts (pg_dump -n schema ...) in single customer data backup files.

First tests with some drupal modules including ubercart ran well.

Status: Fixed » Closed (fixed)

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