[wp-trac] [WordPress Trac] #29604: Meta query OR and meta key sort generate incorrect WHERE clause

[WordPress Trac]

#29604: Meta query OR and meta key sort generate incorrect WHERE clause
--------------------------+------------------------------
 Reporter:  Pwhitehurst   |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Query         |     Version:  4.0
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------
Description changed by boonebgorges:

Old description:

> I have the following arguments
>

> $args3 = array(
>     'post_type' => 'match',
> 'orderby' => 'meta_value',
> 'meta_key' => 'match_date',
> 'order' => 'ASC',
>     'meta_query' => array(
>
>                 array(
>                         'key'     => 'home_team',
>                         'value'   => $teamID,
>                         'compare' => '=' ,
>
>                 ),
>  'relation' => 'OR',
>       array(
>                         'key'     => 'away_team',
>                         'value'   => $teamID,
>                         'compare' => '=' ,
>
>                 ),
>
>         ),
>
>         );
>

> Generates the following SQL which incorrectly OR's the match_date with
> home_team and away_team
>

> REQUEST:SELECT SQL_CALC_FOUND_ROWS  deece_posts.ID FROM deece_posts
> INNER JOIN deece_postmeta ON deece_posts.ID = deece_postmeta.post_id
> INNER JOIN deece_postmeta AS mt1 ON (deece_posts.ID = mt1.post_id)
> INNER JOIN deece_postmeta AS mt2 ON (deece_posts.ID = mt2.post_id) WHERE
> 1=1  AND deece_posts.post_type = 'match' AND (deece_posts.post_status =
> 'publish' OR deece_posts.post_status = 'private') AND
> (deece_postmeta.meta_key = 'match_date'
> OR  (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) = '120')
> OR  (mt2.meta_key = 'away_team' AND CAST(mt2.meta_value AS CHAR) = '120')
> ) GROUP BY deece_posts.ID ORDER BY deece_postmeta.meta_value ASC LIMIT 0,
> 10
>

>

> It should generate the following SQL with an additional AND and
> parentheses around the OR condition in the where , as below.
>
> REQUEST:SELECT SQL_CALC_FOUND_ROWS  deece_posts.ID FROM deece_posts
> INNER JOIN deece_postmeta ON deece_posts.ID = deece_postmeta.post_id
> INNER JOIN deece_postmeta AS mt1 ON (deece_posts.ID = mt1.post_id)
> INNER JOIN deece_postmeta AS mt2 ON (deece_posts.ID = mt2.post_id) WHERE
> 1=1  AND deece_posts.post_type = 'match' AND (deece_posts.post_status =
> 'publish' OR deece_posts.post_status = 'private') AND
> (deece_postmeta.meta_key = 'match_date' AND
> ( (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) = '120')
> OR  (mt2.meta_key = 'away_team' AND CAST(mt2.meta_value AS CHAR) =
> '120')) ) GROUP BY deece_posts.ID ORDER BY deece_postmeta.meta_value ASC
> LIMIT 0, 10

New description:

 I have the following arguments

 {{{
 $args3 = array(
     'post_type' => 'match',
     'orderby' => 'meta_value',
     'meta_key' => 'match_date',
     'order' => 'ASC',
     'meta_query' => array(
         array(
             'key'     => 'home_team',
             'value'   => $teamID,
             'compare' => '=' ,
         ),
         'relation' => 'OR',
          array(
             'key'     => 'away_team',
             'value'   => $teamID,
             'compare' => '=' ,
         ),
     ),
 );
 }}}

 Generates the following SQL which incorrectly OR's the match_date with
 home_team and away_team


 REQUEST:SELECT SQL_CALC_FOUND_ROWS  deece_posts.ID FROM deece_posts  INNER
 JOIN deece_postmeta ON deece_posts.ID = deece_postmeta.post_id
 INNER JOIN deece_postmeta AS mt1 ON (deece_posts.ID = mt1.post_id)
 INNER JOIN deece_postmeta AS mt2 ON (deece_posts.ID = mt2.post_id) WHERE
 1=1  AND deece_posts.post_type = 'match' AND (deece_posts.post_status =
 'publish' OR deece_posts.post_status = 'private') AND
 (deece_postmeta.meta_key = 'match_date'
 OR  (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) = '120')
 OR  (mt2.meta_key = 'away_team' AND CAST(mt2.meta_value AS CHAR) = '120')
 ) GROUP BY deece_posts.ID ORDER BY deece_postmeta.meta_value ASC LIMIT 0,
 10




 It should generate the following SQL with an additional AND and
 parentheses around the OR condition in the where , as below.

 REQUEST:SELECT SQL_CALC_FOUND_ROWS  deece_posts.ID FROM deece_posts  INNER
 JOIN deece_postmeta ON deece_posts.ID = deece_postmeta.post_id
 INNER JOIN deece_postmeta AS mt1 ON (deece_posts.ID = mt1.post_id)
 INNER JOIN deece_postmeta AS mt2 ON (deece_posts.ID = mt2.post_id) WHERE
 1=1  AND deece_posts.post_type = 'match' AND (deece_posts.post_status =
 'publish' OR deece_posts.post_status = 'private') AND
 (deece_postmeta.meta_key = 'match_date' AND
 ( (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) = '120')
 OR  (mt2.meta_key = 'away_team' AND CAST(mt2.meta_value AS CHAR) = '120'))
 ) GROUP BY deece_posts.ID ORDER BY deece_postmeta.meta_value ASC LIMIT 0,
 10

--

--
Ticket URL: <https://core.trac.wordpress.org/ticket/29604#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform