Correctly handle NULLS FIRST | NULLS LAST in SEEK method

lukaseder commented 11 years ago

In #2657, the SEEK method was supported in jOOQ. The current implementation only works for NOT NULL columns. Support for NULL columns and NULLS FIRST, NULLS LAST is more complex and will be added later.

More details about the SEEK method can be seen here: http://use-the-index-luke.com/sql/partial-results/fetch-next-page

atollk commented 2 years ago

Just came to this issue from a search engine. Considering that this has been dormant for 8 years, it's probably safe to assume that this won't be implemented in the foreseeable future? Would this issue be considered small enough to accept public PRs?

lukaseder commented 2 years ago

Thanks for your message, @atollk. The issue being old doesn't have any meaning regarding its significance or status on the future roadmap. There are many flaws and open issues, and no strict reason why one flaw should require a more urgent fix than another.

Though the fact that there are 4 upvotes by now surely does change things. Let me reconsider this for ~jOOQ 3.17~ jOOQ 3.18 (?) (No promises 😅).

Regarding a PR, chances of it being correct or optimal are slim because you don't have access to integration tests (which aren't open source). If you're not licensed, then you can also not see the commercial code, which might require a different fix from that of the jOOQ Open Source Edition.

Though, if you're willing to spend time on analysing potential solutions, and make implementation suggestions, that might still be helpful (no promises). I can't say whether this is a "small enough" fix yet. It does seem to produce a few caveats, maybe even expose other flaws in the current SEEK implementation. Up to you whether you want to spend the time.

What would be helpful in any case is:

A few test cases, scenarios, showing what kinds of queries you're trying to write
A few workarounds showing how you implemented the query without SEEK (which is just syntax sugar, you can always work around SEEK limitations by writing the predicates manually)

atollk commented 2 years ago

Sorry for the late answer; I had to get the clear for publishing this code.

I tried to simplify it somewhat and remove our special cases, so this should be the general use case implementation:

import org.jooq.Condition
import org.jooq.Field
import org.jooq.Record
import org.jooq.SelectConditionStep
import org.jooq.SelectLimitStep
import org.jooq.SelectWhereStep
import org.jooq.SortField
import org.jooq.SortOrder
import org.jooq.impl.DSL
import org.jooq.impl.QOM

/**
 * A query ready to be paginated via a single attribute.
 */
internal class SelectPaginateStep1<R : Record, T1>(
    private val query: SelectConditionStep<R>,
    private val orderField1: SortField<T1>,
) {
    fun page(v1: T1): SelectLimitStep<R> =
        page(DSL.value(v1, orderField1.`$field`()))

    fun page(v1: Field<T1>): SelectLimitStep<R> {
        val orderedFields = listOf(orderField1)
        val condition = makeAfterPageCursorCondition(orderedFields, listOf(v1))
        return query.and(condition).orderBy(orderedFields)
    }
}

/**
 * A query ready to be paginated via two attributes.
 */
internal class SelectPaginateStep2<R : Record, T1, T2>(
    private val query: SelectConditionStep<R>,
    private val orderField1: SortField<T1>,
    private val orderField2: SortField<T2>,
) {
    fun page(v1: T1, v2: T2): SelectLimitStep<R> =
        page(
            DSL.value(v1, orderField1.`$field`()),
            DSL.value(v2, orderField2.`$field`()),
        )

    fun page(v1: Field<T1>, v2: Field<T2>): SelectLimitStep<R> {
        val orderedFields = listOf(orderField1, orderField2)
        val condition = makeAfterPageCursorCondition(orderedFields, listOf(v1, v2))
        return query.and(condition).orderBy(orderedFields)
    }
}

/**
 * A query ready to be paginated via three attributes.
 */
internal class SelectPaginateStep3<R : Record, T1, T2, T3>(
    private val query: SelectConditionStep<R>,
    private val orderField1: SortField<T1>,
    private val orderField2: SortField<T2>,
    private val orderField3: SortField<T3>,
) {
    fun page(v1: T1, v2: T2, v3: T3): SelectLimitStep<R> =
        page(
            DSL.value(v1, orderField1.`$field`()),
            DSL.value(v2, orderField2.`$field`()),
            DSL.value(v3, orderField3.`$field`()),
        )

    fun page(v1: Field<T1>, v2: Field<T2>, v3: Field<T3>): SelectLimitStep<R> {
        val orderedFields = listOf(orderField1, orderField2, orderField3)
        val condition = makeAfterPageCursorCondition(orderedFields, listOf(v1, v2, v3))
        return query.and(condition).orderBy(orderedFields)
    }
}

/**
 * Prepares a query to be paginated by specifying the attributes to filter.and(sort) by.
 * @param value1 The highest-priority attribute to sort.and(filter) by.
 * @return A new query object which can be used for pagination.
 */
internal fun <R : Record, T1> SelectConditionStep<R>.pageBy(
    value1: SortField<T1>,
): SelectPaginateStep1<R, T1> =
    SelectPaginateStep1(this, value1)

/**
 * Prepares a query to be paginated by specifying the attributes to filter.and(sort) by.
 * @param value1 The highest-priority attribute to sort.and(filter) by.
 * @param value2 The second-highest-priority attribute to sort.and(filter) by.
 * @return A new query object which can be used for pagination.
 */
internal fun <R : Record, T1, T2> SelectConditionStep<R>.pageBy(
    value1: SortField<T1>,
    value2: SortField<T2>,
): SelectPaginateStep2<R, T1, T2> =
    SelectPaginateStep2(this, value1, value2)

/**
 * Prepares a query to be paginated by specifying the attributes to filter.and(sort) by.
 * @param value1 The highest-priority attribute to sort.and(filter) by.
 * @param value2 The second-highest-priority attribute to sort.and(filter) by.
 * @param value3 The third-highest-priority attribute to sort.and(filter) by.
 * @return A new query object which can be used for pagination.
 */
internal fun <R : Record, T1, T2, T3> SelectConditionStep<R>.pageBy(
    value1: SortField<T1>,
    value2: SortField<T2>,
    value3: SortField<T3>,
): SelectPaginateStep3<R, T1, T2, T3> =
    SelectPaginateStep3(this, value1, value2, value3)

/** See [SelectConditionStep.pageBy]. */
internal fun <R : Record, T1> SelectWhereStep<R>.pageBy(
    value1: SortField<T1>,
): SelectPaginateStep1<R, T1> =
    this.where(DSL.trueCondition()).pageBy(value1)

/** See [SelectConditionStep.pageBy]. */
internal fun <R : Record, T1, T2> SelectWhereStep<R>.pageBy(
    value1: SortField<T1>,
    value2: SortField<T2>,
): SelectPaginateStep2<R, T1, T2> =
    this.where(DSL.trueCondition()).pageBy(value1, value2)

/** See [SelectConditionStep.pageBy]. */
internal fun <R : Record, T1, T2, T3> SelectWhereStep<R>.pageBy(
    value1: SortField<T1>,
    value2: SortField<T2>,
    value3: SortField<T3>,
): SelectPaginateStep3<R, T1, T2, T3> =
    this.where(DSL.trueCondition()).pageBy(value1, value2, value3)

private fun makeAfterPageCursorCondition(
    tableFields: Collection<SortField<*>>,
    cursorFields: Collection<Field<*>>,
): Condition {
    require(tableFields.size == cursorFields.size) { "[values] must have the same size as [orderedFields]. (is ${tableFields.size} != ${cursorFields.size})" }
    return (tableFields zip cursorFields).toList()
        .foldRight(
            DSL.falseCondition() as Condition
        ) { (tableSortField, cursorField), condition ->
            // Destructure fields.
            val tableField = tableSortField.`$field`().coerce(Any::class.java)
            val sortAsc = when (tableSortField.`$sortOrder`()) {
                SortOrder.ASC -> true
                SortOrder.DESC -> false
                SortOrder.DEFAULT -> throw UnsupportedOperationException("Default sort not supported by pagination.")
            }
            val nullsFirst = when (tableSortField.`$nullOrdering`()) {
                QOM.NullOrdering.NULLS_FIRST -> true
                QOM.NullOrdering.NULLS_LAST -> false
                null -> !sortAsc
            }

            // Is the table row equal to the cursor (just considering this current field).
            val tableEqualsCursor = (tableField.isNull.and(cursorField.isNull)).or(tableField.eq(cursorField))

            // Is the table row placed after the cursor in order (just considering this current field).
            val tableAfterCursorIfNotNull =
                if (sortAsc) tableField.gt(cursorField) else tableField.lt(cursorField)
            val tableAfterCursor =
                if (nullsFirst)
                    tableField.isNotNull.and((cursorField.isNull).or(tableAfterCursorIfNotNull))
                else
                    cursorField.isNotNull.and((tableField.isNull).or(tableAfterCursorIfNotNull))

            (condition.and(tableEqualsCursor)).or(tableAfterCursor)
        }
}

It's Kotlin though :)

A usage example would be:

jooqDsl.selectFrom(USERS)
    .where(USERS.USERNAME.eq("foobar"))
    .pageBy(USERS.FIRST_NAME.asc(), USERS.LAST_NAME.asc(), USERS.ID.desc())
    .page("Max", "Mustermann", "b24943e5-028c-439d-8389-0ed90aa86d74")
    .limit(10)

lukaseder commented 2 years ago

Thanks for documenting your workaround. This might be useful for others...

SParakhin commented 2 months ago

It's Kotlin though :)

A usage example would be:

jooqDsl.selectFrom(USERS)
    .where(USERS.USERNAME.eq("foobar"))
    .pageBy(USERS.FIRST_NAME.asc(), USERS.LAST_NAME.asc(), USERS.ID.desc())
    .page("Max", "Mustermann", "b24943e5-028c-439d-8389-0ed90aa86d74")
    .limit(10)

Do you have a similar solution in Java or SQL ?

nucle commented 2 months ago

@SParakhin second post is interesting: https://ask.use-the-index-luke.com/questions/225/pagination-with-nulls

It's easy to create the JOOQ condition.

SParakhin commented 2 months ago

I still don't understand how it works. I have 3 fields – name, created_date, id. Created_date and ID fields have a default sorting of desk. They are used as unique keys in cursor pagination. The “name” field can have both desk and asc sorting and it may not always be filled in (null). The seekAfter() method generates such a sql code when requesting the next page:

Where*** and (
 "base".name > 'ААА'
 or (
 "base".name = 'ААА'
 and "base".created_date < '2024-06-24T09:18:58.333185Z'
 )
 or (
 "base".name = 'ААА'
 and "base".created_date = '2024-06-24T09:18:58.333185Z'
 and "base".id < '8633c6fd-0df6-434e-ba45-26ea8635155a'
 )
 )
)
order by
("base".name)::varchar asc,
"base".created_date desc,
"base".id desc

However, I do not receive records in which name=null. But if I add the or ("base".name is null) condition, then the selection is formed correctly. Is there any way to add an additional condition to the sql code that the seekAfter() method generates? I use postgresql.

lukaseder commented 2 months ago

@SParakhin This bug here describes the fact that SEEK isn't capable of handling NULL values correctly. It's this very bug. There isn't a way around it, it's a known issue, thus the bug.

Of course, you don't have to use SEEK. SEEK is just syntax sugar for this predicate that you can also just write yourself. I hope this helps.

nucle commented 2 months ago

Hi! @SParakhin below you can find my code. At the moment this code works only for two fields. But it should be easy to add another field, I don't know if I have all the cases, but I tested it, and it worked for me.

The last element must be an non null field.

// Case with non null
 conditions.add(page("abd@test.com", TICKET.EMAIL.asc().nullsFirst(), "202403ABCDE", TICKET.ID));
// Case with null
 conditions.add(page(null, TICKET.EMAIL.asc().nullsFirst(), "202403ABCDE", TICKET.ID));

    private <T1, T2> Condition page(T1 value, SortField<T1> tableField, @NotNull T2 nonNullValue, Field<T2> nonNullField) {
        assert nonNullValue != null;
        var sortAsc = switch (tableField.$sortOrder()) {
            case ASC -> true;
            case DESC -> false;
            case DEFAULT -> throw new UnsupportedOperationException("Sort order must be of type ASC or DESC");
        };
        var ordering = (tableField.$nullOrdering() == null ?
                NULLS_FIRST :
                tableField.$nullOrdering()
        );
        var nullsFirst = switch (ordering) {
            case NULLS_FIRST -> true;
            case NULLS_LAST -> false;
        };

        var field = tableField.$field();
        Condition condition;
        if (value == null) {
            var nullCondition = (sortAsc ?
                    field.isNull().and(nonNullField.gt(nonNullValue)) :
                    field.isNull().and(nonNullField.lt(nonNullValue))
            );
            if(nullsFirst) {
                condition = noCondition().and(nullCondition).or(field.isNotNull());
            } else {
                condition = noCondition().and(nullCondition);
            }
        } else {
            var rowCondition = (sortAsc ?
                    row(field, nonNullField).gt(value, nonNullValue) :
                    row(field, nonNullField).lt(value, nonNullValue)
            );
            if (nullsFirst) {
                condition = (noCondition().and(rowCondition));
            } else {
                condition = (noCondition().and(rowCondition).or(field.isNull()));
            }
        }
        return condition;
    }

At the end of your query you have to add an order by like this:

.orderBy(TICKET.EMAIL.asc().nullsFirst(), TICKET.TICKET_ID.asc().nullsFirst());

Should I have an issue or miss something let me know.

@lukaseder can I improve something?

jOOQ / jOOQ

Correctly handle NULLS FIRST | NULLS LAST in SEEK method #2786