Commit 49ec5efc354 for woocommerce
commit 49ec5efc354321fb1e4774dcd442dcfd5828ce23
Author: Brandon Kraft <public@brandonkraft.com>
Date: Wed Mar 25 15:24:50 2026 +0100
Fix SQL error for exclusion-only and empty search queries (#63760)
* Fix SQL error for exclusion-only and empty search queries
When a search contains only exclusion terms (e.g. ?s=-condebug) or is
empty, WooCommerce unconditionally set orderby=relevance. WordPress
core's relevance ordering requires positive search terms to build a
valid ORDER BY clause, resulting in invalid SQL like "ORDER BY DESC,".
Add has_positive_search_terms() to detect these cases and fall back to
the default catalog ordering instead of relevance.
Fixes #63590
Co-authored-by: Vladimir Reznichenko <kalessil@gmail.com>
diff --git a/plugins/woocommerce/changelog/fix-empty-search-orderby b/plugins/woocommerce/changelog/fix-empty-search-orderby
new file mode 100644
index 00000000000..ee529057793
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-empty-search-orderby
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Fix SQL error when searching with empty or exclusion-only terms by falling back to default ordering instead of relevance.
diff --git a/plugins/woocommerce/includes/class-wc-query.php b/plugins/woocommerce/includes/class-wc-query.php
index 1f67a91aac4..4aaefff1367 100644
--- a/plugins/woocommerce/includes/class-wc-query.php
+++ b/plugins/woocommerce/includes/class-wc-query.php
@@ -576,6 +576,59 @@ class WC_Query {
remove_filter( 'posts_clauses', array( $this, 'order_by_rating_post_clauses' ) );
}
+ /**
+ * Check whether the current search query contains at least one positive (non-exclusion) term.
+ *
+ * WordPress relevance ordering requires positive search terms to build a valid ORDER BY clause.
+ * Searches that are empty or contain only exclusion terms (e.g. "-condebug") produce no positive
+ * terms, which results in invalid SQL when relevance ordering is used.
+ *
+ * This method delegates tokenization to WP_Query so it correctly handles WordPress's search
+ * term parsing (splitting on spaces, commas, and +) and respects the
+ * wp_query_search_exclusion_prefix filter.
+ *
+ * @return bool
+ */
+ private function has_positive_search_terms(): bool {
+ $search_string = get_query_var( 's' );
+ $search_string = is_array( $search_string ) ? '' : trim( (string) $search_string );
+
+ if ( '' === $search_string ) {
+ return false;
+ }
+
+ // Use WP_Query to parse search terms using core's tokenization rules.
+ $search_query = new class( array( 's' => $search_string ) ) extends \WP_Query {
+ /**
+ * This constructor is overridden to avoid triggering a database query while allowing access to search term parsing routines.
+ * Using public query APIs such as the `parse_query` method leads to test regressions, so an anonymous class approach is used instead.
+ *
+ * @param string|array $query URL query string or array of vars.
+ */
+ public function __construct( $query = '' ) {
+ $this->query_vars = (array) $query;
+ $this->parse_search( $this->query_vars );
+ }
+ };
+ $search_terms = $search_query->query_vars['search_terms'] ?? array();
+
+ if ( empty( $search_terms ) ) {
+ return false;
+ }
+
+ /** This filter is documented in wp-includes/class-wp-query.php */
+ $exclusion_prefix = (string) apply_filters( 'wp_query_search_exclusion_prefix', '-' ); // phpcs:ignore WooCommerce.Commenting.CommentHooks.MissingSinceComment
+
+ if ( '' !== $exclusion_prefix ) {
+ $search_terms = array_filter(
+ $search_terms,
+ static fn( $term ) => ! str_starts_with( $term, $exclusion_prefix )
+ );
+ }
+
+ return ! empty( $search_terms );
+ }
+
/**
* Returns an array of arguments for ordering products based on the selected values.
*
@@ -598,7 +651,7 @@ class WC_Query {
}
if ( ! $orderby_value ) {
- if ( is_search() ) {
+ if ( is_search() && $this->has_positive_search_terms() ) {
$orderby_value = 'relevance';
} else {
$orderby_value = apply_filters( 'woocommerce_default_catalog_orderby', get_option( 'woocommerce_default_catalog_orderby', 'menu_order' ) );
diff --git a/plugins/woocommerce/tests/php/includes/class-wc-query-test.php b/plugins/woocommerce/tests/php/includes/class-wc-query-test.php
index abb06816aae..f8d1bad9de1 100644
--- a/plugins/woocommerce/tests/php/includes/class-wc-query-test.php
+++ b/plugins/woocommerce/tests/php/includes/class-wc-query-test.php
@@ -1,4 +1,5 @@
<?php
+declare( strict_types = 1 );
/**
* Tests for WC_Query.
@@ -77,4 +78,63 @@ class WC_Query_Test extends \WC_Unit_Test_Case {
update_option( 'page_on_front', $default_page_on_front );
wp_delete_post( $shop_page_id, true );
}
+
+ /**
+ * Data provider for search ordering tests.
+ *
+ * @return array[] Each entry: [ search query string, whether relevance ordering is expected, description ].
+ */
+ public function data_provider_search_ordering(): array {
+ return array(
+ 'normal search' => array( 'shirt', true, 'Normal search should use relevance ordering' ),
+ 'exclusion-only search' => array( '-condebug', false, 'Exclusion-only search should not use relevance ordering' ),
+ 'empty search' => array( '', false, 'Empty search should not use relevance ordering' ),
+ 'multiple exclusion terms' => array( '-foo+-bar', false, 'Multiple exclusion terms should not use relevance ordering' ),
+ 'mixed positive + exclusion' => array( 'shirt+-condebug', true, 'Mixed search with positive terms should use relevance ordering' ),
+ 'bare dash' => array( '-', false, 'Bare dash search should not use relevance ordering' ),
+ 'comma-separated mixed' => array( '-foo,bar', true, 'Comma-separated search with positive terms should use relevance ordering' ),
+ );
+ }
+
+ /**
+ * @testdox Ordering args: $description.
+ * @dataProvider data_provider_search_ordering
+ *
+ * @param string $search The search query string.
+ * @param bool $expect_relevance Whether relevance ordering is expected.
+ * @param string $description Test case description.
+ */
+ public function test_get_catalog_ordering_args_search_ordering( string $search, bool $expect_relevance, string $description ): void {
+ $sut = new WC_Query();
+
+ $this->go_to( '/?s=' . rawurlencode( $search ) . '&post_type=product' );
+
+ $result = $sut->get_catalog_ordering_args();
+
+ if ( $expect_relevance ) {
+ $this->assertSame( 'relevance', $result['orderby'], $description );
+ } else {
+ $this->assertNotEquals( 'relevance', $result['orderby'], $description );
+ }
+ }
+
+ /**
+ * @testdox Ordering args should respect the wp_query_search_exclusion_prefix filter.
+ */
+ public function test_get_catalog_ordering_args_respects_custom_exclusion_prefix(): void {
+ $sut = new WC_Query();
+
+ $custom_prefix = static function () {
+ return '!';
+ };
+ add_filter( 'wp_query_search_exclusion_prefix', $custom_prefix );
+
+ $this->go_to( '/?s=!foo&post_type=product' );
+
+ $result = $sut->get_catalog_ordering_args();
+
+ remove_filter( 'wp_query_search_exclusion_prefix', $custom_prefix );
+
+ $this->assertNotEquals( 'relevance', $result['orderby'], 'Exclusion-only search with custom prefix should not use relevance ordering' );
+ }
}