Page results are incorrect for :desc ordering [Bug]

[robkane1]

Summary

First off - thanks for the work you have done with this library. Being honest, there have a been a lot of quirks during adoption and it has become a bit of a time sink to adopt Flop, but I am hopeful that the payback will be more than worth it.

I have to give up on this one though and defer. I have spent a couple of days working through quirks, but this has got me stumped.

My gut tells me I am probably just missing something important in the docs, but perhaps not...

I have a query which returns 7 items. If I use pagination with a sort order of :asc and a page size of 5, Page 1 has 5 items and page 2 has 2 items. It took me a long time to actually make that happen (due to bound joins with through tables for filters etc), but that's another story! 😁

This result is great - it is how I expect it to work.

However, if I set the order to :desc, page 1 has 2 items and page 2 has 5 items. This is not what I expect. It is almost like the pages and offsets are calculated and assigned before the ordering is applied.

I started with validate_and_run and have broken the query right now into its tiny parts to see if I can pinpoint where it breaks, but nothing makes any difference. I'm hoping you will tell me I am just being silly. There is always that duh moment with a new library :D

Steps to reproduce

1. Have yourself a flop

%Flop{
  after: nil,
  before: nil,
  first: nil,
  last: nil,
  limit: nil,
  offset: nil,
  order_by: [:updated_at],
  order_directions: [:desc],
  page: 1,
  page_size: 5,
  decoded_cursor: nil,
  filters: []
}

2. Run the query in any way you like.

It will produce something like this:

%Flop.Meta{
 backend: nil,
 current_offset: 0,
 current_page: 1,
 end_cursor: nil,
 next_offset: 5,
 next_page: 2,
 page_size: 5,
 previous_offset: nil,
 previous_page: nil,
 schema: ***,
 start_cursor: nil,
 total_count: 7,
 total_pages: 2,
 errors: [],
 flop: %Flop{
   after: nil,
   before: nil,
   first: nil,
   last: nil,
   limit: nil,
   offset: nil,
   order_by: [:updated_at],
   order_directions: [:desc],
   page: 1,
   page_size: 5,
   decoded_cursor: nil,
   filters: []
 },
 has_next_page?: true,
 has_previous_page?: false,
 opts: [for:***, count_query: ***],
 params: %{}
}

3. Check the number of items returned for that page. It will be 2, not 5.

Expected behaviour

Given: I have 7 results
And: I have a page size of 5
When: I query page 1
Then: Page 1 will have 5 items

Given: I have 7 results
And: I have a page size of 5
When: I query page 2
Then: Page 2 will have 2 items

Actual behaviour

Page 1 has 2 items and page 2 has 5 items

Elixir/Erlang version

1.16.2

Flop and Ecto versions

Ecto: 3.11.1
Flop: 0.25.0

Additional context

No response

[robkane1]

Sorry, should have mentioned that I have configured the pagination types on the schema already:

default_limit: 5,
default_pagination_type: :page,
pagination_types: [:page]

[woylie]

Hey @robkane1, I cannot reproduce this. Can you post your query? I suspect you might be joining on one-to-many or many-to-many associations and using preloads.

[robkane1]

I don;t think this is actually a flop problem. I just manually wrote the query with Ecto and it is producing the same result.

      q
      |> distinct(true)
      |> limit(5)
      |> offset(0)
      |> Repo.all()

It could be a quirk related to Ecto query caching, but I suspect not.

Feel free to close if you think this is not worth investigating

Edit: Just saw your reply. Thanks for looking and sorry for wasting your time!

[woylie]

I don't think Ecto has anything to do with this either. I'd run some raw SQL queries directly in the DB to figure this out. It's important to understand what is actually returned by the DB, compared to what Ecto returns.