get_adjacent_post() SQL changes can loop infinitely in Storefront/derivative theme code

cc @ramonopoly

Thanks for the heads up, and for the detailed explanation.

Do folks have any recommendations for making the changes in ​https://github.com/WordPress/wordpress-develop/pull/10394/files more defensive/plugin friendly?

Wondering if we could separate the date comparison from the ID tiebreaker and add filters for each. I really don't know.

<?php
// Build the date comparison
$date_comparison = $wpdb->prepare( ... );

// Build the ID tiebreaker for posts with identical dates
$id_tiebreaker = $wpdb->prepare(... );

// Combine into WHERE clause
$where_prepared = "WHERE ($date_comparison OR $id_tiebreaker) AND p.post_type = " . 
    $wpdb->prepare("%s", $post->post_type) . " $where";

/**
 * Filters the date comparison in the SQL for an adjacent post query.
 *
 * @param string  $date_comparison The date comparison clause.
 * @param string  $current_post_date Current post's date.
 * @param bool    $previous Whether querying for previous post.
 * @param WP_Post $post WP_Post object.
 */
$date_comparison = apply_filters(
    "get_{$adjacent}_post_date_comparison",
    $date_comparison,
    $current_post_date,
    $previous,
    $post
);

// Rebuild WHERE with filtered date comparison
$where_prepared = "WHERE ($date_comparison OR $id_tiebreaker) AND p.post_type = " . 
    $wpdb->prepare("%s", $post->post_type) . " $where";

// Apply the existing WHERE filter (now safer for plugins to use)
$where = apply_filters( "get_{$adjacent}_post_where", $where_prepared, ... )

Also add a note to update documentation for the get_{$adjacent}_post_where filter

Also, would this be considered a "regression" to the extent that it should be rolled back?

Or is there scope to mitigate (example above) and move on?

Not sure if fixing that bug is worth it. I mean, I think it is. It was pretty obvious and gnarly for end users. I guess there's a balance to be struck here.

Setting milestone to 6.9.1 for visibility, though I know this may end up getting closed as wontfix since there may be no core change required.

Dev note: ​Adjacent Post Navigation Changes in WordPress 6.9 and Compatibility Issues

Replying to ramonopoly:

Do folks have any recommendations for making the changes in ​https://github.com/WordPress/wordpress-develop/pull/10394/files more defensive/plugin friendly?

Seems there are couple things that can be done:

1. What if we move the logic from this ​new block of code after the get_{$adjacent}_post_where filter? Same for ​the change to the $sort in get_{$adjacent}_post_sort. I know it's not great to edit/tweak prepared SQL, but both of these filters expect plugins to do just that. (Ideally these filters should be deprecated, and replaced with something better. Hard on back-compat though.)

2. As above, move the changes after the filters. But instead of tweaking the prepared SQL, check to see if a plugin has changed the defaults and leave it alone if yes. If not, redo the SQL with adding the post ID. This would be most backwards compatible but might miss some cases where a plugin would supply (old style) SQL that still has problems. That of course can be mitigated by (loudly) telling plugins authors about it :)

move the changes after the filters. But instead of tweaking the prepared SQL, check to see if a plugin has changed the defaults and leave it alone

This sounds very reasonable, I'm grateful for the input.

I can get a patch up to demonstrate this for folks' consideration.

That of course can be mitigated by (loudly) telling plugins authors about it :)

I've definitely made note of this for future reference 😂

Follow up to ​https://github.com/WordPress/wordpress-develop/pull/10394

Tests out an idea suggested in https://core.trac.wordpress.org/ticket/64390#comment:10 Props @azaozz

## Description

This PR is a follow-up to ​#10394 that makes the deterministic ordering changes more defensive and plugin-friendly. It ensures that when plugins modify the get_{$adjacent}_post_where or get_{$adjacent}_post_sort filters, the deterministic logic is not applied on top of their modifications.

### Problem

In PR #10394, deterministic ID-based ordering was added to fix adjacent post navigation for posts with identical dates. However, the implementation applied the deterministic logic before the filters ran, which meant:

1. Plugins modifying the WHERE or SORT clauses via filters would receive SQL that already included the deterministic changes
2. Plugins couldn't opt out or modify the SQL in ways that might conflict with the deterministic logic
3. This could break plugins that parse or manipulate the SQL clauses

### Solution

This PR implements a defensive approach that:

1. Applies filters first with the original (non-deterministic) SQL clauses
2. Only applies deterministic logic if filters don't modify the clauses - checks if the filtered value equals the original prepared value
3. Respects plugin modifications - when filters modify the SQL, their changes are preserved and deterministic logic is not applied

Implementation:

• For WHERE clause: Only applies ID-based fallback if $where === $where_prepared (filter didn't modify it)
• For SORT clause: Only applies ID-based sorting if $sort === $sort_prepared (filter didn't modify it)

## Test Steps

### Automated Tests

Run the PHPUnit tests:

npm run test:php -- --filter Tests_Link_GetAdjacentPost

### Manual Testing

#### 1. Verify Default Behavior (No Filters)

1. Create test scenario:
   • Create multiple posts with identical post_date values (bulk publish drafts)
   • Navigate between posts

2. Expected Results:
   • Navigation works deterministically, you should be able to navigate through all posts with identical dates

#### 2. Verify Filter Modifications Are Respected

Test WHERE Filter:

1. Add test filter to functions.php or a test plugin:

// Test filter for next post WHERE clause
add_filter( 'get_next_post_where', function( $where ) {
        // Modify the WHERE clause - deterministic fallback should NOT be applied.
        return $where . ' AND 1=1';
}, 10, 1 );

// Test filter for previous post WHERE clause
add_filter( 'get_previous_post_where', function( $where ) {
        // Modify the WHERE clause - deterministic fallback should NOT be applied.
        return $where . ' AND 1=1';
}, 10, 1 );

2. Verify behavior:
   • Navigate between posts, rhe deterministic ID fallback should NOT be applied (no AND p.ID in WHERE clause)

Test SORT Filter:

1. Remove WHERE filters, add SORT filter:

add_filter( 'get_next_post_sort', function( $sort, $post, $order ) {
        // Remove ID from sort - deterministic ID sort should NOT be applied.
        return "ORDER BY p.post_date $order LIMIT 1";
}, 10, 3 );

add_filter( 'get_previous_post_sort', function( $sort, $post, $order ) {
        return "ORDER BY p.post_date $order LIMIT 1";
}, 10, 3 );

2. Verify behavior:
   • Create posts with identical dates
   • Navigate between posts, deterministic ID sort is NOT applied

#### 3. Verify Unmodified Filters Still Get Deterministic Logic

1. Add filters that don't modify the clauses:

add_filter( 'get_next_post_where', function( $where ) {
        // Return unchanged - deterministic fallback should be applied.
        return $where;
}, 10, 1 );

add_filter( 'get_next_post_sort', function( $sort ) {
        // Return unchanged - deterministic ID sort should be applied.
        return $sort;
}, 10, 1 );

2. Verify behavior:
   • Navigation works deterministically, you should be able to navigate through all posts with identical dates

Tentative PR ​https://github.com/WordPress/wordpress-develop/pull/10620 for discussion purposes.

Thank you!

I've added some comments to the PR in the event this goes ahead.

I don't feel strongly about it either way but it might be worth waiting for any other reports/feedback on the make/core post before committing to shipping this in 6.9.1 (pun intended).

Thanks @peterwilsoncc I'll fix those things up next week.

I don't feel strongly about it either way but it might be worth waiting for any other reports/feedback on the make/core post before committing to shipping this in 6.9.1 (pun intended).

Good call. No comments so far. Best not to jump the gun I suppose.

It seems that its moving forward and @peterwilsoncc is reviewing it.

@ramonopoly ping me out when you added the changes proposed and I will add this for testing.

ping me out when you added the changes proposed and I will add this for testing

Much obliged @SirLouen 🙇🏻‍♂️

I plan to respond to the feedback in that PR and keep it current.

I would lean on the opinion of reviewers as to whether it's worth committing to Core.

We were able to mitigate this at a platform level using the following code to capture looping behavior in derivative themes:

<?php

add_action( 'init', function() {
        if ( defined( 'WC_PLUGIN_FILE' ) ) {
                add_filter( 'get_next_post_where', 'wp69_product_adjacent_where', 99999, 5 );
                add_filter( 'get_previous_post_where', 'wp69_product_adjacent_where', 99999, 5 );
        }
} );

function wp69_product_adjacent_where( $where, $in_same_term, $excluded_terms, $taxonomy, $post ) {
        if ( ! ( $post instanceof WP_Post ) || ! property_exists( $post, 'post_type' ) || 'product' !== $post->post_type ) {
                return $where;
        }

        // If the same WHERE repeats too many times, force no results to be returned.
        $threshold = 5;
        static $where_key = null;
        static $where_count;

        $this_where_key = md5( $where );
        if ( $this_where_key !== $where_key ) {
                $where_key = $this_where_key;
                $where_count = 0;
        } else {
                $where_count = $where_count + 1;
        }

        if ( $where_count >= $threshold ) {
                return $where . ' AND 1=0';
        }
        return $where;
}