PostgreSQL error on DISTINCT query
| Project: | Active Translation |
| Version: | 6.x-1.3 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
The code that (re)populates the active_translation table, executes the following query (with an arbitrary number of nids):
SELECT DISTINCT(nid), nid, nid FROM node WHERE tnid = 0 OR tnid IS NULL ORDER BY tnid
PostgreSQL cannot work with this combination of DISTINCT and ORDER BY.
(The exact error is: 'column "node.tnid" must appear in the GROUP BY clause or be used in an aggregate function')
This patch fixes the problem by just getting rid of the DISTINCT 'modifier'. After all, it should not be necessary on the table 'node' because node.nid is its primary key => has unique values.
(IF node.nid were not unique, the following query would yield the same result and work on all SQL92 compliant databases:
SELECT nid, nid, nid FROM node WHERE tnid = 0 OR tnid IS NULL GROUP BY nid ORDER BY Min(tnid)
...but that is not an issue.)
A remark: since this query is only fired once after enabling the module or changing form settings, I don't think performance issues are a huge factor here... (Not that I think the query with DISTINCT runs faster, I don't know that.)
Remark #2: the patch is applied to a source file where http://drupal.org/node/499972 was already applied.
| Attachment | Size |
|---|---|
| active_translation-distinct.patch | 765 bytes |
#1
Humm... that seems like a reasonable change but now I'm a little paranoid wondering why I put that there in the first place. You've been using this patch for a couple weeks now, noticed any issues?
#2
Agree that the DISTINCT nid doesn't seem to make much sense, it's possibly copied from the tnid example? Is this not also the same for the ORDER BY? It doesn't seem to make much difference which order it is done as there is no batching for this bit of the function.
#3
yeah, wtf is the order by doing there? had to be copy pasted from some place. maybe the safest thing would be to just drop the order by. thoughts?
#4
For a starter just removing the ORDER BY should solve the issue described - and be safe. I'm still trying to think of an occasion where the DISTINCT for the nid here would be needed either, however.
#5
okay, committed the attached to HEAD and DRUPAL-6--1 since it was breaking pgsql. leaving this open for a possible followup on the DISTINCT.