Fix epics search query
What does this MR do?
Fix epics search query when :attempt_group_search_optimizations
is enabled.
We have to use the search CTE by last in the query otherwise it tries to group by a column that is not included in the select.
Old query
WITH "epics" AS (
(
SELECT "epics".*
FROM "epics"
WHERE "epics"."group_id" = 9979)
)
SELECT "epics".*
FROM epics
INNER JOIN "label_links"
ON "label_links"."target_id" = "epics"."id"
AND "label_links"."target_type" = 'Epic'
INNER JOIN "labels"
ON "labels"."id" = "label_links"."label_id"
WHERE (
"epics"."title" ilike '%python%'
OR "epics"."description" ilike '%python%')
AND "epics"."state_id" = 1
AND "labels"."title" IN ('group::portfolio management',
'devops::plan')
GROUP BY "epics"."id"
HAVING (
count(DISTINCT labels.title) = 2)
ORDER BY "epics"."updated_at" DESC,
"epics"."id" DESC limit 20 offset 0
New query
WITH "epics" AS (
(
SELECT "epics".*
FROM "epics"
INNER JOIN "label_links"
ON "label_links"."target_id" = "epics"."id"
AND "label_links"."target_type" = 'Epic'
INNER JOIN "labels"
ON "labels"."id" = "label_links"."label_id"
WHERE "epics"."group_id" = 3
AND "labels"."title" IN ('label1',
'label2')
GROUP BY "epics"."id"
HAVING (
Count(DISTINCT labels.title) = 2)))
SELECT "epics".*
FROM epics
WHERE (
"epics"."title" ilike '%filtered%'
OR "epics"."description" ilike '%filtered%')
ORDER BY "epics"."id" DESC
- We can ignore the changelog since this change effect is behind a feature flag.
related to #198156 (closed)