Greater-than and less-than operators don't work when comparing decimal fractions

[skylarmt]

Describe the bug It appears that Medoo rounds decimals/floats to integers before comparing. This means that when searching for a record such as 5.5 using ["col[<]" => 5.6], Medoo will actually return values less than 5. When manually running the SQL query generated by $database->debug(), the expected result is returned.

Information

• Version of Medoo: 1.6.1
• Type of Database: MariaDB/MySQL
• System: Ubuntu Linux 18.04
• PHP version: 7.2.15

Sample Code

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL,
  `latitude` decimal(7,4) DEFAULT NULL,
  `longitude` decimal(7,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `accounts` (`id`, `latitude`, `longitude`) VALUES
(1, '46.1230', '111.3450'),
(2, '50.9870', '25.1340');

<?php
require "vendor/autoload.php";
use Medoo\Medoo;
$database = new Medoo([
    'database_type' => "mysql",
    'database_name' => "medoo_test",
    'server' => "localhost",
    'username' => "root",
    'password' => "xxxxx",
    'charset' => "utf-8"
]);
header("Content-Type: text/plain");

$where = ["AND" => [
    "latitude[>]" => 50.98,
    "longitude[>]" => 25.11,
    "latitude[<]" => 50.99,
    "longitude[<]" => 25.99
]];

echo "Medoo select() output:\n";
var_dump($database->select("accounts", ["id", "latitude", "longitude"], $where));

echo "\n\nRunning captured debug() output with query():\n";

ob_start();
$database->debug()->select("accounts", ["id", "latitude", "longitude"], $where);
$debugcmd = ob_get_contents();
ob_end_flush();
echo "\n";
var_dump($database->query($debugcmd)->fetchAll());

Output

Medoo select() output:
array(0) {
}

Running captured debug() output with query():
SELECT `id`,`latitude`,`longitude` FROM `accounts` WHERE (`latitude` > 50.98 AND `longitude` > 25.11 AND `latitude` < 50.99 AND `longitude` < 25.99)
array(1) {
  [0]=>
  array(6) {
    ["id"]=>
    string(1) "2"
    [0]=>
    string(1) "2"
    ["latitude"]=>
    string(7) "50.9870"
    [1]=>
    string(7) "50.9870"
    ["longitude"]=>
    string(7) "25.1340"
    [2]=>
    string(7) "25.1340"
  }
}

Expected output

The same data should be returned both times.

[skylarmt]

Possibly related to #694 and #797

[catfan]

You may consider using BETWEEN operator [><] and [<>] for this case. https://medoo.in/api/where

There are two latitude and longitude condition in the same array. That's will be conflict.

$where = ["AND" => [
    "latitude[><]" => [50.98, 50.99],
    "longitude[><]" => [25.11, 25.99]
]];

[skylarmt]

The thing is, it works fine if searching for 21 in the range 20 - 22, but breaks with 20.5 if searching with 20.1 - 20.9, because 20.1 becomes 20 and 20.9 becomes 20 (or so it seems).

Your [><] solution works, but what I did should work, and I know it did work as of at least version 1.1.3, thanks to some old code I dug up that did the exact same thing. That makes it a regression, and dangerous because it changes the expected results. The SQL code from $database->debug() works as expected too, so it's not good. My guess is that numbers are either being rounded down or cast to int somewhere.

[catfan]

@skylarmt Yes, I will fix for this.

[skylarmt]

Just FYI, I can confirm the [><] and [<>] operators are also affected. I thought it was working until I tested my app with different values, at which point Medoo started disagreeing with the expected database results.