error in query when using old postgresql version with setup multi-language site

[slibbe]

Steps to reproduce the issue

Setting up new multi-language site (5 languages), with PostGresQL 8.4.10.

Expected result

Joomla multi-language site.

Actual result

Joomla multi-language site with post-install errors that cannot be fixed.

Error

ERROR: column "a.title" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.id, a.title, a.checked_out, a.checked_out_time, a.... ^SQL=SELECT a.id, a.title, a.checked_out, a.checked_out_time, a.created, a.hits,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,COUNT(asso2.id)>1 as association FROM #__content AS a LEFT JOIN "#__languages" AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__categories AS c ON c.id = a.catid LEFT JOIN #__users AS ua ON ua.id = a.created_by LEFT JOIN #__associations AS asso ON asso.id = a.id AND asso.context='com_content.item' LEFT JOIN #__associations AS asso2 ON asso2.key = asso.key GROUP BY a.id, l.title, l.image, uc.name, ag.title, c.title, ua.name ORDER BY a.hits DESC LIMIT 5
ERROR: column "a.title" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.id, a.title, a.checked_out, a.checked_out_time, a.... ^SQL=SELECT a.id, a.title, a.checked_out, a.checked_out_time, a.access, a.created, a.created_by, a.created_by_alias, a.featured, a.state,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,COUNT(asso2.id)>1 as association FROM #__content AS a LEFT JOIN "#__languages" AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__categories AS c ON c.id = a.catid LEFT JOIN #__users AS ua ON ua.id = a.created_by LEFT JOIN #__associations AS asso ON asso.id = a.id AND asso.context='com_content.item' LEFT JOIN #__associations AS asso2 ON asso2.key = asso.key GROUP BY a.id, l.title, l.image, uc.name, ag.title, c.title, ua.name ORDER BY created DESC LIMIT 5
An error has occurred.

System information (as much as possible)

PHP 5.6, PostGreSQL 8.4.10

Additional comments

However on another system with PHP 5.6 and PostGreSQL 9.3 this issue cannot be reproduced. So it appears to be an issue of a rather old PostgreSQL version (2011-12-05).

[slibbe]

When the same is tested against current staging a similar but much shorter error occurs:

Error

ERROR: column "a.title" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.id, a.title, a.checked_out, a.checked_out_time, a.... ^
An error has occurred.

With PHP 5.6 and the old PostgreSQL 8.4.10 as well.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14708.}

[slibbe]

Text may be unclear, sorry. I tested staging against the setup with the old postgresql, not with the recent one.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14708.}

[brianteeman]

Issues with this old version of postgres have come up before. I suggested that we updated our system requirements to postgres 9.x

To be honest thee are so few postgres users in total and even less (if any) on such an old version my 2c is that this isnt worth volunteer time to fix

[mbabker]

7 systems running PostgreSQL 8.4 have pinged the stats server in the last week. So sadly, there are people running it it seems.

[photodude]

I question when the last time we tested (or had testers using) the minimum of PostgreSQL 8.3.18
recommended is PostgreSQL 9.1+ even our test suit defaults to PostgreSQL 9.1 https://github.com/travis-ci/travis-cookbooks/tree/a68419ebe0ce92876a70534cd145ddd931d0feee/ci_environment/postgresql

The last PR I know of which was discussing the option to raise the minimum version of PostgreSQL is #12839

It was suggested that we are stuck with PostgreSQL is 8.3.18 until J4. For J4 we definitely should move to PostgreSQL 9.2+ (the last version with support within the expected J4 project release date)

[slibbe]

I agree. I found lately that Siteground hosting offers PostGresql in version 8.4.10 together with a choice of PHP 5.5, 5.6, 7.0, 7.1 , while their MySQL version is 5.6.28 . I can't see a reason for offering such an outdated version.

@mbabker You can subtract 3 of those 7; that was me. One for setup, one for testing if I could reproduce, and one for testing if it also occurs in staging. I certainly am not gonna use it in this version.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14708.}

[brianteeman]

i am going to close this. It is just not worth the effort to resolve for such an old version etc etc

[jjorbas]

Go to the administrator folder and edit:

./components/com_menus/presets/joomla.xml

look fot sql_group=

add ,a.title, a.menytype after a.id

Save file, and now administration area will work

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/14708.}