[wp-trac] [WordPress Trac] #49785: Wrong result / duplicates in query when using MariaDB

[WordPress Trac]

#49785: Wrong result / duplicates in query when using MariaDB
---------------------------+-----------------------------
 Reporter:  Kim.Bertelsen  |      Owner:  (none)
     Type:  defect (bug)   |     Status:  new
 Priority:  normal         |  Milestone:  Awaiting Review
Component:  General        |    Version:  5.4
 Severity:  normal         |   Keywords:
  Focuses:                 |
---------------------------+-----------------------------
 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

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/49785>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform