Debian Codegraph @

test/fptest01.sql

raw
1 #!sqlite3
2 #
3 # 2026-03-01
4 #
5 # The author disclaims copyright to this source code. In place of
6 # a legal notice, here is a blessing:
7 #
8 # May you do good and not evil.
9 # May you find forgiveness for yourself and forgive others.
10 # May you share freely, never taking more than you give.
11 #
12 #***********************************************************************
13 #
14 # Floating-point <-> text conversions
15 #
16 # FAILURES IN THIS SCRIPT ARE NOT NECESSARILY THE FAULT OF SQLITE.
17 #
18 # Some of the tests below use the system strtod() function as
19 # an oracle of truth. These tests assume that the system strtod()
20 # is always correct. That is the case for Win11, Macs, most Linux
21 # boxes and so forth. But it possible to find a machine for which
22 # is not true. (One example, is Macs from around 2005.) On such
23 # machines, some of these tests might fail.
24 #
25 # So, in other words, a failure in any of the tests below does not
26 # necessarily mean that SQLite is wrong. It might mean that the
27 # strtod() function in the standard library of the machine on which
28 # the test is running is wrong.
29 #
30
31 # Verify that binary64 -> text -> binary64 conversions round-trip
32 # successfully for 98,256 different edge-case binary64 values. The
33 # query result is all cases that do not round-trip without change,
34 # and so the query result should be an empty set.
35 #
36 .testcase 100
37 .mode list
38 WITH
39 i1(i) AS (VALUES(0) UNION ALL SELECT i+1 FROM i1 WHERE i<15),
40 i2(j) AS (VALUES(0) UNION ALL SELECT j+1 FROM i2 WHERE j<0x7fe),
41 i3(k) AS (VALUES(0x0000000000000000),
42 (0x000ffffffffffff0),
43 (0x0008080808080800)),
44 fpint(n) AS (SELECT (j<<52)+i+k FROM i2, i1, i3),
45 fp(n,r) AS (SELECT n, ieee754_from_int(n) FROM fpint)
46 SELECT n, r FROM fp WHERE r<>(0.0 + (r||''));
47 .check ''
48
49 # Another batch of 106,444 edge cases: All postiive floating point
50 # values that have only a single bit set in the mantissa part of the
51 # number.
52 #
53 .testcase 110
54 WITH
55 i1(i) AS (VALUES(0) UNION ALL SELECT i+1 FROM i1 WHERE i<51),
56 i2(j) AS (VALUES(0) UNION ALL SELECT j+1 FROM i2 WHERE j<0x7fe),
57 fpint(n) AS (SELECT (j<<52)+(1<<i) FROM i2, i1),
58 fp(n,r) AS (SELECT n, ieee754_from_int(n) FROM fpint)
59 SELECT n, r FROM fp WHERE r<>(0.0 + (r||''));
60 .check ''
61
62 # Verify that text -> binary64 conversions agree with system strtod().
63 # for 98,256 different edge-cases.
64 #
65 .testcase 200
66 .mode list
67 WITH
68 i1(i) AS (VALUES(0) UNION ALL SELECT i+1 FROM i1 WHERE i<15),
69 i2(j) AS (VALUES(0) UNION ALL SELECT j+1 FROM i2 WHERE j<0x7fe),
70 i3(k) AS (VALUES(0x0000000000000000),
71 (0x000ffffffffffff0),
72 (0x0008080808080800)),
73 fpint(n) AS (SELECT (j<<52)+i+k FROM i2, i1, i3),
74 fp(r) AS (SELECT ieee754_from_int(n) FROM fpint)
75 SELECT r FROM fp WHERE r<>strtod(r||'');
76 .check ''
77
78
79 # Another batch of 106,444 edge cases: All postiive floating point
80 # values that have only a single bit set in the mantissa part of the
81 # number.
82 #
83 .testcase 210
84 WITH
85 i1(i) AS (VALUES(0) UNION ALL SELECT i+1 FROM i1 WHERE i<51),
86 i2(j) AS (VALUES(0) UNION ALL SELECT j+1 FROM i2 WHERE j<0x7fe),
87 fpint(n) AS (SELECT (j<<52)+(1<<i) FROM i2, i1),
88 fp(r) AS (SELECT ieee754_from_int(n) FROM fpint)
89 SELECT r FROM fp WHERE r<>strtod(r||'');
90 .check ''
91
92 # Comparing SQLite's text-to-double conversion against strtod()
93 # for 200,000 random floating-point literals.
94 #
95 .param set $N 50_000
96 .testcase 300
97 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
98 VALUES(1,'1234.5789')
99 UNION ALL
100 SELECT n+1, format('%.*s.%.*se%+d',
101 (n%3)+1,
102 random()%1000,
103 abs(random()%16)+1,
104 abs(random()),
105 random()%308)
106 FROM fp WHERE n<$N
107 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
108 .check ''
109 .testcase 301
110 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
111 VALUES(1,'1234.5789')
112 UNION ALL
113 SELECT n+1, format('%.*s.%.*s',
114 (n%3)+1,
115 random()%1000,
116 abs(random()%16)+1,
117 abs(random()))
118 FROM fp WHERE n<$N
119 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
120 .check ''
121 .testcase 302
122 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
123 VALUES(1,'1234.5789')
124 UNION ALL
125 SELECT n+1, format('%.*s.%.*s',
126 abs(random()%7)+1,
127 random(),
128 abs(random()%10)+1,
129 abs(random()))
130 FROM fp WHERE n<$N
131 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
132 .check ''
133 .testcase 303
134 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
135 VALUES(1,'1234.5789')
136 UNION ALL
137 SELECT n+1, format('%de%+03d',
138 random(),
139 random()%308)
140 FROM fp WHERE n<$N
141 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
142 .check ''
143
144 # Another 500,000 comparisions between SQLite and strtod() from completely
145 # random floating point literals.
146 #
147 .testcase 310
148 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
149 SELECT 1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
150 UNION ALL
151 SELECT n+1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
152 FROM fp WHERE n<$N*2
153 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
154 .check ''
155 .testcase 311
156 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
157 SELECT 1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
158 UNION ALL
159 SELECT n+1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
160 FROM fp WHERE n<$N*2
161 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
162 .check ''
163 .testcase 312
164 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
165 SELECT 1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
166 UNION ALL
167 SELECT n+1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
168 FROM fp WHERE n<$N*2
169 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
170 .check ''
171 .testcase 313
172 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
173 SELECT 1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
174 UNION ALL
175 SELECT n+1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
176 FROM fp WHERE n<$N*2
177 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
178 .check ''
179 .testcase 314
180 WITH RECURSIVE fp(n,x) AS MATERIALIZED (
181 SELECT 1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
182 UNION ALL
183 SELECT n+1, format('%+.*g',abs(random()%18), ieee754_from_int(random()))
184 FROM fp WHERE n<$N*2
185 ) SELECT x FROM fp WHERE (x+0.0)<>strtod(x);
186 .check ''