/ Check-in [68c168b5]
Login

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

Overview
Comment:Added test cases based on Bruce Momjian's talk on SQL NULLs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | is-distinct-from
Files: files | file ages | folders
SHA1: 68c168b5e57aebc1ffd79006068aa99e78b59ee0
User & Date: drh 2015-06-13 19:05:44
Context
2015-06-13
19:05
Added test cases based on Bruce Momjian's talk on SQL NULLs. Leaf check-in: 68c168b5 user: drh tags: is-distinct-from
11:48
Add the IS DISTINCT FROM and IS NOT DISTINCT FROM operators. check-in: 0be44782 user: drh tags: is-distinct-from
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/momjian1.test.

            1  +# 2015-06-12
            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  +# Test cases derived from Bruce Momjian's talk on NULL handling in SQL.
           13  +# http://momjian.us/main/writings/pgsql/nulls.pdf
           14  +#
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +db null nil
           19  +do_execsql_test momjian1-100 {
           20  +  SELECT null;
           21  +} {nil}
           22  +
           23  +do_execsql_test momjian1-110 {
           24  +  CREATE TABLE nulltest (x INTEGER, y INTEGER);
           25  +  INSERT INTO nulltest VALUES (1,NULL);
           26  +  SELECT * FROM nulltest;
           27  +} {1 nil}
           28  +
           29  +do_execsql_test momjian1-120 {
           30  +  INSERT INTO nulltest (x) VALUES (2);
           31  +  SELECT * FROM nulltest;
           32  +} {1 nil 2 nil}
           33  +
           34  +do_catchsql_test momjian1-130 {
           35  +  CREATE TABLE nulltest2 (x INTEGER NOT NULL, y INTEGER NOT NULL);
           36  +  INSERT INTO nulltest2 VALUES (3,NULL )
           37  +} {1 {NOT NULL constraint failed: nulltest2.y}}
           38  +do_catchsql_test momjian1-131 {
           39  +  INSERT INTO nulltest2 (x) VALUES (4);
           40  +} {1 {NOT NULL constraint failed: nulltest2.y}}
           41  +
           42  +do_execsql_test momjian1-140 {
           43  +  SELECT NULL+1;
           44  +} {nil}
           45  +do_execsql_test momjian1-141 {
           46  +  SELECT NULL || 'a';
           47  +} {nil}
           48  +do_execsql_test momjian1-142 {
           49  +  SELECT 'b' || NULL;
           50  +} {nil}
           51  +
           52  +do_execsql_test momjian1-150 {
           53  +  CREATE TABLE inctest (x INTEGER);
           54  +  INSERT INTO inctest VALUES (30), (40),(NULL);
           55  +  SELECT x + 1 FROM inctest;
           56  +} {31 41 nil}
           57  +
           58  +do_execsql_test momjian1-160 {
           59  +  SELECT NULL = 1;
           60  +} {nil}
           61  +do_execsql_test momjian1-161 {
           62  +  SELECT NULL = '';
           63  +} {nil}
           64  +do_execsql_test momjian1-162 {
           65  +  SELECT NULL = NULL;
           66  +} {nil}
           67  +do_execsql_test momjian1-163 {
           68  +  SELECT NULL < NULL + 1;
           69  +} {nil}
           70  +
           71  +do_execsql_test momjian1-170 {
           72  +  SELECT 1 WHERE 1;
           73  +} {1}
           74  +do_execsql_test momjian1-171 {
           75  +  SELECT 1 WHERE 0;
           76  +} {}
           77  +do_execsql_test momjian1-172 {
           78  +  SELECT 1 WHERE NULL;
           79  +} {}
           80  +
           81  +do_execsql_test momjian1-180 {
           82  +  SELECT 1 AND NULL;
           83  +} {nil}
           84  +do_execsql_test momjian1-181 {
           85  +  SELECT NOT NULL;
           86  +} {nil}
           87  +
           88  +do_execsql_test momjian1-190 {
           89  +  SELECT * FROM inctest WHERE x >= 0;
           90  +} {30 40}
           91  +do_execsql_test momjian1-191 {
           92  +  SELECT * FROM inctest WHERE x < 0;
           93  +} {}
           94  +do_execsql_test momjian1-192 {
           95  +  SELECT * FROM inctest WHERE x < 0 OR x >= 0;
           96  +} {30 40}
           97  +
           98  +do_execsql_test momjian1-200 {
           99  +  SELECT * FROM inctest WHERE x <> 10;
          100  +} {30 40}
          101  +do_execsql_test momjian1-201 {
          102  +  SELECT * FROM inctest WHERE x <> 10 OR x = 10;    
          103  +} {30 40}
          104  +
          105  +do_execsql_test momjian1-210 {
          106  +  SELECT 1 <> 2 AND 1 <> 3;
          107  +} {1}
          108  +do_execsql_test momjian1-211 {
          109  +  SELECT 1 <> 2 AND 1 <> 3 AND 1 <> NULL;
          110  +} {nil}
          111  +
          112  +do_execsql_test momjian1-220 {
          113  +  SELECT 'a' IN (SELECT NULL);
          114  +} {nil}
          115  +do_execsql_test momjian1-221 {
          116  +  SELECT 'a' NOT IN (SELECT NULL);
          117  +} {nil}
          118  +
          119  +do_execsql_test momjian1-230 {
          120  +  SELECT 'a' IN (SELECT 'a' UNION ALL SELECT NULL);
          121  +} {1}
          122  +do_execsql_test momjian1-231 {
          123  +  SELECT 'a' NOT IN (SELECT 'a' UNION ALL SELECT NULL);
          124  +} {0}
          125  +do_execsql_test momjian1-232 {
          126  +  SELECT 'a' IN (SELECT 'b' UNION ALL SELECT NULL);
          127  +} {nil}
          128  +do_execsql_test momjian1-233 {
          129  +  SELECT 'a' NOT IN (SELECT 'b' UNION ALL SELECT NULL);
          130  +} {nil}
          131  +
          132  +do_execsql_test momjian1-240 {
          133  +  SELECT 'a' = 'b' OR 'a' = NULL;
          134  +} {nil}
          135  +do_execsql_test momjian1-241 {
          136  +  SELECT 'a' <> 'b' AND 'a' <> NULL;
          137  +} {nil}
          138  +
          139  +do_execsql_test momjian1-250 {
          140  +  SELECT NULL = NULL;
          141  +} {nil}
          142  +do_execsql_test momjian1-251 {
          143  +  SELECT NULL IS NULL;
          144  +} {1}
          145  +do_execsql_test momjian1-252 {
          146  +  SELECT NULL IS NOT NULL;
          147  +} {0}
          148  +
          149  +do_execsql_test momjian1-260 {
          150  +  SELECT * FROM inctest WHERE x IS NULL;
          151  +} {nil}
          152  +do_execsql_test momjian1-261 {
          153  +  SELECT * FROM inctest WHERE x IS NOT NULL;
          154  +} {30 40}
          155  +
          156  +do_execsql_test momjian1-270 {
          157  +  SELECT 2 IS DISTINCT FROM 1;
          158  +  SELECT 2 IS NOT 1
          159  +} {1 1}
          160  +do_execsql_test momjian1-271 {
          161  +  SELECT NULL IS DISTINCT FROM 1;
          162  +  SELECT NULL IS NOT 1;
          163  +} {1 1}
          164  +do_execsql_test momjian1-272 {
          165  +  SELECT NULL IS DISTINCT FROM NULL;
          166  +  SELECT NULL IS NOT NULL;
          167  +} {0 0}
          168  +do_execsql_test momjian1-273 {
          169  +  SELECT NULL <> 1;
          170  +} {nil}
          171  +
          172  +do_execsql_test momjian1-280 {
          173  +  -- SELECT * FROM inctest WHERE x IS DISTINCT FROM 30;
          174  +  SELECT * FROM inctest WHERE x IS NOT 30;
          175  +} {40 nil}
          176  +do_execsql_test momjian1-281 {
          177  +  -- SELECT * FROM inctest WHERE x IS NOT DISTINCT FROM 30;
          178  +  SELECT * FROM inctest WHERE x IS 30;
          179  +} {30}
          180  +
          181  +do_execsql_test momjian1-300 {
          182  +  CREATE TABLE disttest (x INTEGER, y INTEGER);
          183  +  INSERT INTO disttest VALUES (1, 1), (2, 3), (NULL, NULL);
          184  +  SELECT * FROM disttest where x IS NOT DISTINCT FROM y;
          185  +  SELECT * FROM disttest WHERE x IS y;
          186  +} {1 1 nil nil 1 1 nil nil}
          187  +
          188  +do_execsql_test momjian1-310 {
          189  +  WITH ordertest AS (
          190  +    SELECT NULL
          191  +    UNION ALL
          192  +    SELECT 2
          193  +    UNION ALL
          194  +    SELECT 1
          195  +    UNION ALL
          196  +    SELECT NULL
          197  +  )
          198  +  SELECT * FROM ordertest
          199  +  ORDER BY 1;
          200  +} {nil nil 1 2}
          201  +
          202  +do_execsql_test momjian1-320 {
          203  +  CREATE TABLE uniqtest (x INTEGER);
          204  +  CREATE UNIQUE INDEX i_uniqtest ON uniqtest (x);
          205  +  INSERT INTO uniqtest VALUES (1),(NULL), (NULL);
          206  +  SELECT * FROM uniqtest;
          207  +} {1 nil nil}
          208  +
          209  +do_execsql_test momjian1-330 {
          210  +  CREATE TABLE aggtest (x INTEGER);
          211  +  INSERT INTO aggtest VALUES (7), (8), (NULL);
          212  +  SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
          213  +} {3 2 15 7 8 7.5}
          214  +do_execsql_test momjian1-331 {
          215  +  DELETE FROM aggtest;
          216  +  SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
          217  +} {0 0 nil nil nil nil}
          218  +
          219  +do_execsql_test momjian1-340 {
          220  +  DELETE FROM aggtest;
          221  +  INSERT INTO aggtest VALUES (7), (8), (NULL), (NULL);
          222  +  SELECT x, COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x)
          223  +    FROM aggtest
          224  +   GROUP BY x ORDER BY x;
          225  +} {nil 2 0 nil nil nil nil 7 1 1 7 7 7 7.0 8 1 1 8 8 8 8.0}
          226  +
          227  +do_execsql_test momjian1-350 {
          228  +  SELECT COALESCE(NULL, 0);
          229  +} {0}
          230  +do_execsql_test momjian1-351 {
          231  +  SELECT COALESCE(NULL, 'I am null.');
          232  +} {{I am null.}}
          233  +
          234  +do_execsql_test momjian1-352 {
          235  +  CREATE TABLE nullmaptest(x TEXT);
          236  +  INSERT INTO nullmaptest VALUES ('f'), ('g'), (NULL);
          237  +  SELECT x, COALESCE(x, 'n/a') FROM nullmaptest;
          238  +} {f f g g nil n/a}
          239  +do_execsql_test momjian1-353 {
          240  +  SELECT 'a' || COALESCE(NULL, '') || 'b';
          241  +} {ab}
          242  +do_execsql_test momjian1-354 {
          243  +  SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;
          244  +} {15 15}
          245  +do_execsql_test momjian1-355 {
          246  +  DELETE FROM aggtest;
          247  +  SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;
          248  +} {nil 0}
          249  +
          250  +do_execsql_test momjian1-360 {
          251  +  CREATE TABLE emptytest (x INTEGER);
          252  +  SELECT * from emptytest;
          253  +} {}
          254  +do_execsql_test momjian1-361 {
          255  +  SELECT (SELECT * from emptytest);
          256  +} {nil}
          257  +do_execsql_test momjian1-362 {
          258  +  SELECT (SELECT * from emptytest) IS NULL;
          259  +} {1}
          260  +
          261  +
          262  +finish_test