[wp-trac] [WordPress Trac] #62931: Inconsistency When Querying and Sorting by meta_key with Multiple Values

[WordPress Trac]

#62931: Inconsistency When Querying and Sorting by meta_key with Multiple Values
---------------------------+-----------------------------
 Reporter:  maximeschoeni  |      Owner:  (none)
     Type:  defect (bug)   |     Status:  new
 Priority:  normal         |  Milestone:  Awaiting Review
Component:  General        |    Version:  6.7.1
 Severity:  normal         |   Keywords:
  Focuses:                 |
---------------------------+-----------------------------
 **Description:**

 I've encountered an issue with a SQL query generated by WordPress when
 sorting posts by a meta_key value. When I copied the exact same query into
 phpMyAdmin, it resulted in an error, whereas it worked fine within
 WordPress. Upon further investigation, I realized that this discrepancy
 occurs because WordPress disables the SQL mode "ONLY_FULL_GROUP_BY" by
 default.

 The error itself is caused when sorting posts based on the "meta_key"
 column, when some posts have multiple values for the same meta_key. This
 leads to inconsistent sorting results and, with "ONLY_FULL_GROUP_BY"
 enabled, a SQL error.

 **Example Scenario:**

 Consider a custom post type "biography," where each post has a meta_key
 "nationality." Some biographies may have multiple nationalities (e.g.,
 "Algeria" and "Zimbabwe"). When sorting biographies by nationality, the
 sorting behavior for posts with multiple values is unpredictable—it may
 randomly appear at start or at end.

 **Current Query Generated by WordPress:**
 {{{
 SELECT wp_posts.* FROM wp_posts
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
 WHERE 1=1 [...]
 GROUP BY wp_posts.ID
 ORDER BY wp_postmeta.meta_value
 }}}

 **Suggested Fix:**
 To resolve this issue, an aggregation function such as `MIN()` should be
 used:

 {{{
 SELECT wp_posts.* FROM wp_posts
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
 WHERE 1=1 [...]
 GROUP BY wp_posts.ID
 ORDER BY MIN(wp_postmeta.meta_value)
 }}}
 **Related Ticket**
 [https://core.trac.wordpress.org/ticket/54669]

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