On Fri, 8 Mar 2013 15:06:31 -0300 Israel Lins Albuquerque <israelin...@yahoo.com.br> wrote:
> The problem is not comparisons the problem is when I do something > like this: CREATE TABLE tb (a REAL); > INSERT INTO tb (a) VALUES(0); > UPDATE tb SET a = a + 5.45; > UPDATE tb SET a = a + 16.9; > > SELECT a FROM tb; > > Gives visually right answer: 22.35 > But putting on a double variable gives me 22.3499948593433 (something > like that) and when declaring double a = 22.35 => gdb give me > 22.3499999999999 You wan to read up on the IEEE 754 floating point standard. I could recommend http://randomascii.wordpress.com/2013/02/07/float-precision-revisited-nine-digit-float-portability/. As others told you, 22.35 does not have an exact base 2 representation. It might help to know what numbers *can* be represented in near that number. I wrote a little program to illustrate. The library on my computer converts "22.35" to an 8-byte double whose bit pattern is 0x403659999999999a. Breaking it into its components, that turns out to mean 2^4 * (1.0 + 2^-52 * 0x659999999999a) The last 52 bits of those 64 are the fractional part, the mantissa. We know the last 4 of those 52 are 0x0a, or 10 decimal, or 0110 binary. What number do we get if we add/subtract one from that, making the final digits 0x09 or 0x0b? $ for N in 4036599999999999 403659999999999a 403659999999999b; do printf "$N:\t"; ./genfp -i $N; done 4036599999999999: 22.349999999999998 403659999999999a: 22.350000000000001 403659999999999b: 22.350000000000005 > CREATE TABLE tb (a REAL); > INSERT INTO tb (a) VALUES(0); > UPDATE tb SET a = a + 5.45; > UPDATE tb SET a = a + 16.9; ... > But putting on a double variable gives me 22.3499948593433 (something > like that) Be careful with that: your column is REAL, which is single-precision, only 4 bytes, of which only 23 are the mantissa, giving a decimal precision of only about 7 digits. When you add two REALs, you can't depend on more than 6 digits because of rounding error. In your example, 5.45 and 16.9 both have to be rounded in base 2, which explains why their sum is wrong even when rounded to 7 digits (22.34999). HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
