null values get dropped from results

[brianmay]

The SQL queries generated will silently drop null values, e.g: sort_order = '' and sort_order > '' will both exclude null values. So I guess this should currently only be used on not-nullable columns.

At least for string values. My guess is that other types will be affected as well.

[brianmay]

I don't think I have done a good job explaining. In order to get page two of the results, I get this query:

SELECT s0."id", s0."title", s0."sort_name", s0."sort_order", s0."parent_id" FROM "spud_album" AS s0 WHERE (s0."parent_id" = 1) AND (((s0."sort_order" = '') AND ((s0."sort_name" = '') AND ((s0."id" > 999) AND true))) OR (((s0."sort_name" = '') AND ((s0."sort_order" > '') AND true)) OR (((s0."sort_name" > '') AND true) AND true))) ORDER BY s0."sort_name", s0."sort_order", s0."id" LIMIT 11

This is incorrectly returning 0 results, when it should return 1 result - an entry that has sort_name and sort_order both set to NULL.

Apparently under postgres, NULL values get sorted last. But do not match "sort_order" > ''. Which is a little bit annoying. Possible changing "x" > "y" with "x" > "y" or "x" is null might help where y is not already null.

[andreagrossetti]

I think you refer to this issue:
You have a table "books" in your db and use cursor_fields: [:description].
Books with no description will be ignored by paginate.

To solve this simply add another field like [:description, :id]

[sgerrand]

Andrea, thanks for that explanation.

@brianmay, does that resolve your query?

[brianmay]

In the above case, the fields used where [:sort_name, :sort_order, :id] where :id is non-nullable. So, no, this doesn't help.

[dgvncsz0f]

I have just submitted a PR that potentially fix this issue 🤞🏽