A Dissabte, 6 d'agost de 2011 09:59:14, Cédric Krier va escriure: > Hi, > > Even with this patch we still have testing issue with MySQL backend. > Indeed the problem is that MySQL has rounding issue with float and decimal. > For float, I can accept because it is float, but on decimal test like this > should not fail [1] > I think this makes MySQL impossible to work in production environment. So I > propose to drop the support of MySQL for the next version of Tryton. What > do you think?
I didn't try Tryton tests so I don't know exactly which of them is failing but did some tests manually and although MySQL's behaviour is pretty odd, I think it works correctly, at least when the search using decimal fields is in SQL. You can take a look at the log of the session I did (attached) to see when it works and when it does not. The conclusion seems to be that one must cast using exactly the same definition as the field has. So if table creation looks like this: create table test (a decimal); Query must look like this: select * from test where a=cast(1.1 as decimal); If table creation looks like this: create table test (a decimal(25,20)); Query must look like this: select * from test where a=cast(1.1 as decimal(25,20)); Either that or simply cast both values: select * from test where cast(a as decimal)=cast(1.1 as decimal); It really sucks but it seems to work. > > [1] > http://hg.tryton.org/trytond/file/393318c8f257/trytond/tests/test_fields.py > #l576 -- Albert Cervera i Areny http://www.NaN-tic.com Tel: +34 93 553 18 03 http://twitter.com/albertnan http://www.nan-tic.com/blog -- [email protected] mailing list
mysql> create table test (a decimal); Query OK, 0 rows affected (0.03 sec) mysql> insert into test values(1.1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from test where a=1.1; Empty set (0.00 sec) mysql> select * from test where a=cast(1.1 as decimal); +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> select * from test; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> drop table test; Query OK, 0 rows affected (0.00 sec) mysql> create table test (a decimal(25,20)); Query OK, 0 rows affected (0.01 sec) mysql> select * from test; +------------------------+ | a | +------------------------+ | 1.10000000000000000000 | +------------------------+ 1 row in set (0.00 sec) mysql> select * from test where a=cast(1.1 as decimal); Empty set (0.00 sec) mysql> select * from test where a=cast(1.1 as decimal(25,20)); +------------------------+ | a | +------------------------+ | 1.10000000000000000000 | +------------------------+ 1 row in set (0.00 sec) mysql> select * from test where cast(a as decimal)=cast(1.1 as decimal); +------------------------+ | a | +------------------------+ | 1.10000000000000000000 | +------------------------+ 1 row in set (0.00 sec)
