[wp-trac] [WordPress Trac] #50871: When exact is true and orderby set to relevance, there is a DB error on search results page

[WordPress Trac]

#50871: When exact is true and orderby set to relevance, there is a DB error on
search results page
------------------------------------+------------------------------
 Reporter:  5um17                   |       Owner:  (none)
     Type:  defect (bug)            |      Status:  new
 Priority:  normal                  |   Milestone:  Awaiting Review
Component:  Query                   |     Version:
 Severity:  normal                  |  Resolution:
 Keywords:  has-patch dev-feedback  |     Focuses:
------------------------------------+------------------------------

Comment (by leedxw):

 I can see database errors in the logs triggered by this issue (in 6.9.1) -
 I wrote the patch before finding this ticket, and it is as suggested.

 To reproduce:

 {{{
 curl -s "http://localhost/?s=-test+-example&orderby=relevance"
 }}}

 produces log

 {{{
 WordPress database error You have an error in your SQL syntax; check the
 manual that corresponds to your MySQL server version for the right syntax
 to use near 'THEN 2 ELSE 6 END), wp_posts.post_date DESC
                                          LIMIT 0, 10' at line 5 for query
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
                                          FROM wp_posts
                                          WHERE 1=1  AND
 (((wp_posts.post_title NOT LIKE '%test%') AND (wp_posts.post_excerpt NOT
 LIKE '%test%') AND (wp_posts.post_content NOT LIKE '%test%')) AND
 ((wp_posts.post_title NOT LIKE '%example%') AND (wp_posts.post_excerpt NOT
 LIKE '%example%') AND (wp_posts.post_content NOT LIKE '%example%')))  AND
 (wp_posts.post_password = '')  AND ((wp_posts.post_type = 'attachment' AND
 (wp_posts.post_status = 'publish')) OR (wp_posts.post_type = 'page' AND
 (wp_posts.post_status = 'publish')) OR (wp_posts.post_type = 'post' AND
 (wp_posts.post_status = 'publish')))

                                          ORDER BY (CASE WHEN  THEN 2 ELSE
 6 END), wp_posts.post_date DESC
                                          LIMIT 0, 10 made by require('wp-
 blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query,
 WP_Query->get_posts
 }}}

 suggested patch

 {{{
 diff --git a/wp-includes/class-wp-query.php b/wp-includes/class-wp-
 query.php
 index ba7395f9..65f013e9 100644
 --- a/wp-includes/class-wp-query.php
 +++ b/wp-includes/class-wp-query.php
 @@ -2546,7 +2546,7 @@ class WP_Query {
                 // Order search results by relevance only when another
 "orderby" is not specified in the query.
                 if ( ! empty( $query_vars['s'] ) ) {
                         $search_orderby = '';
 -                       if ( ! empty( $query_vars['search_orderby_title']
 ) && ( empty( $query_vars['orderby'] ) && ! $this->is_feed ) || ( isset(
 $query_vars['orderby'] ) && 'relevance' === $query_vars['orderby'] ) ) {
 +                       if ( ! empty( $query_vars['search_orderby_title']
 ) && ( ( empty( $query_vars['orderby'] ) && ! $this->is_feed ) || ( isset(
 $query_vars['orderby'] ) && 'relevance' === $query_vars['orderby'] ) ) ) {
                                 $search_orderby =
 $this->parse_search_order( $query_vars );
                         }

 }}}

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/50871#comment:6>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform