#191 - Postgresql and sources/blocks/main_news.php

By Guest
Added 14th Jul 2010, 1:46 PM
12 views

Identifier	#191
Issue type	Major issue (breaks an entire feature)
Title	Postgresql and sources/blocks/main_news.php
Status	Completed
Handling member	Chris Graham
Addon	General / Uncategorised
Description	Once logged in as admin I get the error: Unfortunately a query has failed [SELECT *,p.id AS p_id FROM cms4_news p LEFT JOIN cms4_news_category_entries d ON d.news_entry=p.id WHERE (1=1 OR 1=1) AND validated=1 AND date_and_time>=1278510143 GROUP BY p.id ORDER BY p.date_and_time DESC]
Steps to reproduce
Additional information	All fields to be selected in postgresql, when there is a "group by", must be 1) be in the "group by" OR 2) be only in an aggregate function. So the fix is either, fill up the group by's (lines 114-123 on sources/blocks/main_news.php) but, since you are selecting "*" that seems pointless unless you have complete duplicate records, which seems unlikely, and is not useful. So the fix is to remove the group by clauses.
Funded?	No

The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".

Rating

Unrated

Comments

By Guest posted 14th Jul 2010, 1:52 PM

I was aware of this one actually, in the sense of it not meeting the SQL spec rather than the sense of knowing it failing on postgresql. The duplicate records are due to the join. The problem is someone can add a news entry such that it is in the same primary and secondary category and hence it comes out twice. Alternatively, a filter might filter on multiple categories and hence it comes out multiple times.
GROUP BY is the only way I really know to remove duplicates without having to do processing in PHP, which breaks the ability for COUNT(*) to work for pagination or is a performance hit due to reading in lots of rows. Any suggestions welcome!

By Guest posted 15th Jul 2010, 4:52 PM

Ok, this one was tough. The only way to solve without making the code into spaghetti was to do a small compromise. Non-MySQL users will have pagination that gets a bit uneven if items are returned that are in multiple categories that are filtered on, due to the duplicates being taken out (in PHP) and leaving holes in the perceived ordering.
I don't think this'll cause any issues for people.