[drupal-devel] [bug] Problems with PostgreSQL and GROUP BY statements

[Jeremy]

Issue status update for 
http://drupal.org/node/20255
Post a follow up: 
http://drupal.org/project/comments/add/20255

 Project:      Drupal
 Version:      cvs
 Component:    throttle.module
 Category:     bug reports
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  Anonymous
 Updated by:   Jeremy at kerneltrap.org
 Status:       patch (ready to be committed)

Agreed, the GROUP BY and ORDER BY are both unnecessary.   Please commit.




Jeremy at kerneltrap.org



Previous comments:
------------------------------------------------------------------------

Sun, 10 Apr 2005 09:05:22 +0000 : Anonymous

I set up Drupal from CVS yesterday, using PostgreSQL 8.0 as database.
viewing pages as anonymous user often generates the following warning:


warning: pg_query(): Query failed: ERROR:  column "sessions.timestamp"
must appear in the GROUP BY clause or be used in an aggregate function
in /www/htdocs/includes/database.pgsql.inc on line 45.always followed
by:


user error: 
query: SELECT COUNT(DISTINCT(uid)) AS count FROM sessions WHERE
timestamp >= 1113122734 AND uid != 0 GROUP BY uid ORDER BY timestamp
DESC in /www/htdocs/includes/database.pgsql.inc on line 62.


------------------------------------------------------------------------

Sun, 10 Apr 2005 18:18:17 +0000 : Anonymous

okay, after some time playing around in drupal, I could near the problem
down a bit:
the message appeared about every 10th time... and my
auto-throttle-probability was set to 10%. Setting the probability to
50%, and the mentioned warnings came with that percentage.
so the error seems to be in the throttle module.


anyway, looking through the support issues, similar warnings ("xyz must
appear in the GROUP BY") happen in several places when pgsql is used.
Maybe the problem could be solved somewhere general instead of in each
module, or there should be at least a note regarding this on the module
developers handbook ( http://drupal.org/node/1395 ), as I assume this is
some MySQL-specific notation.




------------------------------------------------------------------------

Thu, 14 Apr 2005 13:37:34 +0000 : r11r

At my work, we ran into the same problem when doing a migration from
Oracle to Postgres.


The problem, I think, is that Postgres orders its results after the
select statement not before
so that it's impossible for it to order the results on a column you
didn't select.


I'm told we mostly ended up rewriting the queries to avoid this problem
but given the information
below, there might be a way around it without changing the queries too
much.


Here are a couple of links potentially relating to this same error:


http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php [1]


http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-DISTINCT
[2]


[1] http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php
[2]
http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-DISTINCT




------------------------------------------------------------------------

Fri, 15 Apr 2005 13:16:14 +0000 : adrian

We always change the queries. From the link you posted : 


The DISTINCT ON clause is not part of the SQL standard and is sometimes
considered bad style because of the potentially indeterminate nature of
its results. With judicious use of GROUP BY and subqueries in FROM the
construct can be avoided, but it is often the most convenient
alternative.


Adding the fields in the GROUP BY is the only way to have the sql work
for both mysql and postgres. 


I'm gonna make a patch for this now.




------------------------------------------------------------------------

Mon, 22 Aug 2005 14:13:12 +0000 : Uwe Hermann

Adrian, any updates on this?




------------------------------------------------------------------------

Mon, 29 Aug 2005 14:56:29 +0000 : Cvbge

Attachment: http://drupal.org/files/issues/drupal-head-throttle-20255.diff (856 bytes)

Attached patch should fix the error.
I've removed the ORDER BY (sorting does not have any impact on COUNT)
and GROUP BY (we already have DISTINCT).
Please test.




------------------------------------------------------------------------

Mon, 29 Aug 2005 21:08:08 +0000 : Cvbge

I believe there's a problem with throttling by registered users. The
code from throttle.module:




------------------------------------------------------------------------

Mon, 29 Aug 2005 21:12:49 +0000 : Cvbge

Currently throttling by registered users does not work. I've explained
it nicely and project.module or drupal have eaten my explanation
without any warning. The attached post fixes postgres' GROUP BY problem
and also fixes throttling problem.




------------------------------------------------------------------------

Mon, 29 Aug 2005 21:22:06 +0000 : Cvbge

There. I've even tested the patch and it works. +1 ;)