No search results for default search form after upgrade to version 4.4.11

Sorry to hear about the problem you are having.

If it is possible, could you please temporarily upgrade again to 4.4.11 and reproduce the issue on your website so that I can troubleshoot it quickly and resolve the issue?

Also, please share your website URL where it is happening.

Best regards,

Hi Vinod

Sure. I’ve upgraded to 4.4.11, and the URL is https://www.fsc.org.uk/?s=parent

Cheers

Dan

Thank you!

Does the ‘parent’ term exist anywhere?

If yes then please share the URL where it exists?

Yes. When on version 4.4.10, the following pages are shown in the search results:

https://www.fsc.org.uk/parents-reference/associate-membership/
https://www.fsc.org.uk/parents-reference/fsc-stores/
https://www.fsc.org.uk/parents-reference/applying-for-camp/

Ok

Please edit the Default search form and tell me what you have set for the below plugin options on the path Ivory Search -> Search Forms -> Default Search Form -> Options

Search All Or Any Search Terms
Fuzzy Matching

Also, please try changing the above options and see whether it works fine.

We are getting somewhere, as I have other search forms which still work OK!

For the default search form:
* Search All Or Any Search Terms = AND
* Fuzzy Matching = Whole

For the other search form which works OK:
* Search All Or Any Search Terms = AND
* Fuzzy Matching = Partial

When I toggle Fuzzy Matching to Partial on the default search form then it starts working again, but obviously with partial matching on!

I understood the issue now which I am troubleshooting on my website so you can revert back to the old working plugin version now.

I will notify you here, after fixing the issue.

Thank you for your cooperation and patience.

Wonderful – thanks Vinod!

I have tested it multiple times on my website using the shared settings but couldn’t reproduce the issue.

Could you please try resetting the search form by clicking on the Reset button of the search form?

I’ve just given it a reset on my development site (which isn’t publicly accessible unfortunately) and it had no effect. As soon as Fuzzy Matching = Whole, I get no results. When Fuzzy Matching = Partial, I get results.

Here are the two queries running against the database, which I intercepted using the ‘posts_where’ filter:

Partial:

AND ( ((wp_posts.post_title LIKE ‘{7fcd9d466186ff775e1079cc8fb6f57c99a4461aa6b41b3ac16ef0841a4b6a1d}parent{7fcd9d466186ff775e1079cc8fb6f57c99a4461aa6b41b3ac16ef0841a4b6a1d}’) OR (wp_posts.post_content LIKE ‘{7fcd9d466186ff775e1079cc8fb6f57c99a4461aa6b41b3ac16ef0841a4b6a1d}parent{7fcd9d466186ff775e1079cc8fb6f57c99a4461aa6b41b3ac16ef0841a4b6a1d}’) OR (wp_posts.post_excerpt LIKE ‘{7fcd9d466186ff775e1079cc8fb6f57c99a4461aa6b41b3ac16ef0841a4b6a1d}parent{7fcd9d466186ff775e1079cc8fb6f57c99a4461aa6b41b3ac16ef0841a4b6a1d}’))) AND wp_posts.post_type IN (‘post’, ‘page’) AND (wp_posts.post_status = ‘publish’)

Whole:

AND ( ((wp_posts.post_title REGEXP ‘(^|[[\\s|.|~||!|@|#|$|{f6dd7bf71b55e6f46e5048a3b2dcb866092152f00922a87c3e8e44e6c3db24f6}|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]])parent([[\\s|.|~||!|@|#|$|{f6dd7bf71b55e6f46e5048a3b2dcb866092152f00922a87c3e8e44e6c3db24f6}|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]]|$)’) OR (wp_posts.post_content REGEXP ‘(^|[[\\s|.|~||!|@|#|$|{f6dd7bf71b55e6f46e5048a3b2dcb866092152f00922a87c3e8e44e6c3db24f6}|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]])parent([[\\s|.|~||!|@|#|$|{f6dd7bf71b55e6f46e5048a3b2dcb866092152f00922a87c3e8e44e6c3db24f6}|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]]|$)’) OR (wp_posts.post_excerpt REGEXP ‘(^|[[\\s|.|~||!|@|#|$|{f6dd7bf71b55e6f46e5048a3b2dcb866092152f00922a87c3e8e44e6c3db24f6}|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]])parent([[\\s|.|~||!|@|#|$|{f6dd7bf71b55e6f46e5048a3b2dcb866092152f00922a87c3e8e44e6c3db24f6}|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]]|$)’))) AND wp_posts.post_type IN (‘post’, ‘page’) AND (wp_posts.post_status = ‘publish’)

When I try running the “whole” where clause above against the database directly, I get the error: “The regular expression contains an unclosed bracket expression.”

Oh – I’m running MySQL version 8 – specifically 8.0.20-1debian10.

I tested it on my website using MariaDB 10.4.11 version but didn’t face the issue.

It seems the issue with the MySQL version as described on the below pages.

https://bugs.mysql.com/bug.php?id=95347
https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Could you please try running below SQL queries one by one against the database directly and share the results which will help me to troubleshoot the issue?

Query 1 :

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts WHERE 1=1 AND ( ((wp_posts.post_content REGEXP '(^|[[\\s|.|~||!|@|#|$|%|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]])a([[\\s|.|~||!|@|#|$|%|^|&|*|(|)|_|\\-|+|=|{|}|[|\\]|\\||:|;|<|>|\\?|\\/|\\|\\]]|$)'))) AND wp_posts.post_type IN ('post', 'page', 'product') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Query 2 :

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts WHERE 1=1 AND ( ((wp_posts.post_content REGEXP '(^|[[\\s|.|~||!|@|#|$|%|^|&|*|(|)|_|\\-|+|=|{|}|[|]|\\||:|;|<|>|\\?|\\/|\\|]])a([[\\s|.|~||!|@|#|$|%|^|&|*|(|)|_|\\-|+|=|{|}|[|]|\\||:|;|<|>|\\?|\\/|\\|]]|$)'))) AND wp_posts.post_type IN ('post', 'page', 'product') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Query 3 :

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts WHERE 1=1 AND ( ((wp_posts.post_content REGEXP '(^|[[\\s|.|~||!|@|#|$|%|^|&|*|(|)|_|\\-|+|=|{|}|\\[|]|\\||:|;|<|>|\\?|\\/|\\|]])a([[\\s|.|~||!|@|#|$|%|^|&|*|(|)|_|\\-|+|=|{|}|\\[|]|\\||:|;|<|>|\\?|\\/|\\|]]|$)'))) AND wp_posts.post_type IN ('post', 'page', 'product') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Sure. All three had the error “Expression #1 of ORDER BY clause is not in SELECT list, references column ‘wordpress.wp_posts.post_date’ which is not in SELECT list; this is incompatible with DISTINCT”

So I removed the distinct and added a group by, to give:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND … group by wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

And got:

Query 1: “The regular expression contains an unclosed bracket expression.”

Query 2: query worked fine, 10 rows returned

Query 3: query worked fine, 0 rows returned

Thank you for your cooperation.

Your help here is really appreciated.

I have used the expression used in Query 2 and released the plugin version 4.4.12 containing the fix.

Could you please update the plugin on your website and let me know how it works for you?

Hi Vinod,

No problem at all! I’ve updated and can confirm it’s all working correctly.

Thanks a lot for your prompt replies, and congratulations on the plugin – it’s great

Cheers

Dan

Marking as resolved here.