[MojoMojo] Postgresql problem with GROUP BY clauses

[mschumann]

Hi,

I have a MojoMojo-1.02 installation with a Postgresql 8.4 backend.
When clicking on any 
http://www.marcoschumann.de/catalyst/mojomojo/bugs/postgresql-wrong-group-by-clause
"tags" link , there is an error page.

I checked the apache log and found

[error] DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute
failed: column "content.created" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: ...epth, me.lft, me.rgt, me.content_version ORDER BY content.cr...
                                                             ^ [for
Statement "SELECT me.id, me.version, me.parent, me.name, me.name_orig,
me.depth, me.lft, me.rgt, me.content_version FROM page as me, page as
ancestor, tag, content WHERE ( ( ( me.id =ancestor.id OR ( me.lft >
ancestor.lft AND me.rgt < ancestor.rgt ) ) AND ancestor.id = ? AND
content.page =me.id AND content.version =me.content_version AND me.id
=tag.page AND tag = ? ) ) GROUP BY me.id, me.version, me.parent, me.name,
me.name_orig, me.depth, me.lft, me.rgt, me.content_version ORDER BY
content.created DESC" with ParamValues: 1='1', 2='G<E4>stehaus Behnke'] at
/usr/share/perl5/MojoMojo/Schema/Result/Page.pm line 219

This is caused by the GROUP BY clause with its column missing in the SELECT
clause, which is 
http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-GROUPBY not
supported by Postgresql :

"When GROUP BY is present, it is not valid for the SELECT list expressions
to refer to ungrouped columns except within aggregate functions, since there
would be more than one possible value to return for an ungrouped column."

Thus there are two possibilities:

* add the missing column to the SELECT clause
* drop the GROUP BY clause

Appearantly dropping the GROUP BY clause is not an option, so I am currently
searching for a patch to add another column. Other methods need to be
checked, too. Maybe someone has a fix already?

Kind regards
-- 
View this message in context: http://mojomojo.2358427.n2.nabble.com/Postgresql-problem-with-GROUP-BY-clauses-tp5908804p5908804.html
Sent from the mojomojo mailing list archive at Nabble.com.