When exact is true and orderby set to relevance, there is a DB error on search results page

[5um17]

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 ​WP Extended Search and ​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
  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.

[SergeyBiryukov]

Thanks for the ticket!

IMHO, we should not call parse_search_order() when search_orderby_title is empty regardless of orderby.

As far as I can tell, this is already the case in the current condition:

if ( ! empty( $q['search_orderby_title'] )
	&& ( empty( $q['orderby'] ) && ! $this->is_feed )
		|| ( isset( $q['orderby'] ) && 'relevance' === $q['orderby'] )
) {
	$search_orderby = $this->parse_search_order( $q );
}

$this->parse_search_order( $q ) is only called if:

• $q['search_orderby_title'] is not empty.
• AND either $q['orderby'] is empty and this is not a feed, OR $q['orderby'] is set to relevance.

If $q['search_orderby_title'] is empty, the first part of the condition is not satisfied, so ::parse_search_order() is not called.

So the analysis seems correct, but it looks like the patch won't work as expected.

[5um17]

Thanks for checking @SergeyBiryukov

As far as I can tell, this is already the case in the current condition:

No actually this is not true.

There is no parenthesis in the current condition so it will results in

<?php
var_dump( false && false || true );

Which will result in true.

In patch I am adding parenthesis

<?php
var_dump( false && ( false || true ) );

Please correct me if I am wrong?

[SergeyBiryukov]

As far as I can tell, there are parentheses in the current condition:

( false && ( false || true ) )

With the patch, that becomes:

( false && ( ( false || true ) )

but the logic is the same.

[5um17]

Could you please take another look at https://core.trac.wordpress.org/browser/trunk/src/wp-includes/class-wp-query.php#L2368 ?

I am sorry but I don't see any braces wrapping last two condition.

I am showing the start and end of parentheses with arrow and highlighted internal braces. There is no braces wrapping last && and ||

And only because of this it is showing error.