WP_Meta_Query's 'compare_key' does not support 'NOT EXISTS' when used with WP_User_Query

[gikaragia]

The following snippet:

<?php
function test_get_users() {
        $meta_query = array(
                'relation' => 'OR',
                array(
                        'key' => 'learner_calculated_version',
                        'value' => '3',
                        'compare' => '!=',
                ),
                array(
                        'key' => 'learner_calculated_version',
                        'compare_key' => 'NOT EXISTS',
                )
        );

        $user_args = array(
                'meta_query' => $meta_query
        );

        $users = get_users( $user_args );
        
        global $wpdb;
        print_r($wpdb->queries);
}

add_action('admin_init', 'test_get_users');

produces the following error:

WordPress database error: [Unknown column 'mt1.post_ID' in 'where clause']
SELECT DISTINCT wp_users.* 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 ) WHERE 1=1 AND ( ( wp_usermeta.meta_key = 'learner_calculated_version' AND wp_usermeta.meta_value != '3' ) OR NOT EXISTS (SELECT 1 FROM wp_postmeta mt2 WHERE mt2.post_ID = mt1.post_ID AND mt2.meta_key = 'learner_calculated_version' LIMIT 1) ) ORDER BY user_login ASC 

The issue is that in the subquery, wp_postmeta is queried instead of wp_usermeta.

[gikaragia]

I have prepared a patch for this issue which I will supply shortly.

[gikaragia]

Patch file

[gikaragia]

To test this, I used SAVEQUERIES configuration. After applying the patch, I observed that the following query is generated (which is correct):

 SELECT DISTINCT wp_users.* 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 ) WHERE 1=1 AND ( ( wp_usermeta.meta_key = 'learner_calculated_version' AND wp_usermeta.meta_value != '3' ) OR NOT EXISTS (SELECT 1 FROM wp_usermeta mt2 WHERE mt2.user_id = mt1.user_id AND mt2.meta_key = 'learner_calculated_version' LIMIT 1) ) ORDER BY user_login ASC

I also tried the patch with get_posts to ensure that it is not broken. The query for get_posts was unchanged after the patch and it was:

SELECT 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_postmeta.meta_key = 'learner_calculated_version' AND wp_postmeta.meta_value != '3' ) OR NOT EXISTS (SELECT 1 FROM wp_postmeta mt2 WHERE mt2.post_ID = mt1.post_ID AND mt2.meta_key = 'learner_calculated_version' LIMIT 1) ) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5

[fantasyworld]

#62041 was marked as a duplicate.

[hellofromTonya]

#62041 was marked as a duplicate.

[hellofromTonya]

As shared in a [https://core.trac.wordpress.org/ticket/62041#comment:3 duplicate ticket:

The last change to 'compare_key' in WP_Meta_Query was during 5.1 via #43346 / [46188]. Though I suspect its from an early version, I'll mark Version as 5.1 to denote what is currently known.