1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193
|
# 2017 December 9
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix decimal
if {[catch {load_static_extension db decimal} error]} {
puts "Skipping decimal tests, hit load error: $error"
finish_test; return
}
do_execsql_test 1000 {
SELECT decimal(1);
} {1}
do_execsql_test 1010 {
SELECT decimal('1.0');
} {1.0}
do_execsql_test 1020 {
SELECT decimal('0001.0');
} {1.0}
do_execsql_test 1030 {
SELECT decimal('+0001.0');
} {1.0}
do_execsql_test 1040 {
SELECT decimal('-0001.0');
} {-1.0}
do_execsql_test 1050 {
SELECT decimal('1.0e72');
} {1000000000000000000000000000000000000000000000000000000000000000000000000}
# 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123
do_execsql_test 1060 {
SELECT decimal('1.0e-72');
} {0.0000000000000000000000000000000000000000000000000000000000000000000000010}
# 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123
do_execsql_test 1070 {
SELECT decimal('-123e-4');
} {-0.0123}
do_execsql_test 1080 {
SELECT decimal('+123e+4');
} {1230000}
do_execsql_test 1081 {
SELECT decimal_exp('+123e+4');
} {+1.23e+06}
do_execsql_test 2000 {
CREATE TABLE t1(seq INTEGER PRIMARY KEY, val TEXT);
INSERT INTO t1 VALUES
(1, '-9999e99'),
(2, '-9998.000e+99'),
(3, '-9999.0'),
(4, '-1'),
(5, '-9999e-20'),
(6, '0'),
(7, '1e-30'),
(8, '1e-29'),
(9, '1'),
(10,'1.00000000000000001'),
(11,'+1.00001'),
(12,'99e+99');
SELECT *, '|'
FROM t1 AS a, t1 AS b
WHERE a.seq<b.seq
AND decimal_cmp(a.val,b.val)>=0;
} {}
do_execsql_test 2010 {
SELECT *, '|'
FROM t1 AS a, t1 AS b
WHERE a.seq<>b.seq
AND decimal_cmp(a.val,b.val)==0;
} {}
do_execsql_test 2020 {
SELECT *, '|'
FROM t1 AS a, t1 AS b
WHERE a.seq>b.seq
AND decimal_cmp(a.val,b.val)<=0;
} {}
do_execsql_test 2030 {
SELECT seq FROM t1 ORDER BY val COLLATE decimal;
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_execsql_test 2040 {
SELECT seq FROM t1 ORDER BY val COLLATE decimal DESC;
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_execsql_test 3000 {
CREATE TABLE t3(seq INTEGER PRIMARY KEY, val TEXT);
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<10)
INSERT INTO t3(seq, val) SELECT x, x FROM c;
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
INSERT INTO t3(seq, val) SELECT x+10, x*1000 FROM c;
SELECT decimal(val) FROM t3 ORDER BY seq;
} {1 2 3 4 5 6 7 8 9 10 1000 2000 3000 4000 5000}
do_execsql_test 3020 {
SELECT decimal_add(val,'0.5') FROM t3 WHERE seq>5 ORDER BY seq
} {6.5 7.5 8.5 9.5 10.5 1000.5 2000.5 3000.5 4000.5 5000.5}
do_execsql_test 3030 {
SELECT decimal_add(val,'-10') FROM t3 ORDER BY seq;
} {-9 -8 -7 -6 -5 -4 -3 -2 -1 0 990 1990 2990 3990 4990}
do_execsql_test 4000 {
SELECT decimal_sum(val) FROM t3;
} {15055}
do_execsql_test 4010 {
SELECT decimal_sum(decimal_add(val,val||'e+10')) FROM t3;
} {150550000015055}
do_execsql_test 4010 {
SELECT decimal_sum(decimal_add(val||'e+20',decimal_add(val,val||'e-20')))
FROM t3;
} {1505500000000000000015055.00000000000000015055}
do_execsql_test 5000 {
WITH RECURSIVE c(x,y,z) AS (
VALUES(0,'1','1')
UNION ALL
SELECT x+1, decimal_mul(y,'2'), decimal_mul(z,'0.5')
FROM c WHERE x<32
)
SELECT count(*) FROM c WHERE decimal_mul(y,z)='1';
} {33}
do_execsql_test 5100 {
SELECT decimal_mul('1234.00','2.00');
} {2468.00}
do_execsql_test 5101 {
SELECT decimal_mul('1234.00','2.0000');
} {2468.00}
do_execsql_test 5102 {
SELECT decimal_mul('1234.0000','2.000');
} {2468.000}
do_execsql_test 5103 {
SELECT decimal_mul('1234.0000','2');
} {2468}
if {[catch {load_static_extension db ieee754} error]} {
puts "Skipping ieee754 tests, hit load error: $error"
finish_test; return
}
do_execsql_test 6000 {
CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT);
WITH RECURSIVE c(x,v) AS (
VALUES(0,'1')
UNION ALL
SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971
) INSERT INTO pow2(x,v) SELECT x, v FROM c;
WITH RECURSIVE c(x,v) AS (
VALUES(-1,'0.5')
UNION ALL
SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075
) INSERT INTO pow2(x,v) SELECT x, v FROM c;
} {}
do_execsql_test 6010 {
WITH c(n) AS (SELECT ieee754_from_blob(x'0000000000000001'))
SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v)
FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n);
} {0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004940656458412465441765687928682213723650598026143247644255856825006755072702087518652998363616359923797965646954457177309266567103559397963987747960107818781263007131903114045278458171678489821036887186360569987307230500063874091535649843873124733972731696151400317153853980741262385655911710266585566867681870395603106249319452715914924553293054565444011274801297099995419319894090804165633245247571478690147267801593552386115501348035264934720193790268107107491703332226844753335720832431936092382893458368060106011506169809753078342277318329247904982524730776375927247874656084778203734469699533647017972677717585125660551199131504891101451037862738167250955837389733598993664809941164205702637090279242767544565229087538682506419718265533447265625}
do_execsql_test 6011 {
WITH c(n) AS (SELECT ieee754_from_blob(x'0000000000000001'))
SELECT decimal(c.n) FROM c;
} {0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004940656458412465441765687928682213723650598026143247644255856825006755072702087518652998363616359923797965646954457177309266567103559397963987747960107818781263007131903114045278458171678489821036887186360569987307230500063874091535649843873124733972731696151400317153853980741262385655911710266585566867681870395603106249319452715914924553293054565444011274801297099995419319894090804165633245247571478690147267801593552386115501348035264934720193790268107107491703332226844753335720832431936092382893458368060106011506169809753078342277318329247904982524730776375927247874656084778203734469699533647017972677717585125660551199131504891101451037862738167250955837389733598993664809941164205702637090279242767544565229087538682506419718265533447265625}
do_execsql_test 6020 {
WITH c(n) AS (SELECT ieee754_from_blob(x'7fefffffffffffff'))
SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v)
FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n);
} {179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368}
do_execsql_test 6100 {
SELECT ieee754(ieee754_from_blob(x'0000000000000001'));
} {ieee754(1,-1074)}
do_execsql_test 6110 {
SELECT ieee754(ieee754_from_blob(x'7fefffffffffffff'));
} {ieee754(9007199254740991,971)}
do_execsql_test 6120 {
SELECT printf('%.8e',ieee754_from_blob(x'0000000000000001'));
} {4.94065646e-324}
do_execsql_test 6130 {
SELECT printf('%.8e',ieee754_from_blob(x'ffefffffffffffff'));
} {-1.79769313e+308}
finish_test
|