Jump to:
| Project: | Drupal core |
| Version: | 6.16 |
| Component: | taxonomy.module |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed (duplicate) |
Issue Summary
On main page, before any user logs in, one can see several warnings like:
# warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /var/www/drupal-6.16/includes/database.pgsql.inc on line 139.
# user warning: query: SELECT DISTINCT t.* FROM drupal_term_node r INNER JOIN drupal_term_data t ON r.tid = t.tid INNER JOIN drupal_vocabulary v ON t.vid = v.vid LEFT JOIN drupal_forum_access fa ON t.tid = fa.tid LEFT JOIN drupal_acl acl_fa ON acl_fa.name = CAST(t.tid AS VARCHAR) AND acl_fa.module = 'forum_access' LEFT JOIN drupal_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 = 578 )ORDER BY v.weight, t.weight, t.name in /var/www/drupal-6.16/modules/taxonomy/taxonomy.module on line 617.
This happens on several different queries and modules, not just Taxonomy (also on "Image" for instance). The server is a Debian Squeeze/sid running apache 2.2.14-7 and php 5.3.1-5.
Comments
#1
An important detail I forgot: DBMS is PostgreSQL 8.3. Maybe the SQL is valid for MySQL, that's being used on most installations, and hasn't been tested on Postgres?
#2
similar problem also on comment module line 314 in 6.15
That's because PostgreSQL implement strictly SELECT clause with ORDER BY.
A solution is simple. We need insert a column described in ORDER BY into SELET list.
see postgresql manual for select list.
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-SELECT-LIST
for example,
--- comment.module.orig 2010-03-14 23:17:22.000000000 +0900
+++ comment.module 2010-03-14 23:17:34.000000000 +0900
@@ -311,7 +311,7 @@
// Select the $number nodes (visible to the current user) with the most
// recent comments. This is efficient due to the index on
// last_comment_timestamp.
- $result = db_query_range(db_rewrite_sql("SELECT nc.nid FROM {node_comment_sta
tistics} nc WHERE nc.comment_count > 0 ORDER BY nc.last_comment_timestamp DESC",
'nc'), 0, $number);
+ $result = db_query_range(db_rewrite_sql("SELECT nc.nid,nc.last_comment_timest
amp FROM {node_comment_statistics} nc WHERE nc.comment_count > 0 ORDER BY nc.las
t_comment_timestamp DESC", 'nc'), 0, $number);
$nids = array();
while ($row = db_fetch_object($result)) {
It is no side effect because after this code block, it retrieves only $row->nid from results.
from the issue, proposal of fix is that
original:
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];
}
changes
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 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];
}
That's mean remove v.weight from ORDER BY.
It looks no bad side effect because the results will set into $term array indexed by vid and key.
#3
Thank you miurahr for looking into the problem. I confirm removing v.weight from the ORDER BY list resolves the issue, since all other ordering fields are present in the SELECT list ("select t.* ..."). I'm not sure if there can be any particular situation where ordering by v.weight first may be meaningful... On my particular case it seems to be ok now.
#4
What ordering by v.weight does: if you have terms from several vocabularies attached to the node, the terms are always grouped per vocabulary. This seems nice to keep.
I tried solving this in a different way, which should catch all the errors. Patch is in comment #1 of #735120: Fix changes to db_distinct_field() in D6.16 / PostgreSQL.
No idea if that will get committed,assume that that issue will yield a 'proper' (as proper as possible) solution, in the end.