Wordpress won't select rows when using `BETWEEN 0 and 100000`

Question

I have a Wordpress installation that lists properties for sale. I'm trying to find properties between 2 values.

The problem I have is when I do something like:

SELECT SQL_CALC_FOUND_ROWS  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 )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = 'type_of_property' AND wp_postmeta.meta_value IN ('semidetached') ) 
  AND 
  ( mt1.meta_key = 'property_value' AND mt1.meta_value BETWEEN '0' AND '100000' )
) AND wp_posts.post_type = 'shared_ownership' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

It only returns 3 rows. However, if I change it to something like this:

SELECT SQL_CALC_FOUND_ROWS  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 )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = 'type_of_property' AND wp_postmeta.meta_value IN ('semidetached') ) 
  AND 
  ( mt1.meta_key = 'property_value' AND mt1.meta_value BETWEEN '0' AND '99999' )
) AND wp_posts.post_type = 'shared_ownership' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

It returns 13 rows as expected. The only difference here is the maximum number, and I can't quite understand why there is such a huge difference between 99999 and 100000 in terms of results from the database.

Is wordpress just ignoring the extra zeros? Does it not see it as a number? I'm not sure on how to go about fixing this one.

EDIT:

Below is an example of how I'm building this query using advanced custom fields etc.

$args = array(
        'post_type' => $propertyType[0],
        'paged'=>$paged,
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'type_of_property',
                'value' => $data[0],
            ),
            array(
                'key' => $propertyValue,
                'value' => array($data[3], $data[4]),
                'compare' => 'BETWEEN'
            ),
            array(
                'key' => 'number_of_bedrooms',
                'value' => $data[2],
                'compare' => 'IN'
            ),
            array(
                'key' => 'town_index',
                'value' => $data[1],
                'compare' => 'IN'
            ),
        )
    );
}
$loop = new WP_Query( $args );

$data is an array. $data[3] and $data[4] are equal to the values I mentioned above, just populated from a form POST.

What happens when you run that SQL statement directly in MySQL workbench or PHPMyAdmin? — MonkeyZeus, Oct 19 '16 at 14:40
Can't imagine it's this simple, but what about removing the single quotes? To your "extra zeroes" hypothesis, what about changing it to 100001? What happens then? — Cameron Hurd, Oct 19 '16 at 14:40
@CameronHurd I bet your hypothesis is correct but I wonder if `mt1.meta_value` is not a numeric field. — MonkeyZeus, Oct 19 '16 at 14:42
@AndyHolmes So WP is not the issue, the issue rests in the SQL syntax. — MonkeyZeus, Oct 19 '16 at 14:43
@CameronHurd Insane, removing the single quotes seems to work. Do we need to change the column type? The only thing is the `meta_value` field can hold alphanumeric chars as it's not just used for pricing — Andy Holmes, Oct 19 '16 at 14:44
@MonkeyZeus yes but that query is the result of doing a `print_r` on the `$loop->request` - Its WP's query — Andy Holmes, Oct 19 '16 at 14:45
`meta_value` is a `LONGTEXT`, try to `CAST` it to `INTEGER`. I think `BETWEEN` only works on scalar values and dates/times. Like, you can't select something between "a" and "b". — Daniel W., Oct 19 '16 at 14:46
I'm no WP expert and I am sure there is an oversight somewhere in WP's core but anyways, MySQL's `between` clause is probably not really designed to handle alphanumeric shenanigans. You will have to play around with [`CAST()`](http://stackoverflow.com/q/13297652/2191572) — MonkeyZeus, Oct 19 '16 at 14:47
@DanFromGermany I understand, but we're building a loop like this: `$loop = new WP_Query( $args );` - How can I cast to integer prior to the loop, just run a separate SQL query above the loop? — Andy Holmes, Oct 19 '16 at 14:48
Based on their [docs](https://codex.wordpress.org/Class_Reference/WP_Query) you can try manually building the query yourself because `new WP_Query()` accepts a SQL string. — MonkeyZeus, Oct 19 '16 at 14:52
@MonkeyZeus so instead of the system doing that huge query, I'll have to put it in manually instead? — Andy Holmes, Oct 19 '16 at 14:53
I am not sure what your `$args` looks like but could you add the `print_r($args);` specifically for the part which is responsible for setting the between clause for `meta_value`? — MonkeyZeus, Oct 19 '16 at 14:56
@MonkeyZeus I've just popped that info in the Q as I thought it would be beneficial :) — Andy Holmes, Oct 19 '16 at 14:57
Nice, I have to go for now but I will check out this post in about 30 minutes so hold tight...unless of course someone else answers lol — MonkeyZeus, Oct 19 '16 at 14:57
Nice! Glad I could help everyone provide answers on the correct path rather than continue to provide blind theories and conjecture :-) — MonkeyZeus, Oct 19 '16 at 15:37

score 2 · Accepted Answer · edited May 23 '17 at 11:45

2

Try removing the single quotes. I believe SQL is unsure of how to "type" those values for comparison. (string vs. scalar, as @danfromgermany points out in the comments.)

To test your "extra zeroes" hypothesis, which would prove this: try changing the 10000 value to 100001. As you suspect, SQL might be better able to interpret the query without the right-padded 0s.

Edit: To get that fine-grain control over your query that avoids WP applying single quotes, you might just use the $wpdb object.

From https://codex.wordpress.org/Class_Reference/wpdb:

Using the $wpdb Object

WordPress provides a global object variable, $wpdb, which is an instantiation of the wpdb class defined in /wp-includes/wp-db.php By default, $wpdb is instantiated to talk to the WordPress database. To access $wpdb in your WordPress PHP code, declare $wpdb as a global variable using the global keyword, or use the superglobal $GLOBALS in the following manner:
global $wpdb;
$results = $wpdb->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

Edit #2:

Try assigning a key/value of type as NUMERIC in the meta_query pieces of your $args array:

(Gleaned from from https://codex.wordpress.org/Class_Reference/WP_Meta_Query#Accepted_Arguments)

$args = array(
    // ...
    'meta_query' => array(
        'relation' => 'AND',
        // ...
        array(
            'key' => $propertyValue,
            'value' => array($data[3], $data[4]),
            'type' => 'NUMERIC',
            'compare' => 'BETWEEN'
        ),
        //...
    )
);

edited May 23 '17 at 11:45

Community

1
1

answered Oct 19 '16 at 14:45

Cameron Hurd

4,836
1
22
31

1

This is not an answer, it's a comment. – Daniel W. Oct 19 '16 at 14:47
In Cameron's defense, removing the quotes did work, but the query is being built by WP so can't modify it as easily – Andy Holmes Oct 19 '16 at 14:48
1

Sorry, what's the distinction, here? I'll recant my question/comment if you can explain to me why it's not an answer. Would you prefer I just word it more authoritatively? – Cameron Hurd Oct 19 '16 at 14:49
I guess he's going to turn his comment-answer into a real solution answer so I withdraw the downvote – Daniel W. Oct 19 '16 at 14:50
Awfully prickly. Still not sure why it can't be an answer, as well. Dan - I think your comment was also very insightful. If it was an answer, I'd upvote it! – Cameron Hurd Oct 19 '16 at 14:52
Forever trying to win internet stranger's approval, I've edited my comment-answer. I added a method through which you might apply the principal of mine and Dan's proposed solutions. :) – Cameron Hurd Oct 19 '16 at 14:57
Cheers Cameron. Running the query like that seems to detach from the point of the `$args` though? Is there no other way to send these values as integers other than putting the entire query inside `get_results` ? – Andy Holmes Oct 19 '16 at 15:06
Hey - had a look at the `WP_Meta_Query` class and made another proposal for how you might get WP to cast value as integers. – Cameron Hurd Oct 19 '16 at 15:20
Literally just posted the same thing! haha. I set it to decimal (haven't tried numeric) and it works fine :) I'll mark yours as accepted, thank you – Andy Holmes Oct 19 '16 at 15:23
1

JINX. ;) Good sleuthing all around. Cheers, mate. – Cameron Hurd Oct 19 '16 at 15:24
Just think something like "should avoid x and try y" is not an answer. But it can turn into an answer later "avoid x and use y instead". Anyways, maybe my opinion is too strict :-) Upvote for your answer! – Daniel W. Oct 20 '16 at 13:45
Might be; might not be. I appreciate the dialogue, regardless. I try not to be guided by gamesmanship on here, and usually don't [begin an] answer unless I believe I've got a handle on the core of the problem. Nonetheless, I'll think twice in the future. Hope future readers of this thread find value in it. – Cameron Hurd Oct 20 '16 at 13:55

score 1 · Answer 2 · answered Oct 19 '16 at 15:22

Okay so I tried all the suggested ideas, and they worked fine. However, because we are using Advanced Custom Fields and running a query with the $args ACF suggests, apparently you can just do something like this:

$args = array(
        'post_type' => $propertyType[0],
        'paged'=>$paged,
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'type_of_property',
                'value' => $data[0],
            ),
            array(
                'key' => $propertyValue,
                'value' => array($data[3], $data[4]),
                'type' => 'DECIMAL',
                'compare' => 'BETWEEN'
            ),
            array(
                'key' => 'number_of_bedrooms',
                'value' => $data[2],
                'compare' => 'IN'
            ),
            array(
                'key' => 'town_index',
                'value' => $data[1],
                'compare' => 'IN'
            ),
        )
    );
}
$loop = new WP_Query( $args );

So defining a type of DECIMAL on the arguments, it auto cast the field for me to this excerpt: mt1.meta_key = 'property_value' AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '0' AND '445295' )

score 1 · Answer 3 · answered Oct 19 '16 at 15:27

Your values are being compared as STRINGS, which means which string comparison rules apply:

'2' > '3' -> false
'2' > '1999999999' -> true

In your case:

  '2' BETWEEN '0' and '10000'

executes as

  ('0' <= '2') && ('2' < '10000')
      (true)   &&    (false)
             false

and for the '2'<'10...', going char by char:

  '2' < '10000'
   a     abcde

runs as:

  ('2' < '1') && (null < '0') && (null < '0') etc...
       a               b               c
  (false) && (false) && (false) ....
  false

If it helps, just convert your numbers into letters:

 1234567890
 abcdefgjij

 '2' BETWEEN '1' AND '100000000'
 'B' BETWEEN 'A' AND 'AJJJJJJJJ'

which obviously works out to false

Wordpress won't select rows when using `BETWEEN 0 and 100000`

3 Answers3

Using the $wpdb Object