[wp-trac] [WordPress Trac] #25245: Issue with SQL query generated by pre_get_posts

[WordPress Trac]

#25245: Issue with SQL query generated by pre_get_posts
---------------------------+-----------------------------
 Reporter:  greendemiurge  |      Owner:
     Type:  defect (bug)   |     Status:  new
 Priority:  normal         |  Milestone:  Awaiting Review
Component:  General        |    Version:  3.6
 Severity:  normal         |   Keywords:
---------------------------+-----------------------------
 For reference, I attempted to run this by support first to validate that
 the issue is truly a bug, but I have not yet gotten a reply. I have vetted
 this extensively myself, I was only looking for a second opinion
 (http://wordpress.org/support/topic/problem-with-the-wp-meta_query-
 sql?replies=2).

 The issue, in brief, is that when I use the pre_get_posts method to tell
 Wordpress to search two custom meta fields in addition to the Title and
 Content fields the resulting SQL Query is put together using AND
 conditions rather than OR conditions, meaning that the searched keyword
 has to exist in all places for a result to be returned, instead of being
 returned if the keyword is in any of the fields. I have verified this is
 the case using xDebug and by setting the keyword in all places.

 Here is the code I used in functions.php to add the two custom meta
 fields:

 {{{
 function custom_search_query( $query ) {
         if ( !is_admin() && $query->is_search ) {
                 $query->set('post_type', 'listing');

                 $metaquery = array(
                         array(
                                 'key' => '_shortdescription',
                                 'value' => $query->query_vars['s'],
                                 'compare' => 'LIKE'
                         ),
                         array(
                                 'key' => '_longdescription',
                                 'value' => $query->query_vars['s'],
                                 'compare' => 'LIKE'
                         ));

                 $query->set('meta_query', $metaquery);

         }
      }
      add_filter( 'pre_get_posts', 'custom_search_query');
 }}}

 And here is the SQL query this code generates when searching for "val":

 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
 INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
 WHERE 1=1
 AND (((wp_posts.post_title LIKE '%val%')
 OR (wp_posts.post_content LIKE '%val%')))
 AND (wp_posts.post_password = '')
 AND wp_posts.post_type = 'listing'
 AND (wp_posts.post_status = 'publish')
 AND ((wp_postmeta.meta_key = '_shortdescription' AND
 CAST(wp_postmeta.meta_value AS CHAR) LIKE '%val%')
 AND (mt1.meta_key = '_longdescription' AND CAST(mt1.meta_value AS CHAR)
 LIKE '%val%') )
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_date
 DESC LIMIT 0, 10
 }}}

 This query functions as expected if I manually run it, changing the two
 final ANDs to ORs:

 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
 INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
 WHERE 1=1
 AND (((wp_posts.post_title LIKE '%val%')
 OR (wp_posts.post_content LIKE '%val%')))
 AND (wp_posts.post_password = '')
 AND wp_posts.post_type = 'listing'
 AND (wp_posts.post_status = 'publish')
 OR ((wp_postmeta.meta_key = '_shortdescription' AND
 CAST(wp_postmeta.meta_value AS CHAR) LIKE '%val%')
 OR (mt1.meta_key = '_longdescription' AND CAST(mt1.meta_value AS CHAR)
 LIKE '%val%') )
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_date
 DESC LIMIT 0, 10
 }}}

 I pulled the first defective query from Wordpress using Eclipse and
 xDebug, setting a breakpoint just prior to the loop code on search.php.

 Thank you for your consideration

--
Ticket URL: <http://core.trac.wordpress.org/ticket/25245>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software