[rt-users] SQL error from Pg on 4.0.21

[Bill McGonigle]

Hi, everybody,

I've got a 4.0.21 system (recently upgraded from 4.0.8)  Database is
Postgresql 8.4.20.  I'm seeing intermittent ticket creation errors after
the update, and when I go to the log, I see SQL errors which are
reproducible at the console (assuming causation without proof):

rt4=> SELECT main.* FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1  ON ( ObjectCustomFields_1.CustomField = main.id )
 WHERE (ObjectCustomFields_1.ObjectId = '5' 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
rt4-> ;
ERROR:  column "main.name" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: SELECT main.* FROM CustomFields main JOIN ObjectCustomFields...
               ^
Could somebody give me a pointer to where this might be generated?   Or
if anybody knows of a fix that's be great too.

These variants are valid syntax, but I'm not up on the code enough to
know what it's looking for or exactly where to tweak it:

rt4=> SELECT main.id FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1  ON ( ObjectCustomFields_1.CustomField = main.id )
 WHERE (ObjectCustomFields_1.ObjectId = '5' 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;
 id
----
(0 rows)

rt4=> SELECT main.* FROM CustomFields main JOIN ObjectCustomFields
ObjectCustomFields_1  ON ( ObjectCustomFields_1.CustomField = main.id )
 WHERE (ObjectCustomFields_1.ObjectId = '5' OR
ObjectCustomFields_1.ObjectId = '0') AND (main.Disabled = '0') AND
(main.LookupType = 'RT::Queue-RT::Ticket-RT::Transaction')  GROUP BY
main.id,main.name,main.type,main.description,main.sortorder,main.creator,main.created,main.lastupdatedby,main.lastupdated,main.disabled,main.lookuptype,main.repeated,main.pattern,main.maxvalues,main.basedon,main.rendertype,main.valuesclass
ORDER BY MIN(ObjectCustomFields_1.SortOrder) ASC
;
 id | name | type | description | sortorder | creator | created |
lastupdatedby | lastupdated | disabled | lookuptype | repeated |
pattern | maxvalues | basedon | rendertype | valuesclass
----+------+------+-------------+-----------+---------+---------+---------------+-------------+----------+------------+----------+-
--------+-----------+---------+------------+-------------
(0 rows)




Thanks,
-Bill

-- 
Bill McGonigle, Owner
BFC Computing, LLC
http://bfccomputing.com/
Telephone: +1.855.SW.LIBRE
Email, IM, VOIP: bill at bfccomputing.com
VCard: http://bfccomputing.com/vcard/bill.vcf
Social networks: bill_mcgonigle/bill.mcgonigle