Complex WP_User_Query call fails on production server

Question

I'm having a very specific and hard to debug problem with a complex meta_query in a WP_User_Query call:

I'm using this (simplified) code to built the query:

// custom user role
$arguments = array(
    'role' => 'member'
);

// number and offset
$arguments['number'] = 10;
$arguments['offset'] = 0;

$meta = array();
$meta['relation'] = 'AND';

// filter one (array)
$meta[] = array(
   'key'       => 'field-one',
   'value'     => $values,
   'compare'   => 'IN'
);

// filter two
$meta[] = array(
    'key'       => 'field-two',
    'value'     => 0,
    'compare'   => '='
);

// filter three
$meta[] = array(
    'key'       => 'field-three',
    'value'     => 0,
    'compare'   => '='
);

// even more filter
foreach( $filter as $key => $value ) {
    $meta[] = array(
        'key'     => 'field-' . $key,
        'value'   => $value,
        'compare' => 'LIKE'
    );
}

$arguments['meta_query'] = $meta;

// orderby
$arguments['meta_key'] = 'last_name';       
$arguments['orderby']  = 'meta_value'; 

$query = new WP_User_Query(
    $arguments
);

On my localhost (MySQL version 5.6.23) it correctly returns results and the correct $query->total_users. On the production site (MySQL version 5.5.42-37.1-log) $query->get_results() is empty, but the $query->total_users shows an arbitrary number.

When I print the WP_User_Query object I can inspect the resulting SQL query, which is identical both on my localhost and on the production server:

DISTINCT SQL_CALC_FOUND_ROWS wp_users.* [query_from] => FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) INNER JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id ) INNER JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) INNER JOIN wp_usermeta AS mt5 ON ( wp_users.ID = mt5.user_id ) [query_where] => WHERE 1=1 AND ( ( ( wp_usermeta.meta_key = 'last_name' AND ( ( mt1.meta_key = 'field-one' AND CAST(mt1.meta_value AS CHAR) IN ('professional','member','junior') ) AND ( mt2.meta_key = 'field-two' AND CAST(mt2.meta_value AS CHAR) = '0' ) AND ( mt3.meta_key = 'field-three' AND CAST(mt3.meta_value AS CHAR) = '0' ) AND ( mt4.meta_key = 'field-four' AND CAST(mt4.meta_value AS CHAR) LIKE '%my-value%' ) ) ) AND ( mt5.meta_key = 'wp_capabilities' AND CAST(mt5.meta_value AS CHAR) LIKE '%\"member\"%' ) ) ) [query_orderby] => ORDER BY wp_usermeta.meta_value ASC [query_limit] => LIMIT 20 )

Any idea what could be wrong with this?

Answer 1

When executing the generated SQL statement in MySQL it gave me

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

To fix that I had to call $wpdb->query( 'SET OPTION SQL_BIG_SELECTS = 1' ); right before calling WP_User_Query.