AryaWu/sqlite

1# 2009 November 102#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# Additional test cases for the COALESCE() and IFNULL() functions.12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16 17 18do_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}33do_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}38do_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}43do_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}48do_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}53do_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}58do_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 {}}64do_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}70do_test coalesce-1.8 {71  db eval {72pragma 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      d78    )79    FROM t1 ORDER BY a;80  }81} {{} 123 345 4 {} 99 456 44}82 83 84finish_test85