I'm running PostgreSQL 8.3.7 as my back-end database and it's very particular about query syntax. When I try to create a Book page, I get the following error:

warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /opt/www/drupal-6.12/includes/database.pgsql.inc on line 139.
user warning: query: SELECT DISTINCT g.gid FROM simple_access_groups g INNER JOIN simple_access_roles r ON g.gid = r.gid WHERE r.rid IN (2) ORDER BY weight, name in /opt/www/drupal-sites/all/modules/simple_access/simple_access.module on line 597.

In simple_access_group_select, you need to add g.gid to the ORDER BY and weight and to the SELECT list. So, line 597 should read:

$result = db_query('SELECT DISTINCT ON (g.gid) g.gid, weight, name FROM {simple_access_groups} g INNER JOIN {simple_access_roles} r ON g.gid = r.gid WHERE r.rid IN ('. implode(',', array_fill(0, count($roles), '%d')) .') ORDER BY g.gid, weight, name', $roles);

Comments

alpapan’s picture

alpapan commented
Priority: Normal » Critical

verifying and subscribing
also at
/admin/content/simple_access

gordon’s picture

gordon commented

I have made a fix to the select, I am not sure it it will work, but if you can give it a go. I couldn't use the select above as it would stuff up the weights.

gordon’s picture

gordon commented
Status: Active » Fixed

I have install postgresql and the dev version should be working, Can you please give it a test and make sure.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Anonymous’s picture

Anonymous (not verified) commented
Version: 6.x-2.0-rc2 » 6.x-2.0-rc4
Status: Closed (fixed) » Needs work

I tried the rc4 and the dev version. The problem is not fixed. When I trie to alter a node protected with simple access, I get this message: 

 * warning: pg_query() [function.pg-query]: Query failed: FEHLER: bei SELECT DISTINCT müssen ORDER-BY-Ausdrücke in der Select-Liste erscheinen in /server/includes/database.pgsql.inc on line 139.
 * user warning: query: SELECT DISTINCT(g.gid) FROM simple_access_groups g INNER JOIN simple_access_roles r ON g.gid = r.gid WHERE r.rid IN (2,12,20) ORDER BY g.weight, g.name in /server/sites/default/modules/simple_access/simple_access.module on line 598.
Anonymous’s picture

Anonymous (not verified) commented
Status: Needs work » Needs review

Hi gordon. Based on this article from Jeff's SQL Server Blog I found a solution.
SELECT g.gid FROM {simple_access_groups} g INNER JOIN {simple_access_roles} r ON g.gid = r.gid WHERE r.rid IN ('. implode(',', array_fill(0, count($roles), '%d')) .') GROUP BY g.gid,g.name ORDER BY max(g.weight), g.name
Please make sure that the 'weight' still works, as you intended.

gordon’s picture

gordon commented
Status: Needs review » Fixed

I have looked at this again, and made some changes to the dev version.

Give it ago and I will see what difference it makes.

Gordon.

Anonymous’s picture

Anonymous (not verified) commented

It works!

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Needs work

Sorry to bother you again gorden, but I found an other simular mistake. 

* warning: pg_query() [function.pg-query]: Query failed: FEHLER: bei SELECT DISTINCT müssen ORDER-BY-Ausdrücke in der Select-Liste erscheinen in /storage/www/includes/database.pgsql.inc on line 139.
* user warning: query: SELECT DISTINCT n.title, na.nid FROM node n INNER JOIN node_access na ON n.nid = na.nid WHERE na.realm='simple_access' AND na.gid > 0 AND na.grant_view = 1 ORDER BY n.nid DESC LIMIT 50 OFFSET 0 in /storage/www/sites/default/modules/simple_access/simple_access.admin.inc on line 281.
Anonymous’s picture

Anonymous (not verified) commented

The problem is based on these three lines of SQL:

SELECT DISTINCT n.title, na.nid FROM {node} n INNER JOIN {node_access} na ON n.nid = na.nid WHERE na.realm='simple_access' AND na.gid > 0 AND na.grant_update = 1

in simple_access.admin.inc on line 261

SELECT DISTINCT n.title, na.nid FROM {node} n INNER JOIN {node_access} na ON n.nid = na.nid WHERE na.realm='simple_access' AND na.gid > 0 AND na.grant_delete = 1

in simple_access.admin.inc on line 266

SELECT DISTINCT n.title, na.nid FROM {node} n INNER JOIN {node_access} na ON n.nid = na.nid WHERE na.realm='simple_access' AND na.gid > 0 AND na.grant_view = 1

in simple_access.admin.inc on line 272

The "ORDER BY" is added by the tablesort_sql function in simple_access.admin.inc on line 280.
When you use the "GROUP BY" clause, in the pager_query function in line 281, you have to pass a query that will be used to count the records.

Is DISTINCT necessary? I removed it and had no duplicates.

alpapan’s picture

alpapan commented
Version: 6.x-2.0-rc4 » 6.x-2.x-dev

just to verify that CvJ's fix works for problem @ /admin/content/simple_access

gordon’s picture

gordon commented
Status: Needs work » Closed (won't fix)

I have fixed this issue by actually removing this functionality which is now covered by the devel node access module.