WordPress.org

Make WordPress Core

Opened 16 months ago

Closed 14 months ago

Last modified 13 months ago

#47286 closed defect (bug) (invalid)

WP_Query final SQL replaces int numbers to string in meta_query

Reported by: eddr Owned by:
Milestone: Priority: normal
Severity: normal Version: 5.2
Component: Query Keywords: reporter-feedback
Focuses: Cc:

Description (last modified by SergeyBiryukov)

WP_Query meta_query numeric value results in comparison to a string value

  1. <?php
    function intercept_query_clauses( $pieces )
    {
            var_dump( $pieces );
            return $pieces;
    }
    add_filter( 'posts_where', 'intercept_query_clauses', 1, 1 );
    $query_args = [
            'meta_query' => [[
                    'key'     => 'key',
                    'value'   => 10,
                    'compare' => "="
                    
            ]]
            , 'fields' => 'ids'
            
    ];
    $query = new WP_Query( $query_args );
    

result:

AND ( 
  ( wp_postmeta.meta_key = 'key' AND wp_postmeta.meta_value = '10' )
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
  1. using 'type' => numeric results in
AND ( 
  ( wp_postmeta.meta_key = 'key' AND CAST(wp_postmeta.meta_value AS SIGNED) = '10' )
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')"

which doesn't solve the problem + why casting the field and not the value?

Am I missing something big here?

Thanks

Attachments (1)

wp_query patch (1.8 KB) - added by eddr 15 months ago.
proposed solution idea

Download all attachments as: .zip

Change History (8)

#1 @SergeyBiryukov
16 months ago

  • Description modified (diff)

#2 @eddr
16 months ago

Hi, can someone review it? I want to post a patch, but I want to be sure I'm not wrong about this bug.

#3 @eddr
15 months ago

My proposed solution is to take into account the meta query "type" field for formatting the value. In case of - say - "INTEGER", the value will be formatted as "%d", otherwise, can remain as it is now - "%s"

Added the patch file. This is just to give an idea and need fixing. As described, when meta query includes type => "INTEGER", the formatting will be "%d" and SQL will be correct for this case

Last edited 15 months ago by eddr (previous) (diff)

@eddr
15 months ago

proposed solution idea

This ticket was mentioned in Slack in #core by sticky. View the logs.


15 months ago

#5 @eddr
14 months ago

  • Resolution set to invalid
  • Status changed from new to closed

#6 @desrosj
13 months ago

  • Keywords reporter-feedback added

Hi @eddr,

Thanks for this ticket! I'm sorry that it took so long to receive a response.

I see you closed the ticket and marked it as invalid. Is this because you were able to figure out the issue? I just want to make sure that closing the ticket was intentional. In the future, please make sure to include a detailed message whenever updating a ticket (especially when closing) so that others can follow the thought process.

In MySQL, UNSIGNED and SIGNED represent types integers. UNSIGNED can only be a positive integer, where as SIGNED can be a positive or negative integer. Because the meta_value key is a longtext type, the casting ensures the values are looked at as integers in the query.

As far as I know, all of the unit tests are still passing and I have not seen any other reports that there is something going on with integer meta value queries. I also did some testing in a local install and was unable to produce any incorrect behaviors.

#7 @desrosj
13 months ago

  • Milestone Awaiting Review deleted
Note: See TracTickets for help on using tickets.