sql/pgwire: decimal loses precision when inserted with binary format #158093
Description
We seem to be losing some precision when inserting a value into a DECIMAL column when the value is sent using the binary format instead of text. Here's a pgwire test that shows the bug:
send
Query {"String": "DROP TABLE IF EXISTS t_dec_round_trip;"}
Query {"String": "CREATE TABLE t_dec_round_trip (k INT PRIMARY KEY, d DECIMAL);"}
----
until ignore=NoticeResponse
ReadyForQuery
ReadyForQuery
----
{"Type":"CommandComplete","CommandTag":"DROP TABLE"}
{"Type":"ReadyForQuery","TxStatus":"I"}
{"Type":"CommandComplete","CommandTag":"CREATE TABLE"}
{"Type":"ReadyForQuery","TxStatus":"I"}
send
Parse {"Name": "s3", "Query": "INSERT INTO t_dec_round_trip VALUES (1, $1);"}
Bind {"DestinationPortal": "p3", "PreparedStatement": "s3", "ParameterFormatCodes": [0], "Parameters": [{"text":"1.23000"}]}
Execute {"Portal": "p3"}
Sync
----
until
ReadyForQuery
----
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"CommandComplete","CommandTag":"INSERT 0 1"}
{"Type":"ReadyForQuery","TxStatus":"I"}
send
Parse {"Name": "s4", "Query": "INSERT INTO t_dec_round_trip VALUES (2, $1);"}
Bind {"DestinationPortal": "p4", "PreparedStatement": "s4", "ParameterFormatCodes": [1], "Parameters": [{"binary":"0002000000000005000108fc"}]}
Execute {"Portal": "p4"}
Sync
----
until
ReadyForQuery
----
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"CommandComplete","CommandTag":"INSERT 0 1"}
{"Type":"ReadyForQuery","TxStatus":"I"}
send
Parse {"Name": "s5", "Query": "SELECT * FROM t_dec_round_trip ORDER BY k"}
Bind {"DestinationPortal": "p5", "PreparedStatement": "s5", "ResultFormatCodes": [0]}
Execute {"Portal": "p5"}
Sync
----
until
ReadyForQuery
----
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"DataRow","Values":[{"text":"1"},{"text":"1.23000"}]}
{"Type":"DataRow","Values":[{"text":"2"},{"text":"1.2300"}]}
{"Type":"CommandComplete","CommandTag":"SELECT 2"}
{"Type":"ReadyForQuery","TxStatus":"I"}
send
Parse {"Name": "s6", "Query": "SELECT * FROM t_dec_round_trip ORDER BY k"}
Bind {"DestinationPortal": "p6", "PreparedStatement": "s6", "ResultFormatCodes": [1]}
Execute {"Portal": "p6"}
Sync
----
until
ReadyForQuery
----
{"Type":"ParseComplete"}
{"Type":"BindComplete"}
{"Type":"DataRow","Values":[{"binary":"0000000000000001"},{"binary":"0002000000000005000108fc"}]}
{"Type":"DataRow","Values":[{"binary":"0000000000000002"},{"binary":"0002000000000004000108fc"}]}
{"Type":"CommandComplete","CommandTag":"SELECT 2"}
{"Type":"ReadyForQuery","TxStatus":"I"}
send
Query {"String": "DROP TABLE IF EXISTS t_dec_round_trip;"}
----
until
ReadyForQuery
----
{"Type":"CommandComplete","CommandTag":"DROP TABLE"}
{"Type":"ReadyForQuery","TxStatus":"I"}
Note the results we get for the two inserted rows:
{"Type":"DataRow","Values":[{"text":"1"},{"text":"1.23000"}]}
{"Type":"DataRow","Values":[{"text":"2"},{"text":"1.2300"}]}
For some reason, the row inserted using binary format only has two trailing zeros.
Jira issue: CRDB-57092