Meta data fields preventing posts with title matching search returning

We’ve noticed that with the plugin enabled searching for a post titled: ‘Lorem Ipsum Concepts Ipsum’ using the search term ‘Concepts’ resulted in a limited number or results that didn’t include our post.

We pulled the query from WordPress and identified that the issue was being caused by this code:

  INNER JOIN wp_postmeta AS b ON (
    (b.meta_id = a.meta_id + @@auto_increment_increment)
    AND (b.post_id = wp_posts.ID)
    )

We’re not 100% on why this is necessary, but on the post in question we only had two postmeta records: _edit_lock and _edit_last. These two records were not following each other sequentially, there were ~3 records between them that were file attachments related to other posts.

We managed to include our post in the returned results by:

• commenting out the demonstrated join and all references to its tables in the rest of the query
• replacing the AND condition with an OR
• Commenting out either half of the join condition (essentially an OR)
• Switching the join type to LEFT

But like I said we’re not sure exactly what these joins are doing so we can’t recommend any of the the above.

We managed to fix our affected post by manually deleting the _edit_lock and _edit_last records from the post meta table. When we re saved the post the records were created sequentially and the post was returned in results on subsequent queries.

The full query pulled from WordPress was:

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
  JOIN wp_icl_translations t
    ON wp_posts.ID = t.element_id
       AND t.element_type = CONCAT('post_', wp_posts.post_type)
  INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID)
  INNER JOIN wp_postmeta AS b ON (
    (b.meta_id = a.meta_id + @@auto_increment_increment)
    AND (b.post_id = wp_posts.ID)
    )
  INNER JOIN wp_posts AS c
    ON (
    (c.post_type = 'acf-field')
    AND (
      (c.post_content LIKE '%:"text"%')
      OR (c.post_content LIKE '%:"textarea"%')
      OR (c.post_content LIKE '%:"wysiwyg"%')
    )
    ) WHERE 1=1 AND
            (
                            (
                              (c.post_name = b.meta_value)
                              AND (a.meta_value LIKE '%concepts%')
                            )
                            OR
              (
                (wp_posts.post_title LIKE '%concepts%')
                OR (wp_posts.post_content LIKE '%concepts%')
                OR (wp_posts.post_excerpt LIKE '%concepts%')
              )
            )
            AND wp_posts.post_type = 'question'
            AND (
              wp_posts.post_status = 'publish'
              OR wp_posts.post_status = 'acf-disabled'
              OR wp_posts.post_status = 'future'
              OR wp_posts.post_status = 'draft'
              OR wp_posts.post_status = 'pending'
              OR wp_posts.post_status = 'private'
            )
            AND
            (
              ( ( t.language_code = 'en' OR 0 ) AND wp_posts.post_type  IN ('post','page','acf-field-group','person','event','question','vacancy','venue' ) )
              OR wp_posts.post_type  NOT IN ('post','page','acf-field-group','person','event','question','vacancy','venue' )
            )
ORDER BY wp_posts.post_title LIKE '{2de070b23d42acba38e1dad98d42bb10594e7899833772cb5e67642ebec8d3ee}concepts{2de070b23d42acba38e1dad98d42bb10594e7899833772cb5e67642ebec8d3ee}' DESC, wp_posts.post_date DESC LIMIT 0, 20

I would have thought the intended behaviour would be: return the post regardless of any meta data situation: the title matches.

Cheers