AryaWu/sqlite

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