meta_query fails with multiple clauses that have non-default 'compare' values

[boonebgorges]

It appears that when using 'meta_query' with multiple clauses, where those clauses have an explicit value set for 'compare', will cause the query to fail. This is true even when the 'compare' operator is '='. For example:

'meta_query' => array(
    'relation' => 'AND',
    array(
        'key' => 'foo',
        'value' => 'bar',
        'compare' => '=',
    ),
    array(
        'key' => 'foo2',
        'value' => 'bar2',
        'compare' => '=',
    ),
),

This will return no values, while removing the 'compare' params will make it work. This appears to be the case for all values of compare.

I'm attaching a number of unit tests that demonstrate this problem for a couple different values of 'relation' and 'compare'.

I haven't dug deep into the root cause, but at a glance, it appears that the SQL clauses are not being clustered into parentheses correctly - it's getting short-circuited by the 'compare' logic.

[boonebgorges]

fixes @ticket :)

[boonebgorges]

On further investigation, this problem is caused by r28659 #25538. I'm going to close this ticket and continue discussion on that ticket.

[nacin]

In 29650:

Meta Query: Revert [28659] (and [28665]) due to regressions.

props boonebgorges.
fixes #29285. see #25538.