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");
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | group_by_label.patch | 1.86 KB | Norbert Poellmann |
Comments
Comment #1
Island Usurper commentedThanks 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:
Changes are committed.
Comment #2
Norbert Poellmann commentedyes ... 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:
First tests with some drupal modules including ubercart ran well.