Download & Extend
Simple Access » Issues

SELECT DISTINCT error on Postgres

Posted by organicveggie on June 14, 2009 at 6:22pm
Project:Simple Access
Version:6.x-2.x-dev
Component:Code
Category:bug report
Priority:critical
Assigned:Unassigned
Status:closed (won't fix)

Issue Summary

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);
Login or register to post comments

Comments

#1

Posted by alpapan on June 14, 2009 at 7:10pm
Priority:normal» critical

verifying and subscribing
also at
/admin/content/simple_access

#2

Posted by gordon on June 15, 2009 at 12:56pm

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.

#3

Posted by gordon on June 21, 2009 at 4:32pm
Status:active» fixed

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

#4

Posted by System Message on July 5, 2009 at 4:40pm
Status:fixed» closed (fixed)

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

#5

Posted by CvJ on September 6, 2009 at 4:36pm
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.

#6

Posted by CvJ on September 7, 2009 at 11:50am
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.

#7

Posted by gordon on September 8, 2009 at 5:20am
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.

#8

Posted by CvJ on September 8, 2009 at 7:04am

It works!

#9

Posted by CvJ on September 8, 2009 at 10:29am
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.

#10

Posted by CvJ on September 11, 2009 at 11:35am

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.

#11

Posted by alpapan on September 15, 2009 at 2:49pm
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

#12

Posted by gordon on September 16, 2009 at 7:18am
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.