PostgreSQL DISTINCT ORDER BY CLAUSE causes break during installation
jmpoure - October 17, 2008 - 20:27
| Project: | Translation Overview |
| Version: | 6.x-2.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Description
In PostgreSQL and probably SQL99, you can only ORDER BY flields in a SELECT DISTINCT clause if the fields are part of the SELECT DISTINCT clause.
Example:
SELECT DISTINCT (nid) FROM node
WHERE nid = tnid OR tnid = 0 OR tnid IS NULL
ORDER BY tnidCorrect would be:
SELECT DISTINCT (nid, tnid) FROM node
WHERE nid = tnid OR tnid = 0 OR tnid IS NULL
ORDER BY tnidOf course, this does work during the installation.
Because data is inserted, there is no need to ORDER BY.
Therefore,
INSERT INTO translation_overview_priority (tnid)
SELECT DISTINCT (nid) FROM node WHERE nid = tnid OR tnid = 0 OR tnid IS NULL ORDER BY tnidwhould be replace with:
INSERT INTO translation_overview_priority (tnid)
SELECT DISTINCT (nid) FROM node WHERE nid = tnid OR tnid = 0 OR tnid IS NULL Cheers,
Jean-Michel
#1
good call, the order isn't needed, i'd copied and pasted the code from elsewhere and didn't clean that up. would you mind testing this and ensuring that it works?
#2
simple enough change so i went ahead and committed it. thanks!
#3
Automatically closed -- issue fixed for two weeks with no activity.