WP_Query final SQL replaces int numbers to string in meta_query

[eddr]

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')

2. 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

[eddr]

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

[eddr]

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

[eddr]

proposed solution idea

[desrosj]

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.