Pagination not correct when sorting by non-unique field

[jaypipes]

If the sort is on a field that is not unique, such as name, then the marker part of the query isn't correct. Example here:

jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list
+----------------------------------+----------------+-----------------------+-----------+
|               UUID               |  DISPLAY NAME  |         SLUG          |  PARENT   |
+----------------------------------+----------------+-----------------------+-----------+
| 185b5a3b635b4e318675ab3e17089aa8 | X-Men          | heroes-x-men          | Heroes    |
| 40e24a55705d4ae78c78e670f617356e | Kubernetes     | kubernetes            |           |
| 7db1db6546384b868b5774d839db171e | Cartoons       | cartoons              |           |
| a25fe6fec11b4815b0dbd884fd9eb021 | Heroes         | heroes                |           |
| a9c124c4a1d94719bf974147a289a9a1 | OpenStack      | openstack             |           |
| b40af52744fa44eabb0f22400deea873 | Justice League | heroes-justice-league | Heroes    |
| cde7cfc170e242b8b6de1a9246269dba | Heroes         | openstack-heroes      | OpenStack |
| fe1a9ffdd2944ee992da7b86c063cf43 | Villains       | villains              |           |
+----------------------------------+----------------+-----------------------+-----------+
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list --limit 2
+----------------------------------+--------------+--------------+--------+
|               UUID               | DISPLAY NAME |     SLUG     | PARENT |
+----------------------------------+--------------+--------------+--------+
| 185b5a3b635b4e318675ab3e17089aa8 | X-Men        | heroes-x-men | Heroes |
| 40e24a55705d4ae78c78e670f617356e | Kubernetes   | kubernetes   |        |
+----------------------------------+--------------+--------------+--------+
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list --limit 2 --sort name
+----------------------------------+--------------+----------+--------+
|               UUID               | DISPLAY NAME |   SLUG   | PARENT |
+----------------------------------+--------------+----------+--------+
| 7db1db6546384b868b5774d839db171e | Cartoons     | cartoons |        |
| a25fe6fec11b4815b0dbd884fd9eb021 | Heroes       | heroes   |        |
+----------------------------------+--------------+----------+--------+
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list --limit 2 --sort name:desc
+----------------------------------+--------------+--------------+--------+
|               UUID               | DISPLAY NAME |     SLUG     | PARENT |
+----------------------------------+--------------+--------------+--------+
| 185b5a3b635b4e318675ab3e17089aa8 | X-Men        | heroes-x-men | Heroes |
| fe1a9ffdd2944ee992da7b86c063cf43 | Villains     | villains     |        |
+----------------------------------+--------------+--------------+--------+
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list --limit 2 --sort name:desc --marker ^C
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ^C
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list --limit 2 --sort name --marker a25fe6fec11b4815b0dbd884fd9eb021
+----------------------------------+----------------+-----------------------+-----------+
|               UUID               |  DISPLAY NAME  |         SLUG          |  PARENT   |
+----------------------------------+----------------+-----------------------+-----------+
| cde7cfc170e242b8b6de1a9246269dba | Heroes         | openstack-heroes      | OpenStack |
| b40af52744fa44eabb0f22400deea873 | Justice League | heroes-justice-league | Heroes    |
+----------------------------------+----------------+-----------------------+-----------+
jaypipes@uberbox:~/src/github.com/jaypipes/procession$ ./build/bin/p7n organization list --limit 2 --sort name --marker b40af52744fa44eabb0f22400deea873
+----------------------------------+--------------+------------------+-----------+
|               UUID               | DISPLAY NAME |       SLUG       |  PARENT   |
+----------------------------------+--------------+------------------+-----------+
| cde7cfc170e242b8b6de1a9246269dba | Heroes       | openstack-heroes | OpenStack |
| fe1a9ffdd2944ee992da7b86c063cf43 | Villains     | villains         |           |
+----------------------------------+--------------+------------------+-----------+

The reason is because we're not looking up the value of the sort field for the record identified by the marker. Instead, we're always adding a WHERE uuid > $MARKER clause. Instead, we need to add a WHERE $SORTFIELD > $MARKER_RECORD_VALUE_FOR_SORTFIELD expression.

[jaypipes]

The resulting WHERE expression for sorts on non-unique fields also needs to include the UUID to determine ties, and the ORDER BY clause needs to include a secondary order on uuid.