github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/decimal.test (about)

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