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.

Comments

drewish’s picture

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?

ekes’s picture

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.

drewish’s picture

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?

ekes’s picture

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.

drewish’s picture

Status: Needs review » Active
StatusFileSize
new763 bytes

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.

drewish’s picture

Status: Active » Fixed

Seems like it's safe to close this now.

Status: Fixed » Closed (fixed)

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