Management of float values

[leogr]

    $where['geodist <= ?'] = (float) $distance;

It works With float like 203.620805, but it doesn't with a float like 40.0000.

PHP Version: 5.5.3 (pdo in bundle) Sphinx version: 2.0.9-release (rel20-r4115)

[leogr]

Also present in other versions, tested with Sphinx 2.1.4-release (rel21-r4421)

Step to reproduce:

$rowset = $search->search('foo', function(Select $select) {
        $select->where(array('f1' => 10.0));
    });

PDO error: Statement could not be executed (42000 - 1064 - index foo: unsupported filter type 'intvalues' on float column)

[leodido]

It may be useful to debug how SphinxQL queries are internally translated and executed. So change the query_log_format global variable value first of all.

As follows:

SET GLOBAL query_log_format = sphinxql; /* default is plain */

Then execute this two queries

SELECT * FROM foo WHERE f1 = 12.2; /* (q1) */
SELECT * FROM foo WHERE f1 = 12; /* (q2) */

And compare the SphinxQL in the query log file

tail -2 /var/log/sphinx/query.log`

Output:

SELECT * FROM foo WHERE f1 BETWEEN 12.200000 AND 12.200000;
SELECT * FROM foo WHERE f1=12; /* error=index foo: unsupported filter type 'intvalues' on float column */

---

So, a temporary workaround (probably not the better) can be to change queries which WHERE clause regards one or mode float fields.

E.g., query (q2) can be successfully executed rewriting it as:

SELECT * FROM foo WHERE f1 BETWEEN 12 and 12;

---

@leogr: what do you think about it ?

[leodido]

In this context it may be useful to investigate other variables too (e.g., SQL_MODE).

Variables (global and not) documentation.

Modes documentation.

[leogr]

It can't be changed automagically from 'identifier = ?' to 'identifier BETWEEN ? and ?' because ->where API allows a string expression, not just a single predicate at a time.

So, we can't introduce a a library side workaround at moment.

[leodido]

:+1:

[leodido]

Other inherent reports that checks if 12 (or any other integer) is handled and/or inserted as a float value when the column type is float.

---

UPDATE `foo` SET f1 = 12 WHERE id = 1;

Works.

---

SELECT id FROM `foo` GROUP BY c1 HAVING f1 >= 12;

Works.

---

SELECT id FROM `foo` GROUP BY c1 HAVING f1 = 12;

Does not work.

[leogr]

Other troubles with float precision:

mysql> insert into foo (id, f1) values (1, 55.55);
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo;
+------+------+------+------+------+------+-----------+------+
| id   | baz  | bam  | c1   | c2   | c3   | f1        | bar  |
+------+------+------+------+------+------+-----------+------+
|    1 |    0 |    0 |    0 |    0 |    0 | 55.549999 |      |
+------+------+------+------+------+------+-----------+------+
1 row in set (0.00 sec)

mysql> select * from foo where f1 = 55.55;
+------+------+------+------+------+------+-----------+------+
| id   | baz  | bam  | c1   | c2   | c3   | f1        | bar  |
+------+------+------+------+------+------+-----------+------+
|    1 |    0 |    0 |    0 |    0 |    0 | 55.549999 |      |
+------+------+------+------+------+------+-----------+------+
1 row in set (0.00 sec)