WordPress.org

Make WordPress Core

Opened 10 months ago

Last modified 10 months ago

#49284 new defect (bug)

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

Reported by: gikaragia Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 5.3.2
Component: Query Keywords: has-patch
Focuses: Cc:

Description

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.

Attachments (1)

class-wp-meta-query.diff (828 bytes) - added by gikaragia 10 months ago.
Patch file

Download all attachments as: .zip

Change History (3)

#1 @gikaragia
10 months ago

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

@gikaragia
10 months ago

Patch file

#2 @gikaragia
10 months ago

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
Note: See TracTickets for help on using tickets.