WP_Query: force deterministic ordering #10262

ramonjd · 2025-10-15T02:25:19Z

I'm testing out an approach to fix a long-standing issue:

https://core.trac.wordpress.org/ticket/47642
https://core.trac.wordpress.org/ticket/52907
https://core.trac.wordpress.org/ticket/64042
https://core.trac.wordpress.org/ticket/44349
https://core.trac.wordpress.org/ticket/46294
https://core.trac.wordpress.org/ticket/8107 (17 years! 🏅 )

Possibly related too:

https://core.trac.wordpress.org/ticket/46294
https://core.trac.wordpress.org/ticket/52626

Unit tests to come once I've validated the approach.

This change addresses potential duplicate records across pages when multiple posts share the same value for a field.

It updates WP_Query ordering to ensure deterministic results by adding ID as a secondary sort field.

npm run test:php -- --filter Tests_Query_DeterministicOrdering

src/wp-includes/class-wp-query.php

ramonjd · 2025-10-15T02:38:20Z

src/wp-includes/class-wp-query.php

 			$orderby = '';
 		} else {
-			$orderby_array = array();
+			/*


@peterwilsoncc When you get a spare moment (can be after 6.9 or whenever you have head space) could you sanity check this approach for me?

The TL;DR is:

When multiple posts have identical values for the primary sort field (like post_date, post_title, menu_order), the database doesn't guarantee consistent ordering across pagination.

This causes inconsistent pagination results, mainly in the form of dupes.

The solution here (and in all the other attempts from 6 years ago) has been to automatically add ID as a secondary sort field when ordering by fields that can have duplicate values. This ensures records with identical primary sort values always appear in the same order.

github-actions · 2025-10-15T02:59:39Z

The following accounts have interacted with this PR and/or linked issues. I will continue to update these lists as activity occurs. You can also manually ask me to refresh this list by adding the props-bot label.

Core Committers: Use this line as a base for the props when committing in SVN:

Props ramonopoly, peterwilsoncc, azaozz.

To understand the WordPress project's expectations around crediting contributors, please review the Contributor Attribution page in the Core Handbook.

src/wp-includes/class-wp-query.php

github-actions · 2025-10-21T22:33:44Z

Test using WordPress Playground

The changes in this pull request can previewed and tested using a WordPress Playground instance.

WordPress Playground is an experimental project that creates a full WordPress instance entirely within the browser.

Some things to be aware of

The Plugin and Theme Directories cannot be accessed within Playground.
All changes will be lost when closing a tab with a Playground instance.
All changes will be lost when refreshing the page.
A fresh instance is created each time the link below is clicked.
Every time this pull request is updated, a new ZIP file containing all changes is created. If changes are not reflected in the Playground instance,
it's possible that the most recent build failed, or has not completed. Check the list of workflow runs to be sure.

For more details about these limitations and more, check out the Limitations page in the WordPress Playground documentation.

Test this pull request with WordPress Playground.

ramonjd · 2025-10-22T05:50:29Z

src/wp-includes/class-wp-query.php

+			$new_request = $this->request;
+			// Split SQL into parts.
+			$parts = explode( 'ORDER BY', $new_request );
+			if ( count( $parts ) === 2 ) {
+				// Replace only in the SELECT part, preserve ORDER BY.
+				$select_part = str_replace( $fields, "{$wpdb->posts}.*", $parts[0] );
+				$new_request = $select_part . 'ORDER BY' . $parts[1];
+			} else {
+				// No ORDER BY clause, safe to replace.
+				$new_request = str_replace( $fields, "{$wpdb->posts}.*", $new_request );
+			}


The current str_replace is too greedy in that it will replace strings in the ORDER BY clause as well.

src/wp-includes/link-template.php

tests/phpunit/tests/query/deterministicOrdering.php

peterwilsoncc · 2025-10-27T01:08:39Z

src/wp-includes/class-wp-query.php

+			 * When multiple posts have the same value for a field, add ID as secondary sort to guarantee consistent ordering.
+			 * Note: this is to circumvent a bug that is currently being tracked in https://core.trac.wordpress.org/ticket/44349.
+			 */
+			$fields_requiring_deterministic_orderby = array(


Meta data (both values and keys) can be identical too. That will be a little messy because orderby also accepts the array keys in $meta_query.

The tickets are very long, so I'm wondering if it was considered to add the ID to the ordering if it wasn't included in the parameter already.

so I'm wondering if it was considered to add the ID to the ordering if it wasn't included in the parameter already.

Thanks, do you mean just include ID by default?

The whitelist is a bit janky and will add maintenance burden. I just did it as a very naive, first version to get a discussion going.

A previous, much older patch used a black list 'ID', 'rand', 'relevance'.

Not sure it would address the maintenance burden side of things, but it'd be more permissive.

Not sure it would address the maintenance burden side of things

Yea, blacklist is better imho too. It will address the maintenance burden better considering that new "columns" are added to the query once per several blue moons :)

tests/phpunit/tests/query/deterministicOrdering.php

…D as a secondary sort field. This change addresses potential duplicate records across pages when multiple posts share the same value for a field. A list of fields requiring deterministic ordering has been introduced to improve query consistency

…nding ID as a secondary sort field. Update related unit tests to reflect changes in expected SQL output for various orderby scenarios

…unit tests for orderby scenarios.

…and ensure ID is consistently used as a tie-breaker. Update unit tests to reflect changes in expected SQL output for various orderby cases.

…secondary sort field in various scenarios. Update unit tests to reflect changes in expected SQL output for orderby cases, enhancing determinism in query results.

…or consistent cache key generation. This change ensures deterministic results when 'date' is specified as the orderby value, improving query consistency.

…nt post creation. Updated post_date to use str_pad for zero-padding single-digit days.

ramonjd · 2025-12-05T01:33:38Z

I'm going to try to revive this PR with the aim to get it in for 7.0. I'll respond to @peterwilsoncc's feedback first.

I'm a bit short on time so if any folks have time to test this approach or help with alternatives, that'd be awesome!

…post creation. Introduced separate arrays for posts with identical dates, titles, and menu orders to enhance test clarity and maintainability. Updated queries to reference these shared fixtures, ensuring consistent results across pagination and ordering scenarios.

…r deterministic ordering. Updated comments for clarity and introduced a new test for metadata ordering to ensure no duplicates across paginated results.

Introduced new tests to verify deterministic ordering when posts are ordered by search relevance. Created shared fixtures for posts with identical content to ensure consistent relevance scores, preventing duplicates across paginated results. Updated the test suite to include scenarios for both explicit and empty orderby parameters, ensuring robust coverage of search-related ordering behavior.

…Included 'post__in', 'post_name__in', 'post_parent__in', and 'include' to improve query flexibility and support additional ordering scenarios.

azaozz · 2025-12-09T12:47:09Z

src/wp-includes/class-wp-query.php

+			// See get_pages(): when sort_column is 'none', the get_pages() function should not generate any ORDER BY clause.
+			// Should it rather be handled in the get_pages() function?
+			// src/wp-includes/post.php L6496
+		} elseif ( 'none' === $query_vars['orderby'] || ( is_array( $query_vars['orderby'] ) && array_key_exists( 'none', $query_vars['orderby'] ) ) ) {


Just a nitpick :) Thought I saw somewhere that isset( array['key'] ) is tiny bit faster/preferable to is_array() followed by array_key_exists(). Maybe the ( is_array( $query_vars['orderby'] ) && array_key_exists( 'none', $query_vars['orderby'] ) ) could be isset( $query_vars['orderby']['none'] )?

azaozz · 2025-12-09T13:03:30Z

src/wp-includes/class-wp-query.php

 			sort( $args['post_status'] );
 		}

-		// Add a default orderby value of date to ensure same cache key generation.


Another nitpick :) This comment still seems okay imho. No need to delete it, just edit it?

ramonjd · 2025-12-11T07:38:10Z

I was mulling over this today, and given the experience in https://make.wordpress.org/core/2025/12/10/adjacent-post-navigation-changes-in-wordpress-6-9-and-compatibility-issues/, I was wondering if the scope here could be narrowed further to only paginated queries.

I'm not sure there's a remedy for filters receiving different values with this PR.

Filter	Risk Level	Affected Queries	Common Use Cases
`posts_orderby`	HIGH	All paginated queries	General ORDER BY modifications
`posts_clauses`	HIGH	All paginated queries	Complex query modifications
`posts_orderby_request`	MEDIUM	All paginated queries	Caching plugins
`posts_clauses_request`	MEDIUM	All paginated queries	Caching plugins
`posts_search_orderby`	LOW	Search queries only	Search relevance ordering

Possibly deterministic ordering could be opt-in to begin with to give folks time to update, or just make it opt-out from the beginning.

peterwilsoncc · 2025-12-11T23:07:44Z

I was wondering if the scope here could be narrowed further to only paginated queries.

I don't think that's possible as we can't know if a query will be paginated before making the query (eg the blogs home page is likely paginated but doesn't have a page number/offset).

There's quite a few plugins with large numbers of installs using those filters, so yeah, let's take a look at these https://wpdirectory.net/search/01KC7TKAN2FGBHSK1NC321HF63

Now there is native caching in WP Core for WP_Query, I think caching plugins should be fine but we'll need to validate it. Some hosts may still be using their own.

ramonjd · 2025-12-11T23:13:47Z

Thanks, Peter.

Another thought bubble I had was pursuing a fix indirectly. Rather than forcing deterministic ordering, make it an option to sort by ID AND whatever else you want.

Wondering if this trac ticket is relevant.

Add multiple order conditions to WP_Term_Query

Edit:

an option to sort by ID AND whatever else you want

LOL you can do that anyway. Me dumb.

And that wasn't the trac ticket I was looking for. I think there's one flying about. Maybe this one Should REST API support multiple orderby values?

ramonjd force-pushed the try/add-id-for-deterministic-ordering-to-prevent-duplicate-records branch from 8f83b7e to 85339ff Compare October 15, 2025 02:30

ramonjd commented Oct 15, 2025

View reviewed changes

src/wp-includes/class-wp-query.php Outdated Show resolved Hide resolved

ramonjd self-assigned this Oct 15, 2025

ramonjd changed the title ~~Modifies the resolve_pattern_blocks function to include metadata fo…~~ WP_Query: force deterministic ordering Oct 15, 2025

ramonjd commented Oct 15, 2025

View reviewed changes

ramonjd added bug help wanted labels Oct 15, 2025

ramonjd commented Oct 17, 2025

View reviewed changes

src/wp-includes/class-wp-query.php Outdated Show resolved Hide resolved

ramonjd commented Oct 22, 2025

View reviewed changes

ramonjd force-pushed the try/add-id-for-deterministic-ordering-to-prevent-duplicate-records branch from 9c77315 to 03ff908 Compare October 22, 2025 06:30

ramonjd commented Oct 22, 2025

View reviewed changes

src/wp-includes/link-template.php Outdated Show resolved Hide resolved

ramonjd mentioned this pull request Oct 22, 2025

get_adjacent_post: Fix adjacent post navigation for posts with identical dates #10394

Closed

peterwilsoncc reviewed Oct 27, 2025

View reviewed changes

ramonjd added 12 commits December 5, 2025 12:25

WHITESPACE! Oh no!

d555ed8

Refactor WP_Query ordering logic to ensure consistent results by appe…

2ff5129

…nding ID as a secondary sort field. Update related unit tests to reflect changes in expected SQL output for various orderby scenarios

Consolidate ID tie-breaker logic and ensure consistent SQL output in …

a584d3e

…unit tests for orderby scenarios.

whitespace in unit test

7ef279b

Refine WP_Query ordering logic to handle 'none' in orderby scenarios …

c589148

…and ensure ID is consistently used as a tie-breaker. Update unit tests to reflect changes in expected SQL output for various orderby cases.

lint

b73529a

Remove ticket number in tests for now

2a851c4

Refactor WP_Query to ensure consistent ordering by appending ID as a …

6ed0474

…secondary sort field in various scenarios. Update unit tests to reflect changes in expected SQL output for orderby cases, enhancing determinism in query results.

Enhance WP_Query ordering logic by normalizing 'date' to 'date, ID' f…

32ff6e2

…or consistent cache key generation. This change ensures deterministic results when 'date' is specified as the orderby value, improving query consistency.

lint

1917d17

linto

d7b7c74

ramonjd force-pushed the try/add-id-for-deterministic-ordering-to-prevent-duplicate-records branch from a204b0d to d7b7c74 Compare December 5, 2025 01:26

Fix date formatting in deterministic ordering test to ensure consiste…

0fedb3f

…nt post creation. Updated post_date to use str_pad for zero-padding single-digit days.

ramonjd added 4 commits December 5, 2025 23:04

Refactor WP_Query ordering logic to implement a blacklist approach fo…

864399f

…r deterministic ordering. Updated comments for clarity and introduced a new test for metadata ordering to ensure no duplicates across paginated results.

Enhance WP_Query ordering by adding new fields to the orderby array. …

467d7cf

…Included 'post__in', 'post_name__in', 'post_parent__in', and 'include' to improve query flexibility and support additional ordering scenarios.

ramonjd mentioned this pull request Dec 5, 2025

Tests only for deterministic ordering. #10421

Draft

azaozz reviewed Dec 9, 2025

View reviewed changes

WP_Query: force deterministic ordering #10262

Are you sure you want to change the base?

WP_Query: force deterministic ordering #10262

Conversation

ramonjd commented Oct 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

ramonjd Oct 15, 2025

Choose a reason for hiding this comment

Uh oh!

github-actions bot commented Oct 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

github-actions bot commented Oct 21, 2025

Test using WordPress Playground

Some things to be aware of

Uh oh!

ramonjd Oct 22, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

peterwilsoncc Oct 27, 2025

Choose a reason for hiding this comment

Uh oh!

ramonjd Oct 27, 2025

Choose a reason for hiding this comment

Uh oh!

azaozz Dec 9, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

ramonjd commented Dec 5, 2025

Uh oh!

azaozz Dec 9, 2025

Choose a reason for hiding this comment

Uh oh!

azaozz Dec 9, 2025

Choose a reason for hiding this comment

Uh oh!

ramonjd commented Dec 11, 2025

Uh oh!

peterwilsoncc commented Dec 11, 2025

Uh oh!

ramonjd commented Dec 11, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

ramonjd commented Oct 15, 2025 •

edited

Loading

github-actions bot commented Oct 15, 2025 •

edited

Loading

ramonjd commented Dec 11, 2025 •

edited

Loading