/ Check-in [d0591258]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional test cases for the coalesce() and ifnull() functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d0591258b62df4fa610b7ac2a2af0344cf82f231
User & Date: drh 2009-11-11 01:14:18
Context
2009-11-11
04:17
Modified CLI to raise an error when extra command line options are passed. Added tests to verify correct handling, as well as other basic handling of command line options. Ticket [f5cb008a65]. check-in: 09b4f19f user: shaneh tags: trunk
01:14
Additional test cases for the coalesce() and ifnull() functions. check-in: d0591258 user: drh tags: trunk
00:24
Generate VDBE code for the built-in COALESCE() and IFNULL() functions. This allows unused arguments to never be evaluated, which is a performance win when the unused argument is a subquery. check-in: 30055b25 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/coalesce.test.

            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