Merge ~cjwatson/launchpad:simplify-external-translation-messages into launchpad:master

It's trying to get the most recent TranslationMessage with each distinct set of translations, not sort the output as such, so this change is likely to cause the result of the subquery and parent query to become orders of magnitude larger. Have you considered that?

A realisation of the query in question (from https://oops.canonical.com/oops/?oopsid=OOPS-6ce2e41d4a1fc3371ecbbfa31fafcc74) is:

WITH msgsets AS
  ( SELECT POTMsgSet.id
   FROM POTMsgSet
   JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
   JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
   WHERE POTMsgSet.msgid_singular = 10087
     AND POTMsgSet.id <> 29306498 )
SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    ( SELECT DISTINCT ON (COALESCE(msgstr0, -1),
                          COALESCE(msgstr1, -1),
                          COALESCE(msgstr2, -1),
                          COALESCE(msgstr3, -1),
                          COALESCE(msgstr4, -1),
                          COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     JOIN msgsets ON msgsets.id = TranslationMessage.potmsgset
     WHERE (TranslationMessage.LANGUAGE IN (129))
     ORDER BY COALESCE(msgstr0, -1),
              COALESCE(msgstr1, -1),
              COALESCE(msgstr2, -1),
              COALESCE(msgstr3, -1),
              COALESCE(msgstr4, -1),
              COALESCE(msgstr5, -1),
              date_created DESC ) LIMIT 2001;

I'm struggling to see how that fetches the most recent TranslationMessage; as far as I can see the parent query just does a set membership check on a subquery that returns all the associated TranslationMessages with some sorting that doesn't seem as though it can change the end result. What am I missing?

The subquery's DISTINCT ON combined with its ORDER BY mean only some of the TranslationMessages will be returned, don't they?