Posted by nileshgr on April 3, 2010 at 5:40pm
2 followers
| Project: | Re: Comment subjects |
| Version: | 7.x-2.0-alpha1 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | needs review |
| Issue tags: | PostgreSQL |
Issue Summary
Hi,
6.x-2.0-beta1 had a SQL query which doesn't work on PostgreSQL.
I have made a patch for it.
| Attachment | Size |
|---|---|
| comment-subject-pgsql.patch | 647 bytes |
Comments
#1
thanks
#2
it seems that at the time I addressed that query as if comments had revisions (like nodes do),
that's why the group by cid,nid,
but cid is primary key, therefore that group by was nonsense
BUT, what version are we talking about?
I can't find that on my repo,
the proposed fix is correct, but it is how it currently is
I just re-downloaded 6.x-2.0-beta1, 6.x-2.x-dev, 7.x-2.0-alpha1, and it is that way on those versions
#3
PostgreSQL follows SQL Standards strictly. Hence you cannot have a 'WHERE' in a select statement which has an aggregate function.
Download 6.x-2.0-beta1, open comment_subject.token.inc and see line 63.
I had this version on my site, and found the errors from php and pg_query in the error log.
Wait a minute. I think the diff arguments were misplaced. I will upload another patch :(
#4
#5
ah... is the other way around
in that case, would be Postgre ok having this:
<?php$query = 'SELECT COUNT(cid) FROM {comments} GROUP BY nid HAVING nid = %d AND cid < %d ORDER BY cid ASC';
?>
you see, it is difficult to read the proposed Group By cid,nid when cid is primary key and therefore unique
also, a reference to Postgree documentation would be ideal for a comment above that query
to know later why it was changed to a groupby-having instead of a simple where
#6
I don't have any reference as of now, but I am sure about it. I will update if I get some reference. MySQL doesn't follow SQL standards strictly so a WHERE may work with an aggregate function. But PostgreSQL is exactly opposite to MySQL in this aspect.
If you alter the code, so that it looks like this:
<?php$query = 'SELECT COUNT(cid) FROM {comments} GROUP BY nid HAVING nid = %d';
?>
it works. I've tested it. And I think we need only the count of comments irrespective of cid.
#7
could you please, try the following
<?php$query = 'SELECT COUNT(cid) FROM {comments} WHERE nid = %d GROUP BY nid';
?>
#8
no, only the preceding ones
the restriction and ORDER BY are needed
#9
Yeah this works. It provides the count of comments per node. (PS: Only one row).
It works even with HAVING instead of WHERE, but the order matters.
WHERE works if placed before GROUP BY, opposite for HAVING..
A new finding:
<?php
$sql = 'SELECT COUNT(cid) FROM comments WHERE nid = %d and cid < %d GROUP BY nid';
?>
But if you add ORDER BY clause, it won't.
#10
then #5 would do?
#11
no, #9 is fine, don't need the order by, just the cid < %d
thanks for your contrib
#12
Here's a patch incase anyone needs it :)
#13
committed to 6.x-2.x
#14
the same would be needed for 7.x-2.x
#15
attached path for 7.x-2.x