Surprising postgres behaviour when paginating with LIMIT and OFFSET

explanation

total_points is a not unique field of player. Here its value is 0 for every player

mysql.rb

50.times { Player.create }
Player.all.map(&:id)
#=> [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50]

50.times.to_a.map{|i| Player.order("total_points asc").limit(1).offset(i).first.id}
#=> [50, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 37, 38, 39, 49, 48, 47, 46, 45, 44, 43, 42, 41, 40, 26, 25, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 12, 13, 14, 24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 1]

50.times.to_a.map{|i| Player.order("total_points asc, id asc").limit(1).offset(i).first.id}
#=> [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50]

postgres.rb

50.times { Player.create }
Player.all.map(&:id)
#=> [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50]

50.times.to_a.map{|i| Player.order("total_points asc").limit(1).offset(i).first.id}
#=> [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50]

50.times.to_a.map{|i| Player.order("total_points asc, id asc").limit(1).offset(i).first.id}
#=> [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50]

Explanations here and here