Loss of trailing 0's receiving numeric data type in binary format

[dvarrazzo]

Describe the problem

Certain numeric values passed as binary parameters to CRDB lose trailing 0's. For instance, 10.0 is interpreted as 10.

To Reproduce

The following test script reproduces the issue with a few sample numbers. A more complete test runs in the psycopg 3 test suite: these are a subset of the values found failing in that test (other failing values likely have a similar pattern, just more 0's).

Script to reproduce the problem

Export the env var `DSN` as the connection string to a CRDB (e.g. `postgresql://root@localhost:26257/defaultdb?sslmode=disable`) ```python import os from decimal import Decimal import psycopg # pip install psycopg tests = ( "10.1" # working """ 10.0 100.00 1.1E+2 1000.000 1.00010 1.1E+3 10000 10000.0000 -10000 1.1E+4 """ ).split() with psycopg.connect(os.environ["DSN"]) as conn: conn.prepare_threshold = None cur = conn.cursor() for val in tests: cur.execute("select %b::text, %s::decimal::text", [Decimal(val), val]) data = cur._query.params[0].hex() got, want = cur.fetchone() print(f"{['NO','OK'][want == got]}: {want=}, {got=}, {data=}") ```

Output of the script

``` OK: want='10.1', got='10.1', data='0002000000000001000a03e8' NO: want='10.0', got='10', data='0001000000000001000a' NO: want='100.00', got='100', data='00010000000000020064' NO: want='1.1E+2', got='110', data='0001000000000000006e' NO: want='1000.000', got='1000', data='000100000000000303e8' NO: want='1.00010', got='1.0001', data='000200000000000500010001' NO: want='1.1E+3', got='1100', data='0001000000000000044c' NO: want='10000', got='1E+4', data='00010001000000000001' NO: want='10000.0000', got='1E+4', data='00010001000000040001' NO: want='-10000', got='-1E+4', data='00010001400000000001' NO: want='1.1E+4', got='11000', data='0002000100000000000103e8' ```

The script returns OK for every number if run against PostgreSQL, or against CRDB using the text format (placeholder %t instead of %b).

Note that, while it is probably possible for a number has several different binary representations, the one that psycopg is generating seems the same that CRDB generates, as can be seen with:

conn = psycopg.connect(os.environ["DSN"])
cur = conn.execute("select 10.0::decimal", binary=True)
print(cur.pgresult.get_value(0,0).hex())
# 0001000000000001000a

this might mean that, on a binary data copy, these values might get altered, but I'm not sure about that, I'll leave you to assess it.

Environment:

• CockroachDB version: v21.2.10
• Client app: psycopg 3.0.14

Jira issue: CRDB-16421

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/bulk-io (found keywords: import,Export)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[blathers-crl[bot]]

cc @cockroachdb/bulk-io

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!