postgresql error: field must appear in the GROUP BY

Upon further inspection, this appears to be a views module issue. Postgres seems to be very picky about what goes in the group by clause.

I tinkered around with the views module and came up with the attached 3 line addition. Basically what it does is adds a groupby every time a new orderby is added if there already exists a groupby.

I am not sure if this is the right way to go, but it creates a query that makes postgres happy.

Well, I committed this patch. I'm not completely confident in it, but I guess we'll see if postgres users suddenly start having weird problems with group by -- I don't think it'll affect anything else terribly negatively.

This patch makes it impossible to sort by anything that is not in the group by. An example is if you want to group by taxonomy and sort by last updated date. See http://drupal.org/node/134375 for an example. This is a big deal to me since it breaks some of my modules and it seems wrong that you can't do that.

There are many cases I can see where you would want to sort by last updated date without grouping by it. Adding grouping by last updated date to any other grouping will mostly produce a list of individual items instead of the grouping you really want. How many items are updated at exactly the same second?? That's what a grouping by last updated date will give you.

So my question to someone who can test this in postgres is whether there is some way to make a view that groups by taxonomy (or content type or anything else) and sorts by last updated work without also grouping by last updated date. Surely there must be some way to make this work in postgres???

As soon as I posted this I had another thought. The problem here is that the sort is only needed in the full view, not the summary view, and the sort by last updated could be completely ignored for the summary view. So instead of adding a groupby for each sortby, instead we could remove the sortby in the summary view for any field that is not already in the groupby. I think that would make postgres work without breaking the ability to sort by fields that are not in the groupby.

In other words, roll back the patch that adds the sortby to the groupby, and instead remove sortbys that are not in the groupby when in a summary view. That should make postgres happy and still do things like make a view that groups by taxonomy and sorts by last updated date.

I haven't tested this idea and have no time to try to make a patch right now, but if no one else picks this up, I'll try to do it later.

Hmm. That's not a bad idea; that way sort by view can at least retain 'relevant' sorting information, though does it become possible to actually create a summary that's sorted differently than the 'sort ascending' or 'sort descending' settings? Personally I never use the 'sort as view' setting because it's rarely relevant.

This is causing some weirdness in 1.6b5 for me, as well. I have a pretty simple view (list nodes of type news, order by sticky desc, created desc) with the RSS feed argument (sorted as view). The feed works fine, but the page view is broken, as the query gets mangled:

SELECT count(node.nid) AS num_nodes, node.sticky AS node_sticky, node.created AS node_created_created FROM (SELECT DISTINCT ON (nid) * FROM node) node WHERE (node.type IN ('news')) AND (node.status = '1') GROUP BY , node_sticky, node_created_created ORDER BY node_sticky DESC, node_created_created DESC LIMIT 5 OFFSET 0

Note the dangling comma right after GROUP BY. I'm presuming that's from a blank column alias being added to the groupby array.

The pager query for the same view is also messed up:

SELECT count(DISTINCT()) FROM (SELECT DISTINCT ON (nid) * FROM node) node WHERE (node.type IN ('news')) AND (node.status = '1')

Here obviously it's the DISTINCT() causing issues, besides the redundancy of how the query is constructed.

I suspect for both of these db_distinct_field() is also mucking things up. I'm running the patch from issue #128846 which fixes several other instances of problems with the db_rewrite_sql() calls.

Sorry, disregard that last comment. I had a different error in the view, much more apparent after a weekend and some coffee. Although the queries for Postgres still end up rather convoluted, but that's from db_distinct_field.

Here obviously it's the DISTINCT() causing issues, besides the redundancy of how the query is constructed.

I second that. I have a pager-enabled list view provided as page view, and defined as follows:

Fields: Created time and title, title as link, other options as default.
Arguments: None.
Filters:

• Node: Published. Operator is equals, and value is yes.
• Taxonomy: Term. Operator is all of, value is a term from vocabulary A and option has no values defined.
• Taxonomy: Terms for Vocabulary B. Options is all of, no value is selected, option has value "3" defined. Option is exposed to users, Exposed filter is marked as optional, and has “Force Single” and “Lock Operator” options selected
• Node: Distinct. Operator Is, Value distinct Everything works if this is not defined.

Sort Criteria: Node: Created Time. Order is descending and option is normal.

When the View URL is accessed, the following errors will be displayed as result:

    <li> warning: pg_query(): Query failed: ERROR: column "node.title" must appear in the GROUP BY clause or be used in an aggregate function in /var/www2/upgtesti/includes/database.pgsql.inc on line 125.</li>
    <li> user warning: query: SELECT DISTINCT(node.nid), node.created AS node_created_created, node.created AS node_created, node.title AS node_title, node.changed AS node_changed FROM (SELECT DISTINCT ON (nid) <li> FROM dbprefix_node) node LEFT JOIN dbprefix_term_node term_node ON node.nid = term_node.nid LEFT JOIN dbprefix_term_hierarchy term_hierarchy ON term_node.tid = term_hierarchy.tid LEFT JOIN dbprefix_i18n_node i18n ON node.nid = i18n.nid WHERE (i18n.language ='fi' OR i18n.language ='' OR i18n.language IS NULL) AND ( (node.status = '1') AND (term_node.tid = '103') ) GROUP BY node.nid, node_created_created ORDER BY node_created_created DESC LIMIT 10 OFFSET 0 in /var/www2/upgtesti/includes/database.pgsql.inc on line 144.</li>

I also have the sort patch applied,

Fixed version information.

For those impatient of us, who (like me) need a working PostgreSQL system right now, I've done a patch that should (in most cases at least) puts things to work. But it is still a huge hack that rips open already-built variables in $this->fields array using regular expressions. So it is not even close to any kind of commit-ready state. It will, however, make the query work by making sure that whatever relations were SELECTed, they'll get into GROUP BY clause (if there is one), unless it is a function like count().

Basically, if you use GROUP BY, PostgreSQL requires that everything in SELECT or in ORDER BY is included in it. vhmauery's patch takes care of the ORDER BYs, mine of the SELECTs (although not quite as elegant way).

It seems that updating Views to 1.6 final version fixed this issue. (But please note that the sort patch is still necessary, otherwise things won't work.)

I am still having this problem postgresql on 1.6. The first patch made it into the release, but patch #2 (the groupby hack) didn't, and 1.6 still throws errors for missing groupby fields. I installed a fresh views 1.6 and I still show this problem. Anybody else still see this? If not, what might be amiss? I upgraded from 1.5. Could table alterations have gone awry?

Another note. I am NOT using Acidfree Albums, just straight views. If create my custom query, enable node:distinct, I get the familiar group by error. Postgresql expects the explicit field names in the group by statement. Views 1.6 or a new version of Acidfree Albums might resolve issues with that particular software, but within views this functionality is still broken.

yea same problem.

http://drupal.org/node/128846 <-- This made it in somewhere between 5.7-5.10. I am running 1.6 fresh.

tagging issue

if anything gets committed, that second patch probably should be looked at for it.

At this time, only security fixes will be made to the 5.x version of Views.