BETWEEN与范围包括'1'也返回'10'

Question

I'm getting some unexpected behaviour using a BETWEEN predicate in ZF2. Consider the following code:

$ranges = array(
            array(
                'min' => 2,
                'max' => 4
            ),
            array(
                'min' => 7,
                'max' => 9
            )
);

$predicates = array();

foreach($ranges as $range){
    // create a new 'Between' predicate and add it to array
    $between = new Predicate\Between('my_field', $range['min'], $range['max']);
    array_push($predicates, $between);
}

// add predicateset
$select->where->addPredicate(
    new Predicate\PredicateSet(
                            $predicates,
                            Predicate\PredicateSet::COMBINED_BY_OR
                       )
);

Now all of this works fine, except when using a range including 1 (eg. 0-2, 1-5 etc). In that case the resultset also includes values of 10

However, this does not happen when I try something like the query below directy on the DB:

SELECT * FROM my_table WHERE my_field BETWEEN 0 AND 5

(my_field is a varchar in this case)

What am I missing here?

Answer 1

After some digging through the ZF2 libraries, I found out that the Between predicate, always sets the input values to a TYPE_VALUE, which basically means the values get wrapped in quotes.

One solution would be to change the Between class in the library to use a TYPE_LITERAL, but I think its bad practice to mess around with the ZF2 library.

In the end I got it to work by setting a custom specification for the predicate, for which ZF2 provides a method setSpecification. This will cast the values back to an integer.

//...

    $between = new Predicate\Between('my_field', $range['min'], $range['max']);

    $between->setSpecification('%1$s BETWEEN CAST(%2$s AS UNSIGNED) AND CAST(%3$s AS UNSIGNED)');

//...

Maybe not the prettiest solution, but it works for what I need.