Maybe build query into DB error with limit and offset

[thachtan]

Hi team,

I have unexpected result when I call query with limit offset. Particularly, I can not get true limit, and offset not match too.

I have built repo to check .

Example:

When i call function get User with limit 5, page 5 (limit 5, offset 20) and I get 20 user from current offset 5 (I think it may be swap when build)

Result when I call.

[vstavskyi]

Ecto.Query has limit anf offset functions. Where is offset in your code? example from(p in Post, limit: 10, offset: 30)

[thachtan]

You can check in module UserService and list_users function.

I put offset in criteria via page when build query.

[vstavskyi]

... OFFSET :2 ... NEXT :1 ... Ecto.Query.Builder generates sql in that order. :( Well, sadly, you need to use constant value for offset or limit for now.

There is no test cases, btw, for postgres or mysql with parameters for offset and limit, they test constant values only.

[thachtan]

I used limit and offset function to build dynamic query.

Can you show me example to change them?

[vstavskyi]

It looks good.

https://github.com/erlangbureau/jamdb_oracle/blob/d39753d331aacaea8d5df2cb2ecfef0db702f053/test/jamdb_oracle_test.exs#L459

    query = Schema |> offset([r], ^5) |> limit([r], ^3) |> select([], true) |> plan()
    assert all(query) == ~s{SELECT 1 FROM schema s0 OFFSET :2 ROWS FETCH NEXT :1 ROWS ONLY}

    query = Schema |> offset([r], 5) |> limit([r], ^3) |> select([], true) |> plan()
    assert all(query) == ~s{SELECT 1 FROM schema s0 OFFSET 5 ROWS FETCH NEXT :1 ROWS ONLY}

    query = Schema |> offset([r], ^5) |> limit([r], 3) |> select([], true) |> plan()
    assert all(query) == ~s{SELECT 1 FROM schema s0 OFFSET :1 ROWS FETCH NEXT 3 ROWS ONLY}

[thachtan]

Thank you for help. I hope I can use dynamic soon.

Now I swap limit, offset to work around ^^