Invalid value after cast to real

[habibutsu]

• asyncpg version: 0.15.0
• PostgreSQL version: 10.4
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: Local
• Python version: 3.6.1
• Platform: MacOSX
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: --
• Can the issue be reproduced under both asyncio and
  uvloop?: Yes

$ docker run -d \
    --restart=always \
    --name=postgres \
    -p 5432:5432 \
    postgres:10.4

$ python -c "import sys; print(sys.version)"
3.6.1 (default, Apr 28 2017, 21:58:47)
[GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.42.1)]

$ cat > ./test.py <<EOF
import asyncio
import asyncpg

loop = asyncio.get_event_loop()
c = loop.run_until_complete(asyncpg.connect(host='0.0.0.0', user='postgres'))
r = loop.run_until_complete(c.fetchrow('select round(31/(86 + 1e-6), 6)::real as x'))
assert r['x'] == 0.360465, r
EOF

$ python test.py
Traceback (most recent call last):
  File "test.py", line 7, in <module>
    assert r['x'] == 0.360465, r
AssertionError: <Record x=0.36046499013900757>

$ psql -h 0.0.0.0 -U postgres -c 'select round(31/(86 + 1e-6), 6)::real as x'
    x
----------
 0.360465
(1 row)

$ # Notes: if you will use 'double precision' instead 'real' also everything ok

[elprans]

Python doesn't have a native single-precision floating point type, so asyncpg needs to cast the input value to double. Since 0.360465 cannot be represented exactly (neither as real, nor as double), you get a different representation with double precision.

>>> (0.36046499013900757).hex()
'0x1.711dbc0000000p-2'

In the above 0x1.711dbc is the 32-bit value as sent by the server, zero-padded when cast to a Python float.

Compare with:

>>> (0.360465).hex()
'0x1.711dbca9691a7p-2'

Note how extra information is needed to approximate to 0.360465 in a double precision value.

This is common issue, not specific to asyncpg. Below is a C program, showing that (float)0.360465 != (double)0.360465:

#include <stdio.h>

int main(void) {
        float f = 0.360465;
        double d = 0.360465;

        printf("%d\n", f == d);
}

Prints: 0

The solution here is to cast the result of your expression to double, or numeric, or text.

I'll add a note to the documentation regarding this.