branch: elpa/pg
commit dfe445ebe5dd84b5194a9455387594d7b49502d7
Author: Eric Marsden <eric.mars...@risk-engineering.org>
Commit: Eric Marsden <eric.mars...@risk-engineering.org>
Add tests for numeric datatype
---
test/test-pg.el | 39 ++++++++++++++++++++++++++++++++-------
1 file changed, 32 insertions(+), 7 deletions(-)
diff --git a/test/test-pg.el b/test/test-pg.el
index 014ea41e3b3..0d6150f4f70 100755
--- a/test/test-pg.el
+++ b/test/test-pg.el
@@ -24,9 +24,6 @@
;;
https://www.reidatcheson.com/floating%20point/comparison/2019/03/20/floating-point-comparison.html
-;;
-;; We were using (defun approx= (x y) (< (/ (abs (- x y)) (max (abs x) (abs
y))) 1e-5))
-;; which exposed us to division by zero.
(defun pgtest-approx= (x y)
(let ((smallest (min x y)))
(if (= (abs smallest) 0.0)
@@ -494,8 +491,10 @@
(should (pgtest-approx= -55.0 (scalar "SELECT $1" '((-55.0 . "float8")))))
(should (pgtest-approx= 42.0 (scalar "SELECT $1 + 1" '((41.0 .
"float4")))))
(should (pgtest-approx= 42.0 (scalar "SELECT $1 + 85.0" '((-43.0 .
"float4")))))
+ (should (pgtest-approx= 42.0 (scalar "SELECT $1 + 85.0" '((-43.0 .
"numeric")))))
(should (pgtest-approx= 42.0 (scalar "SELECT $1 + 1" '((41.0 .
"float8")))))
(should (pgtest-approx= 42.0 (scalar "SELECT $1 + 85" '((-43.0 .
"float8")))))
+ (should (pgtest-approx= 42.0 (scalar "SELECT $1 + 85" '((-43.0 .
"numeric")))))
(unless (member (pgcon-server-variant con) '(cratedb risingwave))
;; CrateDB returns an incorrect value ?8 here
(should (eql ?Q (scalar "SELECT $1" '((?Q . "char"))))))
@@ -516,8 +515,10 @@
(should (equal -1 (scalar "SELECT $1::int" '((-1 . "int4")))))
(should (eql 1.0e+INF (scalar "SELECT $1::float4" '((1.0e+INF .
"float4")))))
(should (eql 1.0e+INF (scalar "SELECT $1::float8" '((1.0e+INF .
"float8")))))
+ (should (eql 1.0e+INF (scalar "SELECT $1::float8" '((1.0e+INF .
"numeric")))))
(should (eql 0.0e+NaN (scalar "SELECT $1::float4" '((0.0e+NaN .
"float4")))))
(should (eql 0.0e+NaN (scalar "SELECT $1::float8" '((0.0e+NaN .
"float8")))))
+ (should (eql 0.0e+NaN (scalar "SELECT $1::numeric" '((0.0e+NaN .
"numeric")))))
;; CrateDB does not support the BYTEA type.
(unless (member (pgcon-server-variant con) '(cratedb))
(should (equal (byte-to-string 0)
@@ -827,7 +828,7 @@ bar$$"))))
ts TIMESTAMPTZ DEFAULT current_timestamp)")))
(pg-exec con "DROP TABLE IF EXISTS measurement")
(pg-exec con sql)
- (let* ((size 30) ;; 9988
+ (let* ((size 39)
(sensors (make-vector size nil))
(values (make-vector size 0.0))
(sql "INSERT INTO measurement(sensorid,value) SELECT * FROM
unnest($1::text[], $2::float8[])"))
@@ -836,6 +837,7 @@ bar$$"))))
(setf (aref values i) (cl-random 1000.0)))
(pg-exec-prepared con sql
`((,sensors . "_text") (,values . "_float8")))
+ (pgtest-flush-table con "measurement")
(let* ((res (pg-exec con "SELECT COUNT(*) FROM measurement"))
(row (pg-result res :tuple 0)))
(should (eql size (cl-first row))))
@@ -871,6 +873,7 @@ bar$$"))))
(dotimes (i size)
(setf (aref values i) i))
(pg-exec-prepared con sql `((,values . "_int2")))
+ (pgtest-flush-table con "sarray")
(let* ((res (pg-exec con "SELECT val FROM sarray ORDER BY val"))
(rows (pg-result res :tuples)))
(dotimes (i size)
@@ -878,12 +881,13 @@ bar$$"))))
(pg-exec con "DROP TABLE sarray"))
(when-let* ((sql (pgtest-massage con "CREATE TABLE sarray(id SERIAL
PRIMARY KEY, val int4)")))
(pg-exec con sql)
- (let* ((size 203)
+ (let* ((size 106)
(values (make-vector size nil))
(sql "INSERT INTO sarray(val) SELECT * FROM
unnest($1::int4[])"))
(dotimes (i size)
(setf (aref values i) i))
(pg-exec-prepared con sql `((,values . "_int4")))
+ (pgtest-flush-table con "sarray")
(let* ((res (pg-exec con "SELECT val FROM sarray ORDER BY val"))
(rows (pg-result res :tuples)))
(dotimes (i size)
@@ -897,6 +901,7 @@ bar$$"))))
(dotimes (i size)
(setf (aref values i) (- i)))
(pg-exec-prepared con sql `((,values . "_int8")))
+ (pgtest-flush-table con "sarray")
(let* ((res (pg-exec con "SELECT val FROM sarray ORDER BY val DESC"))
(rows (pg-result res :tuples)))
(dotimes (i size)
@@ -904,12 +909,13 @@ bar$$"))))
(pg-exec con "DROP TABLE sarray"))
(when-let* ((sql (pgtest-massage con "CREATE TABLE sarray(id SERIAL
PRIMARY KEY, val float4)")))
(pg-exec con sql)
- (let* ((size 103)
+ (let* ((size 67)
(values (make-vector size nil))
(sql "INSERT INTO sarray(val) SELECT * FROM
unnest($1::float4[])"))
(dotimes (i size)
(setf (aref values i) i))
(pg-exec-prepared con sql `((,values . "_float4")))
+ (pgtest-flush-table con "sarray")
(let* ((res (pg-exec con "SELECT val FROM sarray ORDER BY val"))
(rows (pg-result res :tuples)))
(dotimes (i size)
@@ -923,6 +929,7 @@ bar$$"))))
(dotimes (i size)
(setf (aref values i) i))
(pg-exec-prepared con sql `((,values . "_float8")))
+ (pgtest-flush-table con "sarray")
(let* ((res (pg-exec con "SELECT val FROM sarray ORDER BY val"))
(rows (pg-result res :tuples)))
(dotimes (i size)
@@ -930,12 +937,13 @@ bar$$"))))
(pg-exec con "DROP TABLE sarray"))
(when-let* ((sql (pgtest-massage con "CREATE TABLE sarray(id SERIAL
PRIMARY KEY, val TEXT)")))
(pg-exec con sql)
- (let* ((size 17)
+ (let* ((size 172)
(values (make-vector size nil))
(sql "INSERT INTO sarray(val) SELECT * FROM
unnest($1::text[])"))
(dotimes (i size)
(setf (aref values i) (format "%04d-value" i)))
(pg-exec-prepared con sql `((,values . "_text")))
+ (pgtest-flush-table con "sarray")
(let* ((res (pg-exec con "SELECT val FROM sarray ORDER BY val"))
(rows (pg-result res :tuples)))
(dotimes (i size)
@@ -1219,6 +1227,8 @@ bar$$"))))
(should (eql -1.0e+INF (scalar "SELECT '-Infinity'::float8")))
(should (isnan (scalar "SELECT 'NaN'::float4")))
(should (isnan (scalar "SELECT 'NaN'::float8")))
+ (should (pgtest-approx= (scalar "SELECT 100.0::numeric(30,20) +
500.0::numeric(30,20)") 600.0))
+ (should (pgtest-approx= (scalar "SELECT 0.000005::numeric(30,20) +
0.000005::numeric(30,20)") 0.00001))
;; The cube root operator
(unless (member (pgcon-server-variant con) '(cratedb materialize))
(should (pgtest-approx= 3.0 (scalar "SELECT ||/ float8 '27'"))))
@@ -2501,6 +2511,12 @@ bar$$"))))
;; numerical overflow on smallint
(scalar "SELECT (-32768)::int2 / (-1)::int2")
(pg-numeric-value-out-of-range 'ok))))
+ (should (eql 'ok (condition-case nil
+ (scalar "SELECT -12345::numeric(3)")
+ (pg-numeric-value-out-of-range 'ok))))
+ (should (eql 'ok (condition-case nil
+ (scalar "SELECT 2345.678::numeric(5,2)")
+ (pg-numeric-value-out-of-range 'ok))))
;; Yugabyte doesn't accept this input syntax for smallint, nor PostgreSQL
versions < 16
(unless (or (member (pgcon-server-variant con) '(yugabyte greenplum))
(< (pgcon-server-version-major con) 16))
@@ -2761,6 +2777,15 @@ bar$$"))))
(pg-exec con "INSERT INTO pgtest_check(a) VALUES
(-2)")
(pg-check-violation 'ok)))
(pg-exec con "DROP TABLE IF EXISTS pgtest_check"))))
+ (should (eql 'ok
+ (unwind-protect
+ (progn
+ (pg-exec con "CREATE TABLE pgtest_check_num(a INTEGER
PRIMARY KEY, b NUMERIC NOT NULL CHECK (b > 10))")
+ (pg-exec con "INSERT INTO pgtest_check_num(a,b)
VALUES(1, 15.0)")
+ (condition-case nil
+ (pg-exec con "INSERT INTO pgtest_check_num(a,b)
VALUES(2, 5.0)")
+ (pg-check-violation 'ok)))
+ (pg-exec con "DROP TABLE IF EXISTS pgtest_check_num"))))
;; As of 2025-02, yugabyte, CockroachDB and OrioleDB do not implement
EXCLUDE constraints.
(unless (member (pgcon-server-variant con) '(yugabyte cockroachdb
orioledb))
(should (eql 'ok
