Database error when rendering comments in PostgreSQL
| Project: | Drupal |
| Version: | x.y.z |
| Component: | comment.module |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed |
After an upgrade from rc2 to rc3 I get this error when I create a blog entry and everytime I access the entry:
--------------------------------------------------------------------------------------------
* warning: pg_query(): Query failed: ERROR: column "c.status" must appear in the GROUP BY clause or be used in an aggregate function in /home/erl/www/drupal-4.7.0-rc3/includes/database.pgsql.inc on line 84.
* user warning: query: SELECT c.cid as cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, c.homepage, u.uid, u.name AS registered_name, u.picture, u.data, c.score, c.users, c.thread, c.status FROM comments c INNER JOIN users u ON c.uid = u.uid WHERE c.nid = 24 AND c.status = 0 GROUP BY c.cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, u.picture, c.homepage, u.uid, u.name, u.picture, u.data, c.score, c.users, c.thread ORDER BY c.thread DESC LIMIT 30 OFFSET 0 in /home/erl/www/drupal-4.7.0-rc3/includes/database.pgsql.inc on line 103.
--------------------------------------------------------------------------------------------
I use postgresql 8.1.3 with php.4.4.2
#1
I have reverted to RC2 and restored a backup from RC2 and everything works without problmes with RC2.
#2
I believe this is a problem with comment_render, introduced by this:
http://drupal.org/node/51002
#3
I believe adding the
c.statuscolumn to the GROUP BY clausule probably fixes the problem.#4
Hi Markus,
in your patch you add c.status to GROUP BY only for normal users (without administer comments right). Is that enough?
If someone has administer comments right he won't have c.status added to GROUP BY and I believe he will get the error.
Could you test it?
I think correct way would be to add c.status to GROUP BY in both cases.
#5
Oops! you're right.
c.statusshould be added always. I believe I was distracted by the use of this field in the where condition.It should be fixed in this patch now.
#6
I believe this is more accurate
#7
I haven't tested it on "live" system, but I believe it'll work [the query works when executed in pgsql "command line"]
The change is fairy simple anyway. I'd set it to RTBC, but someone with mysql could test it
#8
Tested this patch with MySQL and committed it to CVS HEAD.
Please mark it 'fixed' after it has been confirmed to work with PostgreSQL.
#9
Should be fixed.
#10
Confirmed: Patch fixes issue.
#11