Skip to content

Fully qualify id columns for keyset pagination

Andreas Brandl requested to merge ab/keyset-ambig-bug into master

What does this MR do?

This fixes a bug that pops up when combining id_before or id_after filters on the Projects API with other filters that join a relation onto projects.

An example in the %12.9 release used to be the search=? parameter which joined the routes table. This led to queries like this:

SELECT ...
FROM projects
JOIN routes ON ...
WHERE id > ? AND /* search condition...*/

This in turn leads to an ambiguous column error for id.

We fix this here by qualifying the column fully: projects.id > ? and projects.id < ?.

Note that the combination of id_before/id_after and search is not problematic in >12.9 anymore, since the search implementation changed (and does not require a join anymore). However, it's worth fixing that in general of course and the regression spec is quite general, too, for that reason.

Backports: This should go back to %12.9 and %12.8.

Docs: https://docs.gitlab.com/ee/development/sql.html#reliably-referencing-database-columns

Does this MR meet the acceptance criteria?

Conformity

Edited by Yorick Peterse

Merge request reports