[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        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 In search query, when `exact` is set to `true` and `orderby` set to
 `relevance` there is DB error

  WordPress database error: [You have an error in your SQL syntax; check
 the manual that corresponds to your MariaDB server version for the right
 syntax to use near 'DESC, wp_posts.post_date DESC LIMIT 0, 10' at line 1]

  `SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND
 (((wp_posts.post_title LIKE 'hello') OR (wp_posts.post_excerpt LIKE
 'hello') OR (wp_posts.post_content LIKE 'hello'))) AND wp_posts.post_type
 IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish' OR
 wp_posts.post_author = 1 AND wp_posts.post_status = 'private') ORDER BY
 DESC, wp_posts.post_date DESC LIMIT 0, 10`

 It is clear that both options together has no meaning. But it is
 compatibility issue between [https://wordpress.org/plugins/wp-extended-
 search/ WP Extended Search] and
 [https://wordpress.org/plugins/woocommerce/ WooCommerce]

 WP Extended search has a feature to match exact sentence so it sets
 `exact` to `true` and later WooCommerce adds `orderby => relevance`
 causing this SQL error.

 === How to reproduce with just WP
 * Add this code to theme or plugin
 {{{#!php
 <?php
 add_action('pre_get_posts', function ( $query ){
         $query->set( 'exact', true );
         $query->set( 'orderby', 'relevance' );
         $query->set( 'order', 'DESC' );
 });
 }}}
 * Go to front-end and make a search, you will see the error.

 === Proposed fix
 Here https://core.trac.wordpress.org/browser/tags/5.4.2/src/wp-includes
 /class-wp-query.php#L2357
 We checking if `! empty( $q['search_orderby_title'] )` is not empty but we
 allow to call `parse_search_order()` when `'relevance' === $q['orderby']`
 causing `ORDER BY DESC` in SQL query without column name.
 IMHO, we should not call `parse_search_order()` when
 `search_orderby_title` is empty regardless of `orderby`.

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