Maybe build query into DB error with limit and offset
thachtan opened this issue · 6 comments
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)
Ecto.Query has limit anf offset functions.
Where is offset in your code?
example
from(p in Post, limit: 10, offset: 30)
... 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.
It looks good.
jamdb_oracle/test/jamdb_oracle_test.exs
Line 459 in d39753d
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}