I'm often seeing the following two (paired) errors come up in the logs:

pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 3: ...LL OR aclu_fa.uid = 0) AND ( r.vid = 4 )ORDER BY v.weight, ... ^ in /home/acug/public_html/includes/database.pgsql.inc on line 139.

query: SELECT DISTINCT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid LEFT JOIN forum_access fa ON t.tid = fa.tid LEFT JOIN acl acl_fa ON acl_fa.name = CAST(t.tid AS VARCHAR) AND acl_fa.module = 'forum_access' LEFT JOIN acl_user aclu_fa ON aclu_fa.acl_id = acl_fa.acl_id AND aclu_fa.uid = 0 WHERE ((fa.grant_view >= 1 AND fa.rid IN (1)) OR fa.tid IS NULL OR aclu_fa.uid = 0) AND ( r.vid = 4 )ORDER BY v.weight, t.weight, t.name in /home/acug/public_html/modules/taxonomy/taxonomy.module on line 632.

This site, is, of course, running on PostgreSQL, not MySQL.

Files: 
CommentFileSizeAuthor
#11 taxonomy_pg_compatibility-879270-11.patch971 bytesBen Coleman
PASSED: [[SimpleTest]]: [MySQL] 190 pass(es).
[ View ]
#6 taxonomy_pg_compatibility-879270-6.patch952 bytesBen Coleman
PASSED: [[SimpleTest]]: [MySQL] 190 pass(es).
[ View ]

Comments

We did a break down of the issue above early this morning. A correction needs to be made.

The error occurs only when the Forums Access module is installed. It is constant when the user is in the anonymous role. It is not there when the role is the primary user or another administrator. This issue needs to be moved to that module to be addressed. If I am wrong in my assessment please excuse my error.

Let me know what additional information we could provide to clarify this issue.

Project:Drupal core» Forum Access
Version:6.17» 6.x-1.4
Component:taxonomy.module» Code

Please find out what pgsql is actually complaining about and what the correct query (according to pgsql, of course) would be.

The query originates in

<?php
/**
* Find all terms associated with the given node, ordered by vocabulary and term weight.
*/
function taxonomy_node_get_terms($node, $key = 'tid') {
  static
$terms;
  if (!isset(
$terms[$node->vid][$key])) {
   
$result = db_query(db_rewrite_sql('SELECT t.* FROM {term_node} r INNER JOIN {term_data} t ON r.tid = t.tid INNER JOIN {vocabulary} v ON t.vid = v.vid WHERE r.vid = %d ORDER BY v.weight, t.weight, t.name', 't', 'tid'), $node->vid);
   
$terms[$node->vid][$key] = array();
    while (
$term = db_fetch_object($result)) {
     
$terms[$node->vid][$key][$term->$key] = $term;
    }
  }
  return
$terms[$node->vid][$key];
}
?>

Forum Access rewrites this query to eliminate terms from the Forum vocabulary to which the current user has no View access.

Status:Active» Postponed (maintainer needs more info)

Little interest in getting things to work on pgsql...

Status:Postponed (maintainer needs more info)» Active

Sorry this slipped away from me. II was pretty new to Drupal and the Drupal communityj when I first posted this. I recently bumped into this again and found my old issue on this.

PostgreSQL is complaining because it requires that expressions in the ORDER BY clause must be in the SELECT list for SELECT DISTINCT. In the above query v.weight is not in the select list, but is in the ORDER BY clause.

Of course, in the original taxonomy code, this is not a SELECT DISTINCT and that requirement does not apply. I'm not yet familiar enough with the db_rewrite_sql api to know if Forum Access can add v.weight to the select list when it makes the other changes to this query. If so, it should do so. If not, then v.weight needs to be added to the select list in the taxonomy module (I can't imagine this hurting anything for MySQL). I've got a patch for the latter case, though it should be simple enough to do by hand.

Project:Forum Access» Drupal core
Version:6.x-1.4» 6.x-dev
Component:Code» taxonomy.module

Thank you for the analysis. The core function we're looking at is

<?php
function taxonomy_node_get_terms($node, $key = 'tid') {
  static
$terms;
  if (!isset(
$terms[$node->vid][$key])) {
   
$result = db_query(db_rewrite_sql('SELECT t.* FROM {term_node} r INNER JOIN {term_data} t ON r.tid = t.tid INNER JOIN {vocabulary} v ON t.vid = v.vid WHERE r.vid = %d ORDER BY v.weight, t.weight, t.name', 't', 'tid'), $node->vid);
   
$terms[$node->vid][$key] = array();
    while (
$term = db_fetch_object($result)) {
     
$terms[$node->vid][$key][$term->$key] = $term;
    }
  }
  return
$terms[$node->vid][$key];
}
?>

If I understand you correctly, then we need to change the SELECT to something like

SELECT t.*, v.weight AS v_weight_unused ...

in order to make this treatable by db_rewrite_sql() under PostgreSQL.

While it's a general problem with db_rewrite_sql(), I don't think it can be fixed in that context. Rather the query needs to be adjusted in core, so that the existing mechanism can work as intended.

Please provide your patch (to get your credit :-)) and set the Status to 'needs review' to get the ball rolling.

Status:Active» Needs review
StatusFileSize
new952 bytes
PASSED: [[SimpleTest]]: [MySQL] 190 pass(es).
[ View ]

I don't think you need the AS phrase, just ', v.weight' added to the select list. The patch is attached. I'll be interested to see if this passes. I have a similar patch for the blog module, and so far haven't been able to get it to pass (it won't apply), and so far I can't see why it won't apply.

My reason for adding the AS phrase was to avoid a potential conflict between t.weight and v.weight. I'm not sure what $term->weight will be if we leave this to chance...

Well, if you add the AS phrase, then the ORDER BY phrase needs to be updated to use new name. So, you'd have

'SELECT t.*,v.weight AS v_weight FROM {term_node} r INNER JOIN {term_data} t ON r.tid = t.tid INNER JOIN {vocabulary} v ON t.vid = v.vid WHERE r.vid = %d ORDER BY v_weight, t.weight, t.name'

(I'm assuming that v_weight would come out translating to $term->v_weight so adding a _unused wouldn't be necessary)

I would expect that both the qualified column or the alias would work, but I'm not sure...

The reason for adding _unused is to tip off the reader that we realize that the column result is not used. IOW, it shouldn't be removed, even if it's not used. It'll probably need a test to lock it down anyway.

Have you checked yet whether this issue is present in D7/D8? If yes, then it needs to be fixed there first.

From what I can see of the D7 taxonomy module, it's been rewritten quite a bit. There is no longer a taxonomy_node_get_terms function. From what I see in the code, D7 provides a way to build queries by creating a query object using db_select, and then adding terms to it. It looks to me like if this kind of problem exists in D7 or D8, it would be possible to fix it in the query object code (by making sure that any fields added via the orderBy method are also fields in the select list, and adding them if they are not), and for all I know, it may be doing that.

For sure, this patch won't apply to D7 or D8. If a similar problem exists there, the fix will be quite different.

StatusFileSize
new971 bytes
PASSED: [[SimpleTest]]: [MySQL] 190 pass(es).
[ View ]

OK, this is a revised patch with the _unused tag. PostgreSQL apparently will accept v.weight in the ORDER BY, you don't have to match the AS. I've tested this on our test site, and it works on PostgreSQL, both with and without Forum Access installed.

Title:SQL Syntax error in taxonomy moduleFix ORDER BY query in taxonomy.module to avoid db_rewrite_sql() producing PostgreSQL syntax error

I don't have PostreSQL to actually test this, but the patch looks good.

#989368: Drupal - Postgresql 8.4 when posting a forum topic results in a query error has been marked as a duplicate of this.

I do have a PostgreSQL-backed Drupal instance and the patch in #11 fixed the "SELECT DISTINCT, ORDER BY" problems I had with Forum Access.

So, is this enough to move this issue to 'Reviewed & tested by the community'? (I'm assuming it's probably not considered appropriate for me to move my own patch to that status.)

Status:Needs review» Reviewed & tested by the community

Your assumption is correct.

Status:Reviewed & tested by the community» Fixed

Looks good and simple, committed.

Status:Fixed» Closed (fixed)

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

Issue tags:+PostgreSQL

Tagging