MyBB

Postgres 8.3.7 on CentOS w/ MyBB 1.4.8, 'inline moderation' 'Split Posts' feature generates...

Code:
MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
42803 - ERROR: column "p.tid" must appear in the GROUP BY clause or be used in an aggregate function
Query:
SELECT DISTINCT p.tid, COUNT as count FROM mybb_posts p LEFT JOIN mybb_posts q ON (p.tid=q.tid) WHERE p.pid IN (1272) GROUP BY p.pid

solution likely would be to GROUP BY p.tid,p.pid in `moderation.php` line 1509 or thereabouts.

but there are more of these same errors coming up, I just know. ayup. would any mysql expert tell me just what
Code:
SQL Error:
42803 - ERROR: column "p.tid" must appear in the GROUP BY clause or be used in an aggregate function
Query:
SELECT p.pid, p.tid, p.fid, p.visible, p.uid, t.visible as threadvisible, t.replies as threadreplies, t.unapprovedposts as threadunapprovedposts, t.attachmentcount as threadattachmentcount, COUNT as postattachmentcount FROM mybb_posts p LEFT JOIN mybb_threads t ON (p.tid=t.tid) LEFT JOIN mybb_attachments a ON (a.pid=p.pid) WHERE p.pid IN (1272,1273) GROUP BY p.pid

is supposed to do? I see that as an ambiguous query with zero guidance as to what to do with the ungrouped values in conjunction with the grouped and aggregated variables.

ok, changing that (in split_posts() in class_moderation.php) to..
Code:
GROUP BY p.pid,p.tid,p.fid,p.visible,p.uid,t.visible,t.replies,t.unapprovedposts,t.attachmentcount

worked. to explain myself a little better.... those queries that ask for multiple field values but only GROUP BY a single field... what if there were more than one value of any of those other fields for a given GROUP BY value ? which one would be returned? yes, you know and I know that in this specific case, there's only one of each p.pid, but I don't think the database can assume that from the given statement without more knowlege.

MySQL seems to just decide its OK if its a little ambiguous and assumes you know what you're doing. PostgreSQL on the other hand is more strictly SQL standard compliant and is complaining that the query as formed is ambiguous.