Not PostgreSQL compatible [#1032034]

Comment #1

lochii commented 19 January 2011 at 01:56

Also, this SQL :

SELECT COUNT(uid) FROM users WHERE status = 0 AND created > 0 AND login = 0 ORDER BY created;

Should drop its ORDER BY clause as it is not only not used but disliked by PostgreSQL.

Log in or register to post comments

Comment #2

jaypan

Victoria, BC

commented 19 January 2011 at 03:14

lochii

Thank you for letting me know this. I apologize for the problems you are seeing. As I don't use Postgresql, I don't ever see these errors.

I will roll out an update rewriting the SQL using the database abstraction layer so that you do not see these issues anymore. I should be able to get that out sometime this week.

Log in or register to post comments

Comment #3

jaypan

Victoria, BC

commented 20 January 2011 at 05:48

Status:

Active

» Fixed

First, I have updated the module in version 6.1 to properly use the database abstraction layer for the limit/offset clause.

Second, I cannot find any information regarding postresql disliking ORDER BY clauses, and I actually came across the part of the manual where it describes how to use them: http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-ORDERBY

I can't actually see an SQL based language not using an ordering method of some sort - ordering queries is an essential part of database calls. It is also fairly pertinent to the query in which it resides in this module.

As such, I am going to mark this thread as fixed, however if you have some documentation regarding postresql not liking ORDER BY clauses, and an alternative method for ordering the clauses, please feel free to post it and set the thread to active, and I will take a look at it.

Log in or register to post comments

Comment #4

lochii commented 20 January 2011 at 23:00

I think what I'm saying about ORDER BY is that a query that performs "SELECT COUNT" should only return one row (the count) and the ordering is not important, if you give this to postgres it will complain:

# SELECT COUNT(uid) FROM users WHERE status = 0 AND created > 0 AND login = 0 ORDER BY created;
ERROR: column "users.created" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...E status = 0 AND created > 0 AND login = 0 ORDER BY created;

The correct thing to do is to drop the ORDER BY clause as it is not needed:

# SELECT COUNT(uid) FROM users WHERE status = 0 AND created > 0 AND login = 0;
count
-------
3
(1 row)

Log in or register to post comments

Comment #5

jaypan

Victoria, BC

commented 20 January 2011 at 23:34

Of course! I get it. I was so hung up on figuring out why ORDER BY wasn't valid with postgresql that I wasn't actually looking at the query itself.
I'm not sure why I even had that there in the first place. It's entirely unnecessary. I'll fix it and roll out another release.

Log in or register to post comments

Comment #6

jaypan

Victoria, BC

commented 21 January 2011 at 01:45

Code updated and released in 6.x-1.2. Thanks for the report.

Log in or register to post comments

Comment #7

jaypan

Victoria, BC

commented 21 January 2011 at 01:50

As a side note for anyone who may come across this issue in D7, I also had this code included with the d7 module, however in using the database API, it may not show up. Regardless, I have fixed and committed the code, but I will only roll out a new release for this fix if someone is using D7 and has this issue. Otherwise, it will be included in the next release of the module whenever that comes about.