paginated query is not properly handling sort keys with None value
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
oslo.db |
Fix Released
|
Medium
|
Mehdi Abaakouk |
Bug Description
The intended use case for pagination (not sure if documented somewhere) is that with 'limit' and 'marker', users can control the starting point of each query and the number of records returned. In real life, the identity of a record is one of the primary keys for the db table, i.e. they are used to sort records, and to support pagination. This is the baseline for pagination to work properly.
However, in real use cases, there are requirements that the records are sorted by other columns which are not necessarily a primary key, so such a column could carry a value of None. When these columns are used for sorting, we will get problems when doing paginated query because all sort keys currently participate in the comparison.
For example, when we are sorting a table using the following parameters:
sort_keys = ['created_at', 'id'] # id is the primary key to ensure pagination always works
sort_dirs = ['desc-nullslast', 'asc']
If the 'created_at' column has a value of None, the following lines will break. The model_attr here could be the 'created_at', and the marker_value can be None, but None cannot be compared with a value using '<' or '>'.
167 if sort_dirs[
168 crit_attrs.
169 else:
170 crit_attrs.
So, in these cases, if the model_attr is nullable and table does contain None values, we will get exceptions here.
The solution proposed is to skip these comparisons if marker_values[i] is None. If we have a marker_value that is None, the that is the only column used for sorting (thus pagination), it is the user's fault. We cannot do reliable pagination with a nullable column only. If the sorting keys have a primary key in its combination to ensure unique/
Changed in oslo.db: | |
assignee: | nobody → Qiming Teng (tengqim) |
Changed in oslo.db: | |
status: | New → Confirmed |
importance: | Undecided → Medium |
This issue was fixed in the openstack/oslo.db 4.17.0 release.