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.

[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 );
                        }