关于 sqlda.pgc 中 numeric 数据类型精度丢失问题报告

From: tianhe zh <zhtianhe(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 关于 sqlda.pgc 中 numeric 数据类型精度丢失问题报告
Date: 2018-11-03 02:04:31
Message-ID: CABBNu3huvacH5R=U9GaFy9juPUhwD4bYgHRK=Z+nfqL3akoz9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

问题描述:
当 numeric 数值 小于 1.00 时:存在丢失小数点末尾精度;
问题展现:
1、路径:`pwd`
/home/pg9.6.10/postgresql-9.6.10/src/interfaces/ecpg/test/sql

2、比较:sqlda.pgc 与 sqldabug.pgc 的差别

`diff sqlda.pgc sqldabug.pgc`
90c90,92
< big bigint
---
> big bigint,
> d3 numeric(4,2),
> d4 numeric(4,3)
95,97c97,99
< (1, 'a', 1.0, 1, 'a',1111111111111111111),
< (2, null, null, null, null,null),
< (4, 'd', 4.0, 4, 'd',4444444444444444444);
---
> (1, 'a', 1.0, 1, 'a',1111111111111111111,0.09,0.009),
> (2, null, null, null, null,null,0.99,0.999),
> (4, 'd', 4.0, 4, 'd',4444444444444444444,1.01,1.001);

3、创建ecpg1_regression
` createdb ecpg1_regression `

4、将 'sqldabug sqldabug.c' 加入 Makefile 中
` cat Makefile `

subdir = src/interfaces/ecpg/test/sql
top_builddir = ../../../../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/$(subdir)/../Makefile.regress

TESTS = array array.c \
binary binary.c \
code100 code100.c \
copystdout copystdout.c \
define define.c \
desc desc.c \
sqlda sqlda.c \
describe describe.c \
dyntest dyntest.c \
dynalloc dynalloc.c \
dynalloc2 dynalloc2.c \
execute execute.c \
fetch fetch.c \
func func.c \
indicators indicators.c \
oldexec oldexec.c \
parser parser.c \
quote quote.c \
show show.c \
insupd insupd.c \
sqldabug sqldabug.c

all: $(TESTS)

oldexec.c: oldexec.pgc $(ECPG_TEST_DEPENDENCIES)
$(ECPG) -r questionmarks -o $@ $<

5、执行结果
`./sqldabug | grep -vE 'NO_PID|d2' | grep -E 'id|d[1-4]'`
name sqlda descriptor: 'id' value 1
name sqlda descriptor: 'd1' value NUMERIC '1.0'
name sqlda descriptor: 'd3' value NUMERIC '0.00' /* 精确值:0.09 */
name sqlda descriptor: 'd4' value NUMERIC '0.000' /* 精确值:0.009 */
name sqlda descriptor: 'id' value 2
name sqlda descriptor: 'd1' value NULL'
name sqlda descriptor: 'd3' value NUMERIC '0.90' /* 精确值:0.99 */
name sqlda descriptor: 'd4' value NUMERIC '0.990' /* 精确值:0.999 */
name sqlda descriptor: 'id' value 4
name sqlda descriptor: 'd1' value NUMERIC '4.0'
name sqlda descriptor: 'd3' value NUMERIC '1.01' /* 精确值:1.01 */
name sqlda descriptor: 'd4' value NUMERIC '1.001' /* 精确值:1.001 */

红:错误部分
绿:正确部分

Attachment Content-Type Size
sqldabug.pgc application/octet-stream 5.6 KB
关于 sqlda.pgc 中 numeric 数据类型精度丢失问题报告 application/octet-stream 2.5 KB

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-11-03 04:44:30 Re: BUG #15476: Problem on show_trgm with 4 byte UTF-8 characters
Previous Message Michael Paquier 2018-11-02 23:35:12 Re: Unable to copy large (>2GB) files using PostgreSQL 11 (Windows)