SELECT DISTINCT error on Postgres
organicveggie - June 14, 2009 - 18:22
| Project: | Simple Access |
| Version: | 6.x-2.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | won't fix |
Jump to:
Description
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);
#1
verifying and subscribing
also at
/admin/content/simple_access
#2
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
I have install postgresql and the dev version should be working, Can you please give it a test and make sure.
#4
Automatically closed -- issue fixed for 2 weeks with no activity.
#5
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
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.namePlease make sure that the 'weight' still works, as you intended.
#7
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
It works!
#9
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
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 = 1SELECT 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 = 1SELECT 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 = 1The "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
just to verify that CvJ's fix works for problem @ /admin/content/simple_access
#12
I have fixed this issue by actually removing this functionality which is now covered by the devel node access module.