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

     1  # 2009 November 10
     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  # Additional test cases for the COALESCE() and IFNULL() functions.
    12  #
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  
    17  
    18  do_test coalesce-1.0 {
    19    db eval {
    20      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
    21      INSERT INTO t1 VALUES(1, null, null, null);
    22      INSERT INTO t1 VALUES(2, 2, 99, 99);
    23      INSERT INTO t1 VALUES(3, null, 3, 99);
    24      INSERT INTO t1 VALUES(4, null, null, 4);
    25      INSERT INTO t1 VALUES(5, null, null, null);
    26      INSERT INTO t1 VALUES(6, 22, 99, 99);
    27      INSERT INTO t1 VALUES(7, null, 33, 99);
    28      INSERT INTO t1 VALUES(8, null, null, 44);
    29  
    30      SELECT coalesce(b,c,d) FROM t1 ORDER BY a;
    31    }
    32  } {{} 2 3 4 {} 22 33 44}
    33  do_test coalesce-1.1 {
    34    db eval {
    35      SELECT coalesce(d+c+b,d+c,d) FROM t1 ORDER BY a;
    36    }
    37  } {{} 200 102 4 {} 220 132 44}
    38  do_test coalesce-1.2 {
    39    db eval {
    40      SELECT ifnull(d+c+b,ifnull(d+c,d)) FROM t1 ORDER BY a;
    41    }
    42  } {{} 200 102 4 {} 220 132 44}
    43  do_test coalesce-1.3 {
    44    db eval {
    45      SELECT ifnull(ifnull(d+c+b,d+c),d) FROM t1 ORDER BY a;
    46    }
    47  } {{} 200 102 4 {} 220 132 44}
    48  do_test coalesce-1.4 {
    49    db eval {
    50      SELECT ifnull(ifnull(b,c),d) FROM t1 ORDER BY a;
    51    }
    52  } {{} 2 3 4 {} 22 33 44}
    53  do_test coalesce-1.5 {
    54    db eval {
    55      SELECT ifnull(b,ifnull(c,d)) FROM t1 ORDER BY a;
    56    }
    57  } {{} 2 3 4 {} 22 33 44}
    58  do_test coalesce-1.6 {
    59    db eval {
    60      SELECT coalesce(b,NOT b,-b,abs(b),lower(b),length(b),min(b,5),b*123,c)
    61        FROM t1 ORDER BY a;
    62    }
    63  } {{} 2 3 {} {} 22 33 {}}
    64  do_test coalesce-1.7 {
    65    db eval {
    66      SELECT ifnull(nullif(a,4),99)
    67        FROM t1 ORDER BY a;
    68    }
    69  } {1 2 3 99 5 6 7 8}
    70  do_test coalesce-1.8 {
    71    db eval {
    72  pragma vdbe_listing=on;
    73      SELECT coalesce(
    74        CASE WHEN b=2 THEN 123 END,
    75        CASE WHEN b=3 THEN 234 END,
    76        CASE WHEN c=3 THEN 345 WHEN c=33 THEN 456 END,
    77        d
    78      )
    79      FROM t1 ORDER BY a;
    80    }
    81  } {{} 123 345 4 {} 99 456 44}
    82  
    83  
    84  finish_test