1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2002 January 29 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. 137c478bd9Sstevel@tonic-gate# 147c478bd9Sstevel@tonic-gate# This file implements tests for the NOT NULL constraint. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: notnull.test,v 1.3 2003/01/29 18:46:54 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gatedo_test notnull-1.0 { 227c478bd9Sstevel@tonic-gate execsql { 237c478bd9Sstevel@tonic-gate CREATE TABLE t1 ( 247c478bd9Sstevel@tonic-gate a NOT NULL, 257c478bd9Sstevel@tonic-gate b NOT NULL DEFAULT 5, 267c478bd9Sstevel@tonic-gate c NOT NULL ON CONFLICT REPLACE DEFAULT 6, 277c478bd9Sstevel@tonic-gate d NOT NULL ON CONFLICT IGNORE DEFAULT 7, 287c478bd9Sstevel@tonic-gate e NOT NULL ON CONFLICT ABORT DEFAULT 8 297c478bd9Sstevel@tonic-gate ); 307c478bd9Sstevel@tonic-gate SELECT * FROM t1; 317c478bd9Sstevel@tonic-gate } 327c478bd9Sstevel@tonic-gate} {} 337c478bd9Sstevel@tonic-gatedo_test notnull-1.1 { 347c478bd9Sstevel@tonic-gate catchsql { 357c478bd9Sstevel@tonic-gate DELETE FROM t1; 367c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 377c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 387c478bd9Sstevel@tonic-gate } 397c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 407c478bd9Sstevel@tonic-gatedo_test notnull-1.2 { 417c478bd9Sstevel@tonic-gate catchsql { 427c478bd9Sstevel@tonic-gate DELETE FROM t1; 437c478bd9Sstevel@tonic-gate INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5); 447c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 457c478bd9Sstevel@tonic-gate } 467c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 477c478bd9Sstevel@tonic-gatedo_test notnull-1.3 { 487c478bd9Sstevel@tonic-gate catchsql { 497c478bd9Sstevel@tonic-gate DELETE FROM t1; 507c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5); 517c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 527c478bd9Sstevel@tonic-gate } 537c478bd9Sstevel@tonic-gate} {0 {}} 547c478bd9Sstevel@tonic-gatedo_test notnull-1.4 { 557c478bd9Sstevel@tonic-gate catchsql { 567c478bd9Sstevel@tonic-gate DELETE FROM t1; 577c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5); 587c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 597c478bd9Sstevel@tonic-gate } 607c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 617c478bd9Sstevel@tonic-gatedo_test notnull-1.5 { 627c478bd9Sstevel@tonic-gate catchsql { 637c478bd9Sstevel@tonic-gate DELETE FROM t1; 647c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5); 657c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 667c478bd9Sstevel@tonic-gate } 677c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 687c478bd9Sstevel@tonic-gatedo_test notnull-1.6 { 697c478bd9Sstevel@tonic-gate catchsql { 707c478bd9Sstevel@tonic-gate DELETE FROM t1; 717c478bd9Sstevel@tonic-gate INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5); 727c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 737c478bd9Sstevel@tonic-gate } 747c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 757c478bd9Sstevel@tonic-gatedo_test notnull-1.7 { 767c478bd9Sstevel@tonic-gate catchsql { 777c478bd9Sstevel@tonic-gate DELETE FROM t1; 787c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5); 797c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 807c478bd9Sstevel@tonic-gate } 817c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 827c478bd9Sstevel@tonic-gatedo_test notnull-1.8 { 837c478bd9Sstevel@tonic-gate catchsql { 847c478bd9Sstevel@tonic-gate DELETE FROM t1; 857c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5); 867c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 877c478bd9Sstevel@tonic-gate } 887c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 897c478bd9Sstevel@tonic-gatedo_test notnull-1.9 { 907c478bd9Sstevel@tonic-gate catchsql { 917c478bd9Sstevel@tonic-gate DELETE FROM t1; 927c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5); 937c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 947c478bd9Sstevel@tonic-gate } 957c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 967c478bd9Sstevel@tonic-gatedo_test notnull-1.10 { 977c478bd9Sstevel@tonic-gate catchsql { 987c478bd9Sstevel@tonic-gate DELETE FROM t1; 997c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5); 1007c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1017c478bd9Sstevel@tonic-gate } 1027c478bd9Sstevel@tonic-gate} {1 {t1.b may not be NULL}} 1037c478bd9Sstevel@tonic-gatedo_test notnull-1.11 { 1047c478bd9Sstevel@tonic-gate catchsql { 1057c478bd9Sstevel@tonic-gate DELETE FROM t1; 1067c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5); 1077c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1087c478bd9Sstevel@tonic-gate } 1097c478bd9Sstevel@tonic-gate} {0 {}} 1107c478bd9Sstevel@tonic-gatedo_test notnull-1.12 { 1117c478bd9Sstevel@tonic-gate catchsql { 1127c478bd9Sstevel@tonic-gate DELETE FROM t1; 1137c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5); 1147c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1157c478bd9Sstevel@tonic-gate } 1167c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 1177c478bd9Sstevel@tonic-gatedo_test notnull-1.13 { 1187c478bd9Sstevel@tonic-gate catchsql { 1197c478bd9Sstevel@tonic-gate DELETE FROM t1; 1207c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 1217c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1227c478bd9Sstevel@tonic-gate } 1237c478bd9Sstevel@tonic-gate} {0 {1 2 6 4 5}} 1247c478bd9Sstevel@tonic-gatedo_test notnull-1.14 { 1257c478bd9Sstevel@tonic-gate catchsql { 1267c478bd9Sstevel@tonic-gate DELETE FROM t1; 1277c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 1287c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1297c478bd9Sstevel@tonic-gate } 1307c478bd9Sstevel@tonic-gate} {0 {}} 1317c478bd9Sstevel@tonic-gatedo_test notnull-1.15 { 1327c478bd9Sstevel@tonic-gate catchsql { 1337c478bd9Sstevel@tonic-gate DELETE FROM t1; 1347c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 1357c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1367c478bd9Sstevel@tonic-gate } 1377c478bd9Sstevel@tonic-gate} {0 {1 2 6 4 5}} 1387c478bd9Sstevel@tonic-gatedo_test notnull-1.16 { 1397c478bd9Sstevel@tonic-gate catchsql { 1407c478bd9Sstevel@tonic-gate DELETE FROM t1; 1417c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 1427c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1437c478bd9Sstevel@tonic-gate } 1447c478bd9Sstevel@tonic-gate} {1 {t1.c may not be NULL}} 1457c478bd9Sstevel@tonic-gatedo_test notnull-1.17 { 1467c478bd9Sstevel@tonic-gate catchsql { 1477c478bd9Sstevel@tonic-gate DELETE FROM t1; 1487c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5); 1497c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1507c478bd9Sstevel@tonic-gate } 1517c478bd9Sstevel@tonic-gate} {1 {t1.d may not be NULL}} 1527c478bd9Sstevel@tonic-gatedo_test notnull-1.18 { 1537c478bd9Sstevel@tonic-gate catchsql { 1547c478bd9Sstevel@tonic-gate DELETE FROM t1; 1557c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5); 1567c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1577c478bd9Sstevel@tonic-gate } 1587c478bd9Sstevel@tonic-gate} {0 {1 2 3 7 5}} 1597c478bd9Sstevel@tonic-gatedo_test notnull-1.19 { 1607c478bd9Sstevel@tonic-gate catchsql { 1617c478bd9Sstevel@tonic-gate DELETE FROM t1; 1627c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4); 1637c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1647c478bd9Sstevel@tonic-gate } 1657c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 8}} 1667c478bd9Sstevel@tonic-gatedo_test notnull-1.20 { 1677c478bd9Sstevel@tonic-gate catchsql { 1687c478bd9Sstevel@tonic-gate DELETE FROM t1; 1697c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null); 1707c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1717c478bd9Sstevel@tonic-gate } 1727c478bd9Sstevel@tonic-gate} {1 {t1.e may not be NULL}} 1737c478bd9Sstevel@tonic-gatedo_test notnull-1.21 { 1747c478bd9Sstevel@tonic-gate catchsql { 1757c478bd9Sstevel@tonic-gate DELETE FROM t1; 1767c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5); 1777c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 1787c478bd9Sstevel@tonic-gate } 1797c478bd9Sstevel@tonic-gate} {0 {5 5 3 2 1}} 1807c478bd9Sstevel@tonic-gate 1817c478bd9Sstevel@tonic-gatedo_test notnull-2.1 { 1827c478bd9Sstevel@tonic-gate catchsql { 1837c478bd9Sstevel@tonic-gate DELETE FROM t1; 1847c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 1857c478bd9Sstevel@tonic-gate UPDATE t1 SET a=null; 1867c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 1877c478bd9Sstevel@tonic-gate } 1887c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 1897c478bd9Sstevel@tonic-gatedo_test notnull-2.2 { 1907c478bd9Sstevel@tonic-gate catchsql { 1917c478bd9Sstevel@tonic-gate DELETE FROM t1; 1927c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 1937c478bd9Sstevel@tonic-gate UPDATE OR REPLACE t1 SET a=null; 1947c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 1957c478bd9Sstevel@tonic-gate } 1967c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 1977c478bd9Sstevel@tonic-gatedo_test notnull-2.3 { 1987c478bd9Sstevel@tonic-gate catchsql { 1997c478bd9Sstevel@tonic-gate DELETE FROM t1; 2007c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2017c478bd9Sstevel@tonic-gate UPDATE OR IGNORE t1 SET a=null; 2027c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2037c478bd9Sstevel@tonic-gate } 2047c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 2057c478bd9Sstevel@tonic-gatedo_test notnull-2.4 { 2067c478bd9Sstevel@tonic-gate catchsql { 2077c478bd9Sstevel@tonic-gate DELETE FROM t1; 2087c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2097c478bd9Sstevel@tonic-gate UPDATE OR ABORT t1 SET a=null; 2107c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2117c478bd9Sstevel@tonic-gate } 2127c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 2137c478bd9Sstevel@tonic-gatedo_test notnull-2.5 { 2147c478bd9Sstevel@tonic-gate catchsql { 2157c478bd9Sstevel@tonic-gate DELETE FROM t1; 2167c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2177c478bd9Sstevel@tonic-gate UPDATE t1 SET b=null; 2187c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2197c478bd9Sstevel@tonic-gate } 2207c478bd9Sstevel@tonic-gate} {1 {t1.b may not be NULL}} 2217c478bd9Sstevel@tonic-gatedo_test notnull-2.6 { 2227c478bd9Sstevel@tonic-gate catchsql { 2237c478bd9Sstevel@tonic-gate DELETE FROM t1; 2247c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2257c478bd9Sstevel@tonic-gate UPDATE OR REPLACE t1 SET b=null, d=e, e=d; 2267c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2277c478bd9Sstevel@tonic-gate } 2287c478bd9Sstevel@tonic-gate} {0 {1 5 3 5 4}} 2297c478bd9Sstevel@tonic-gatedo_test notnull-2.7 { 2307c478bd9Sstevel@tonic-gate catchsql { 2317c478bd9Sstevel@tonic-gate DELETE FROM t1; 2327c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2337c478bd9Sstevel@tonic-gate UPDATE OR IGNORE t1 SET b=null, d=e, e=d; 2347c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2357c478bd9Sstevel@tonic-gate } 2367c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 2377c478bd9Sstevel@tonic-gatedo_test notnull-2.8 { 2387c478bd9Sstevel@tonic-gate catchsql { 2397c478bd9Sstevel@tonic-gate DELETE FROM t1; 2407c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2417c478bd9Sstevel@tonic-gate UPDATE t1 SET c=null, d=e, e=d; 2427c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2437c478bd9Sstevel@tonic-gate } 2447c478bd9Sstevel@tonic-gate} {0 {1 2 6 5 4}} 2457c478bd9Sstevel@tonic-gatedo_test notnull-2.9 { 2467c478bd9Sstevel@tonic-gate catchsql { 2477c478bd9Sstevel@tonic-gate DELETE FROM t1; 2487c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2497c478bd9Sstevel@tonic-gate UPDATE t1 SET d=null, a=b, b=a; 2507c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2517c478bd9Sstevel@tonic-gate } 2527c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 2537c478bd9Sstevel@tonic-gatedo_test notnull-2.10 { 2547c478bd9Sstevel@tonic-gate catchsql { 2557c478bd9Sstevel@tonic-gate DELETE FROM t1; 2567c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 2577c478bd9Sstevel@tonic-gate UPDATE t1 SET e=null, a=b, b=a; 2587c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 2597c478bd9Sstevel@tonic-gate } 2607c478bd9Sstevel@tonic-gate} {1 {t1.e may not be NULL}} 2617c478bd9Sstevel@tonic-gate 2627c478bd9Sstevel@tonic-gatedo_test notnull-3.0 { 2637c478bd9Sstevel@tonic-gate execsql { 2647c478bd9Sstevel@tonic-gate CREATE INDEX t1a ON t1(a); 2657c478bd9Sstevel@tonic-gate CREATE INDEX t1b ON t1(b); 2667c478bd9Sstevel@tonic-gate CREATE INDEX t1c ON t1(c); 2677c478bd9Sstevel@tonic-gate CREATE INDEX t1d ON t1(d); 2687c478bd9Sstevel@tonic-gate CREATE INDEX t1e ON t1(e); 2697c478bd9Sstevel@tonic-gate CREATE INDEX t1abc ON t1(a,b,c); 2707c478bd9Sstevel@tonic-gate } 2717c478bd9Sstevel@tonic-gate} {} 2727c478bd9Sstevel@tonic-gatedo_test notnull-3.1 { 2737c478bd9Sstevel@tonic-gate catchsql { 2747c478bd9Sstevel@tonic-gate DELETE FROM t1; 2757c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); 2767c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 2777c478bd9Sstevel@tonic-gate } 2787c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 2797c478bd9Sstevel@tonic-gatedo_test notnull-3.2 { 2807c478bd9Sstevel@tonic-gate catchsql { 2817c478bd9Sstevel@tonic-gate DELETE FROM t1; 2827c478bd9Sstevel@tonic-gate INSERT INTO t1(b,c,d,e) VALUES(2,3,4,5); 2837c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 2847c478bd9Sstevel@tonic-gate } 2857c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 2867c478bd9Sstevel@tonic-gatedo_test notnull-3.3 { 2877c478bd9Sstevel@tonic-gate catchsql { 2887c478bd9Sstevel@tonic-gate DELETE FROM t1; 2897c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(b,c,d,e) VALUES(2,3,4,5); 2907c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 2917c478bd9Sstevel@tonic-gate } 2927c478bd9Sstevel@tonic-gate} {0 {}} 2937c478bd9Sstevel@tonic-gatedo_test notnull-3.4 { 2947c478bd9Sstevel@tonic-gate catchsql { 2957c478bd9Sstevel@tonic-gate DELETE FROM t1; 2967c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(b,c,d,e) VALUES(2,3,4,5); 2977c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 2987c478bd9Sstevel@tonic-gate } 2997c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 3007c478bd9Sstevel@tonic-gatedo_test notnull-3.5 { 3017c478bd9Sstevel@tonic-gate catchsql { 3027c478bd9Sstevel@tonic-gate DELETE FROM t1; 3037c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(b,c,d,e) VALUES(2,3,4,5); 3047c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3057c478bd9Sstevel@tonic-gate } 3067c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 3077c478bd9Sstevel@tonic-gatedo_test notnull-3.6 { 3087c478bd9Sstevel@tonic-gate catchsql { 3097c478bd9Sstevel@tonic-gate DELETE FROM t1; 3107c478bd9Sstevel@tonic-gate INSERT INTO t1(a,c,d,e) VALUES(1,3,4,5); 3117c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3127c478bd9Sstevel@tonic-gate } 3137c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 3147c478bd9Sstevel@tonic-gatedo_test notnull-3.7 { 3157c478bd9Sstevel@tonic-gate catchsql { 3167c478bd9Sstevel@tonic-gate DELETE FROM t1; 3177c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(a,c,d,e) VALUES(1,3,4,5); 3187c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3197c478bd9Sstevel@tonic-gate } 3207c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 3217c478bd9Sstevel@tonic-gatedo_test notnull-3.8 { 3227c478bd9Sstevel@tonic-gate catchsql { 3237c478bd9Sstevel@tonic-gate DELETE FROM t1; 3247c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(a,c,d,e) VALUES(1,3,4,5); 3257c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3267c478bd9Sstevel@tonic-gate } 3277c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 3287c478bd9Sstevel@tonic-gatedo_test notnull-3.9 { 3297c478bd9Sstevel@tonic-gate catchsql { 3307c478bd9Sstevel@tonic-gate DELETE FROM t1; 3317c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,c,d,e) VALUES(1,3,4,5); 3327c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3337c478bd9Sstevel@tonic-gate } 3347c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 3357c478bd9Sstevel@tonic-gatedo_test notnull-3.10 { 3367c478bd9Sstevel@tonic-gate catchsql { 3377c478bd9Sstevel@tonic-gate DELETE FROM t1; 3387c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5); 3397c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3407c478bd9Sstevel@tonic-gate } 3417c478bd9Sstevel@tonic-gate} {1 {t1.b may not be NULL}} 3427c478bd9Sstevel@tonic-gatedo_test notnull-3.11 { 3437c478bd9Sstevel@tonic-gate catchsql { 3447c478bd9Sstevel@tonic-gate DELETE FROM t1; 3457c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5); 3467c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3477c478bd9Sstevel@tonic-gate } 3487c478bd9Sstevel@tonic-gate} {0 {}} 3497c478bd9Sstevel@tonic-gatedo_test notnull-3.12 { 3507c478bd9Sstevel@tonic-gate catchsql { 3517c478bd9Sstevel@tonic-gate DELETE FROM t1; 3527c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,null,3,4,5); 3537c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3547c478bd9Sstevel@tonic-gate } 3557c478bd9Sstevel@tonic-gate} {0 {1 5 3 4 5}} 3567c478bd9Sstevel@tonic-gatedo_test notnull-3.13 { 3577c478bd9Sstevel@tonic-gate catchsql { 3587c478bd9Sstevel@tonic-gate DELETE FROM t1; 3597c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 3607c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3617c478bd9Sstevel@tonic-gate } 3627c478bd9Sstevel@tonic-gate} {0 {1 2 6 4 5}} 3637c478bd9Sstevel@tonic-gatedo_test notnull-3.14 { 3647c478bd9Sstevel@tonic-gate catchsql { 3657c478bd9Sstevel@tonic-gate DELETE FROM t1; 3667c478bd9Sstevel@tonic-gate INSERT OR IGNORE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 3677c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3687c478bd9Sstevel@tonic-gate } 3697c478bd9Sstevel@tonic-gate} {0 {}} 3707c478bd9Sstevel@tonic-gatedo_test notnull-3.15 { 3717c478bd9Sstevel@tonic-gate catchsql { 3727c478bd9Sstevel@tonic-gate DELETE FROM t1; 3737c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 3747c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3757c478bd9Sstevel@tonic-gate } 3767c478bd9Sstevel@tonic-gate} {0 {1 2 6 4 5}} 3777c478bd9Sstevel@tonic-gatedo_test notnull-3.16 { 3787c478bd9Sstevel@tonic-gate catchsql { 3797c478bd9Sstevel@tonic-gate DELETE FROM t1; 3807c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,null,4,5); 3817c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3827c478bd9Sstevel@tonic-gate } 3837c478bd9Sstevel@tonic-gate} {1 {t1.c may not be NULL}} 3847c478bd9Sstevel@tonic-gatedo_test notnull-3.17 { 3857c478bd9Sstevel@tonic-gate catchsql { 3867c478bd9Sstevel@tonic-gate DELETE FROM t1; 3877c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,b,c,d,e) VALUES(1,2,3,null,5); 3887c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3897c478bd9Sstevel@tonic-gate } 3907c478bd9Sstevel@tonic-gate} {1 {t1.d may not be NULL}} 3917c478bd9Sstevel@tonic-gatedo_test notnull-3.18 { 3927c478bd9Sstevel@tonic-gate catchsql { 3937c478bd9Sstevel@tonic-gate DELETE FROM t1; 3947c478bd9Sstevel@tonic-gate INSERT OR ABORT INTO t1(a,b,c,e) VALUES(1,2,3,5); 3957c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 3967c478bd9Sstevel@tonic-gate } 3977c478bd9Sstevel@tonic-gate} {0 {1 2 3 7 5}} 3987c478bd9Sstevel@tonic-gatedo_test notnull-3.19 { 3997c478bd9Sstevel@tonic-gate catchsql { 4007c478bd9Sstevel@tonic-gate DELETE FROM t1; 4017c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4); 4027c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 4037c478bd9Sstevel@tonic-gate } 4047c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 8}} 4057c478bd9Sstevel@tonic-gatedo_test notnull-3.20 { 4067c478bd9Sstevel@tonic-gate catchsql { 4077c478bd9Sstevel@tonic-gate DELETE FROM t1; 4087c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,null); 4097c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 4107c478bd9Sstevel@tonic-gate } 4117c478bd9Sstevel@tonic-gate} {1 {t1.e may not be NULL}} 4127c478bd9Sstevel@tonic-gatedo_test notnull-3.21 { 4137c478bd9Sstevel@tonic-gate catchsql { 4147c478bd9Sstevel@tonic-gate DELETE FROM t1; 4157c478bd9Sstevel@tonic-gate INSERT OR REPLACE INTO t1(e,d,c,b,a) VALUES(1,2,3,null,5); 4167c478bd9Sstevel@tonic-gate SELECT * FROM t1 order by a; 4177c478bd9Sstevel@tonic-gate } 4187c478bd9Sstevel@tonic-gate} {0 {5 5 3 2 1}} 4197c478bd9Sstevel@tonic-gate 4207c478bd9Sstevel@tonic-gatedo_test notnull-4.1 { 4217c478bd9Sstevel@tonic-gate catchsql { 4227c478bd9Sstevel@tonic-gate DELETE FROM t1; 4237c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4247c478bd9Sstevel@tonic-gate UPDATE t1 SET a=null; 4257c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4267c478bd9Sstevel@tonic-gate } 4277c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 4287c478bd9Sstevel@tonic-gatedo_test notnull-4.2 { 4297c478bd9Sstevel@tonic-gate catchsql { 4307c478bd9Sstevel@tonic-gate DELETE FROM t1; 4317c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4327c478bd9Sstevel@tonic-gate UPDATE OR REPLACE t1 SET a=null; 4337c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4347c478bd9Sstevel@tonic-gate } 4357c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 4367c478bd9Sstevel@tonic-gatedo_test notnull-4.3 { 4377c478bd9Sstevel@tonic-gate catchsql { 4387c478bd9Sstevel@tonic-gate DELETE FROM t1; 4397c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4407c478bd9Sstevel@tonic-gate UPDATE OR IGNORE t1 SET a=null; 4417c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4427c478bd9Sstevel@tonic-gate } 4437c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 4447c478bd9Sstevel@tonic-gatedo_test notnull-4.4 { 4457c478bd9Sstevel@tonic-gate catchsql { 4467c478bd9Sstevel@tonic-gate DELETE FROM t1; 4477c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4487c478bd9Sstevel@tonic-gate UPDATE OR ABORT t1 SET a=null; 4497c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4507c478bd9Sstevel@tonic-gate } 4517c478bd9Sstevel@tonic-gate} {1 {t1.a may not be NULL}} 4527c478bd9Sstevel@tonic-gatedo_test notnull-4.5 { 4537c478bd9Sstevel@tonic-gate catchsql { 4547c478bd9Sstevel@tonic-gate DELETE FROM t1; 4557c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4567c478bd9Sstevel@tonic-gate UPDATE t1 SET b=null; 4577c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4587c478bd9Sstevel@tonic-gate } 4597c478bd9Sstevel@tonic-gate} {1 {t1.b may not be NULL}} 4607c478bd9Sstevel@tonic-gatedo_test notnull-4.6 { 4617c478bd9Sstevel@tonic-gate catchsql { 4627c478bd9Sstevel@tonic-gate DELETE FROM t1; 4637c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4647c478bd9Sstevel@tonic-gate UPDATE OR REPLACE t1 SET b=null, d=e, e=d; 4657c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4667c478bd9Sstevel@tonic-gate } 4677c478bd9Sstevel@tonic-gate} {0 {1 5 3 5 4}} 4687c478bd9Sstevel@tonic-gatedo_test notnull-4.7 { 4697c478bd9Sstevel@tonic-gate catchsql { 4707c478bd9Sstevel@tonic-gate DELETE FROM t1; 4717c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4727c478bd9Sstevel@tonic-gate UPDATE OR IGNORE t1 SET b=null, d=e, e=d; 4737c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4747c478bd9Sstevel@tonic-gate } 4757c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 4767c478bd9Sstevel@tonic-gatedo_test notnull-4.8 { 4777c478bd9Sstevel@tonic-gate catchsql { 4787c478bd9Sstevel@tonic-gate DELETE FROM t1; 4797c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4807c478bd9Sstevel@tonic-gate UPDATE t1 SET c=null, d=e, e=d; 4817c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4827c478bd9Sstevel@tonic-gate } 4837c478bd9Sstevel@tonic-gate} {0 {1 2 6 5 4}} 4847c478bd9Sstevel@tonic-gatedo_test notnull-4.9 { 4857c478bd9Sstevel@tonic-gate catchsql { 4867c478bd9Sstevel@tonic-gate DELETE FROM t1; 4877c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4887c478bd9Sstevel@tonic-gate UPDATE t1 SET d=null, a=b, b=a; 4897c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4907c478bd9Sstevel@tonic-gate } 4917c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 5}} 4927c478bd9Sstevel@tonic-gatedo_test notnull-4.10 { 4937c478bd9Sstevel@tonic-gate catchsql { 4947c478bd9Sstevel@tonic-gate DELETE FROM t1; 4957c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3,4,5); 4967c478bd9Sstevel@tonic-gate UPDATE t1 SET e=null, a=b, b=a; 4977c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 4987c478bd9Sstevel@tonic-gate } 4997c478bd9Sstevel@tonic-gate} {1 {t1.e may not be NULL}} 5007c478bd9Sstevel@tonic-gate 5017c478bd9Sstevel@tonic-gatefinish_test 502