2009-07-05, 06:13 PM
Postgres 8.3.7 on CentOS w/ MyBB 1.4.8, 'inline moderation' 'Split Posts' feature generates...
solution likely would be to GROUP BY p.tid,p.pid in
but there are more of these same errors coming up, I just know. ayup. would any mysql expert tell me just what
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..
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.
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(q.pid) 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
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(a.aid) 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..
GROUP BY p.pid,p.tid,p.fid,p.visible,p.uid,t.visible,t.replies,t.unapprovedposts,t.attachmentcountworked.
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.