I’ve had this error going on for the last 2 years or so (the cause seemed to be driven by adding a new Custom Field configuration). I’ve been on the 4.0 release for a while (upgraded to various versions through the 2.x and 3.x series). I hoped that it would fix itself when I migrated up to 4.4 (only done in test so far), but no such luck.
It is generated any time a ticket is shown through the web interface, so it’s a pretty frequent error!
Oct 16 17:40:36 helium postgres[4097]: [5-1] ERROR: column “main.name” must appear in the GROUP BY clause or be used in an aggregate function at character 8
Oct 16 17:40:36 helium postgres[4097]: [5-2] STATEMENT: SELECT main.* FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField = main.id ) WHERE (ObjectCustomFields_1.ObjectId = ‘33’ OR ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND (main.LookupType = ‘RT::Queue-RT::Ticket-RT::Transaction’) GROUP BY main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC
When this fails, it seems to also prevent the Custom Fields functionality from working at all, which is a bummer.
I tried deleting everything from CustomFields, CustomFieldValues, ObjectCustomFields, and ObjectCustomFieldValues, but this didn’t help.
Environment: FreeBSD 9.3 (migrating to 10.3), PostgreSQL 9.1 (migrating to 9.5), RT 4.0.24 (migrating to 4.4.1).
I did a new RT 4.4.1 build/database in a VM and compared the schemas between my upgraded RT database and the clean one, and I didn’t see significant differences in the CustomFields, CustomFieldValues, ObjectCustomFields, and ObjectCustomFieldValues tables.
Any suggestions on what I could do to help resolve this error?
I�ve had this error going on for the last 2 years or so (the cause
seemed to be driven by adding a new Custom Field configuration). I�ve
been on the 4.0 release for a while (upgraded to various versions
through the 2.x and 3.x series). I hoped that it would fix itself when
I migrated up to 4.4 (only done in test so far), but no such luck.
It is generated any time a ticket is shown through the web interface,
so it�s a pretty frequent error!
Oct 16 17:40:36 helium postgres[4097]: [5-1] ERROR: column
“main.name” must appear in the GROUP BY clause or be used in an
aggregate function at character 8
Oct 16 17:40:36 helium postgres[4097]: [5-2] STATEMENT: SELECT main.*
FROM CustomFields main JOIN ObjectCustomFields ObjectCustomFields_1
ON ( ObjectCustomFields_1.CustomField = main.id ) WHERE
(ObjectCustomFields_1.ObjectId = ‘33’ OR ObjectCustomFields_1.ObjectId
= ‘0’) AND (main.Disabled = ‘0’) AND (main.LookupType =
‘RT::Queue-RT::Ticket-RT::Transaction’) GROUP BY main.id ORDER BY
MIN(ObjectCustomFields_1.SortOrder) ASC
When this fails, it seems to also prevent the Custom Fields
functionality from working at all, which is a bummer.
I tried deleting everything from CustomFields, CustomFieldValues,
ObjectCustomFields, and ObjectCustomFieldValues, but this didn�t help.
Environment: FreeBSD 9.3 (migrating to 10.3), PostgreSQL 9.1
(migrating to 9.5), RT 4.0.24 (migrating to 4.4.1).
I did a new RT 4.4.1 build/database in a VM and compared the schemas
between my upgraded RT database and the clean one, and I didn�t see
significant differences in the CustomFields, CustomFieldValues,
ObjectCustomFields, and ObjectCustomFieldValues tables.
Have a look at the perl SearchBuilder component, it might be that your
fresh build has the latest with some adjustments to prevent the error
that is thrown in your current env.
Oct 16 17:40:36 helium postgres[4097]: [5-1] ERROR: column
“main.name” must appear in the GROUP BY clause or be used in an
aggregate function at character 8
Oct 16 17:40:36 helium postgres[4097]: [5-2] STATEMENT: SELECT
main.* FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1 ON ( ObjectCustomFields_1.CustomField =
main.id ) WHERE (ObjectCustomFields_1.ObjectId = ‘33’ OR
ObjectCustomFields_1.ObjectId = ‘0’) AND (main.Disabled = ‘0’) AND
(main.LookupType = ‘RT::Queue-RT::Ticket-RT::Transaction’) GROUP BY
main.id ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC
When this fails, it seems to also prevent the Custom Fields
functionality from working at all, which is a bummer.
I tried deleting everything from CustomFields, CustomFieldValues,
ObjectCustomFields, and ObjectCustomFieldValues, but this didn’t
help.
Environment: FreeBSD 9.3 (migrating to 10.3), PostgreSQL 9.1
(migrating to 9.5), RT 4.0.24 (migrating to 4.4.1).
Have a look at the perl SearchBuilder component, it might be that your
fresh build has the latest with some adjustments to prevent the error
that is thrown in your current env.
Joop,
Thanks for the tip. However, both systems show the error and both
systems are running DBIx-SearchBuilder 1.66 (current in FreeBSD
ports).
I got this error again today after applying an update, and since I searched for the error and it led me back to this post, I’m posting my resolution steps.
(FreeBSD updated the DBIx::SearchBuilder package with a minor tweak about a dependency, and this threw away my local modifications.)
DBIx::SearchBuilder::Handle::Pg contains an assumption about the way that GROUP BY works that does not match my PostgreSQL configuration, and this caused the error. Removing it (from /usr/local/lib/perl5/site_perl/DBIx/SearchBuilder/Handle/Pg.pm) cleaned up the error. Here’s my patch, which requires a little more research to see whether my PostgreSQL config should be tweaked or if I should send this upstream?
--- Pg.pm.orig 2014-08-05 20:32:39.000000000 -0400
+++ Pg.pm 2017-06-22 10:45:09.819662000 -0400
@@ -236,13 +236,7 @@
# plan not guaranting order
my $groups;
- if ($self->DatabaseVersion =~ /^(\d+)\.(\d+)/ and ($1 > 9 or ($1 == 9 and $2 >= 1))) {
- # Pg 9.1 supports "SELECT main.foo ... GROUP BY main.id" if id is the primary key
- $groups = [ {FIELD => "id"} ];
- } else {
- # For earlier versions, we have to list out all of the columns
- $groups = [ map {+{FIELD => $_}} $self->Fields($table) ];
- }
+ $groups = [ map {+{FIELD => $_}} $self->Fields($table) ];
local $sb->{group_by} = $groups;
local $sb->{'order_by'} = [
map {