Duplicated tickets in the theme final review queue

[chetan200891]

In the theme final review queue there are showing duplicate tickets. For example THEME: veayo – 1.1.3 modified date should not be 2017-12-07 it should be 2017-12-24. It showing two times. ​https://themes.trac.wordpress.org/report/24 I have attached screenshot.

[Otto42]

That theme shows up twice because it was approved twice. The approval was changed to reppened, and then it was approved again.

Regardless, the theme being set to live will remove it from both spots in that list, so it's not really an issue that it appears twice.

[SergeyBiryukov]

Replying to Otto42:

That theme shows up twice because it was approved twice. The approval was changed to reppened, and then it was approved again.

Could we adjust the query to add DISTINCT (or something to that effect) to prevent future confusion?

[Otto42]

Replying to SergeyBiryukov:

Could we adjust the query to add DISTINCT (or something to that effect) to prevent future confusion?

If you'd like to give it a try, go ahead. I'm not sure that that query is used enough for DISTINCT to add any sort of performance problem, but I'm unfamiliar with Trac quirks and SQL-Lite oddities.

This is the query for that report:

SELECT
   id AS ticket,
   summary, 
   reporter AS submitter, 
   owner AS reviewer,
   tc.tc_time AS modified,
   t.keywords AS keywords

FROM ticket t   
LEFT JOIN 
(SELECT ticket, time as tc_time FROM ticket_change WHERE field = 'status' AND newvalue = 'approved') tc
ON (t.id = tc.ticket)
WHERE status = 'approved'
ORDER BY tc.tc_time

[dd32]

Adding DISTINCT on this query isn't going to be an issue performance wise, however it wouldn't have actually worked in this case as the modified time would be unique.

I've experimented with some GROUP BY options instead

• Putting it in the subquery tends to slow it down by ~0.5-1s which isn't great, and is because the result set is rather large (It fetches every approval in the history of this Trac)
• Putting it in the outer query seems to work okay, as long as it's grouping on ticket.id rather than tc.ticket

The query on that report now looks like this:

SELECT
   id AS ticket,
   summary, 
   reporter AS submitter, 
   owner AS reviewer,
   tc.tc_time AS modified,
   t.keywords AS keywords

FROM ticket t   
LEFT JOIN (
      SELECT ticket, time as tc_time FROM ticket_change WHERE field = 'status' AND newvalue = 'approved'
   ) tc
ON (t.id = tc.ticket)
WHERE status = 'approved'
GROUP BY id
ORDER BY tc.tc_time

I'm going to close this as fixed, if anyone spots it happen again or performance issues, just revert it to the version in Comment #3 and mark this as wontfix :)