Wrong result / duplicates in query when using MariaDB

[Kim.Bertelsen]

The page parent selector uses a rest api endpoint that results in the following query:

First 100 pages:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN (51915) AND wp_posts.post_parent NOT IN (51915) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC LIMIT 0,100

Next group of 100 pages:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN (51915) AND wp_posts.post_parent NOT IN (51915) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC LIMIT 100,100

etc..

In a site we've got a total of 128 pages that match this query.

Resulting total is correct, however, there is 17 duplicates. This means that we're missing pages in the resulting page parent select input, and instead we have duplicates.

Running query directly in database gives the same, incorrect, result.
Replicating query as wp_query gives the same, incorrect, result.

I've tested this with no plugins activated, twentytwenty theme. Same incorrect result.

MySQL 5.5.5 - 10.1.44-MariaDB
Ubuntu 18.04.1
WordPress 5.4
TwentyTwenty 1.2

I'm also getting one duplicate in my local duplicate (local by flywheel 3.3.0) using built in MySQL 5.7.23

[Kim.Bertelsen]

Made a simple plugin to test the queries front-end. Replace the $ignore_id with a post ID you've got

[johnbillion]

Thanks for the report @kimbertelsen .

What happens if you run the query directly in your mysql console without the LIMIT clause in place? Do you get the correct total and correct results or do you get duplicates in the results?

[Kim.Bertelsen]

Replying to johnbillion:

Thanks for the report @kimbertelsen .

What happens if you run the query directly in your mysql console without the LIMIT clause in place? Do you get the correct total and correct results or do you get duplicates in the results?

Without LIMIT I get the full list of 128 pages, no duplicates.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN (51915) AND wp_posts.post_parent NOT IN (51915) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC

[johnbillion]

If you're seeing a problem with incorrect query results when run directly against your database (ie. without touching WordPress) for the paginated results but not for the non-paginated results then that suggests a problem with your database indexing or caching.