WordPress.org

Make WordPress Core

Opened 4 months ago

Last modified 4 months ago

#49785 new defect (bug)

Wrong result / duplicates in query when using MariaDB

Reported by: Kim.Bertelsen Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 5.4
Component: Database Keywords:
Focuses: Cc:

Description

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

Attachments (1)

kim-logger-ting.zip (1.1 KB) - added by Kim.Bertelsen 4 months ago.
Made a simple plugin to test the queries front-end. Replace the $ignore_id with a post ID you've got

Download all attachments as: .zip

Change History (4)

@Kim.Bertelsen
4 months ago

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

#1 follow-up: @johnbillion
4 months ago

  • Component changed from General to Database
  • Keywords reporter-feedback added

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?

#2 in reply to: ↑ 1 @Kim.Bertelsen
4 months ago

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

#3 @johnbillion
4 months ago

  • Keywords reporter-feedback removed

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.

Note: See TracTickets for help on using tickets.