roschaefer
commented
6 years ago Ok, this bug is caused by a combination of our custom where_station scope and kaminari's pagination.
It only appears when you filter for stations and it's really hard to reproduce in testing/development environment. On the production database @broadcasts.to_sql in the controller gives us this SQL:
1] pry(#<BroadcastsController>)> @broadcasts.to_sql
SQL (53.8ms) SELECT DISTINCT "broadcasts"."id", RANDOM() AS alias_0 FROM "broadcasts" LEFT OUTER JOIN "impressions" ON "impressions"."broadcast_id" = "broadcasts"."id" INNER JOIN "schedules" "schedule_table_alias" ON "broadcasts"."id" = "schedule_table_alias"."broadcast_id" WHERE "broadcasts"."medium_id" = 0 AND "schedule_table_alias"."station_id" = $1 ORDER BY RANDOM() LIMIT $2 OFFSET $3 [["station_id", "36"], ["LIMIT", 6], ["OFFSET", 0]]
=> "SELECT \"broadcasts\".\"id\" AS t0_r0, \"broadcasts\".\"title\" AS t0_r1, \"broadcasts\".\"description\" AS t0_r2, \"broadcasts\".\"topic_id\" AS t0_r3, \"broadcasts\".\"format_id\" AS t0_r4, \"broadcasts\".\"created_at\" AS t0_r5, \"broadcasts\".\"updated_at\" AS t0_r6, \"broadcasts\".\"creator_id\" AS t0_r7, \"broadcasts\".\"mediathek_identification\" AS t0_r8, \"broadcasts\".\"medium_id\" AS t0_r9, \"broadcasts\".\"schedule_id\" AS t0_r10, \"broadcasts\".\"image_url\" AS t0_r11, \"impressions\".\"id\" AS t1_r0, \"impressions\".\"response\" AS t1_r1, \"impressions\".\"amount\" AS t1_r2, \"impressions\".\"user_id\" AS t1_r3, \"impressions\".\"broadcast_id\" AS t1_r4, \"impressions\".\"created_at\" AS t1_r5, \"impressions\".\"updated_at\" AS t1_r6, \"impressions\".\"fixed\" AS t1_r7 FROM \"broadcasts\" LEFT OUTER JOIN \"impressions\" ON \"impressions\".\"broadcast_id\" = \"broadcasts\".\"id\" INNER JOIN \"schedules\" \"schedule_table_alias\" ON \"broadcasts\".\"id\" = \"schedule_table_alias\".\"broadcast_id\" WHERE \"broadcasts\".\"medium_id\" = 0 AND \"schedule_table_alias\".\"station_id\" = '36' AND \"broadcasts\".\"id\"
IN (99, 457, 444, 457, 427, 99) ORDER BY RANDOM()"Can you see the duplicate 457 id in the IN statement? I think that's the problem. Something weird happens when we use our own where_station scope with multiple joins of the same table and then do pagination on top of that.
I'm submitting a ...
What is the current behavior? When I visit https://rundfunk-mitbestimmen.de/find-broadcasts?medium=0&q=&station=36 it says there are 12 broadcasts for TV station "BR Fernsehen". When I click on "Next", some broadcasts appear twice and at least "Startrampe" never appears.
What is the expected behavior? All broadcasts should appear and they should appear only once.
What is the motivation / use case for changing the behavior? I don't want to miss a broadcast!
If the current behavior is a bug, please tell us (otherwise delete):
Steps to reproduce
The version of your browser, local postgresql installation and operating system Chromium Version 61.0.3163.100, Postgresql 9.6.5., Archlinux
A screenshot
Stacktraces, related issues, suggestions how to fix
What could be a possible solution? (not obligatory) I'm pretty sure, we forgot a default order in the broadcasts controller. In that case, the database randomly puts records in the result set.