Annotation of embedaddon/sqlite3/test/insert2.test, revision 1.1
1.1 ! misho 1: # 2001 September 15
! 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: # This file implements regression tests for SQLite library. The
! 12: # focus of this file is testing the INSERT statement that takes is
! 13: # result from a SELECT.
! 14: #
! 15: # $Id: insert2.test,v 1.19 2008/01/16 18:20:42 danielk1977 Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Create some tables with data that we can select against
! 21: #
! 22: do_test insert2-1.0 {
! 23: execsql {CREATE TABLE d1(n int, log int);}
! 24: for {set i 1} {$i<=20} {incr i} {
! 25: for {set j 0} {(1<<$j)<$i} {incr j} {}
! 26: execsql "INSERT INTO d1 VALUES($i,$j)"
! 27: }
! 28: execsql {SELECT * FROM d1 ORDER BY n}
! 29: } {1 0 2 1 3 2 4 2 5 3 6 3 7 3 8 3 9 4 10 4 11 4 12 4 13 4 14 4 15 4 16 4 17 5 18 5 19 5 20 5}
! 30:
! 31: # Insert into a new table from the old one.
! 32: #
! 33: do_test insert2-1.1.1 {
! 34: execsql {
! 35: CREATE TABLE t1(log int, cnt int);
! 36: PRAGMA count_changes=on;
! 37: }
! 38: ifcapable explain {
! 39: execsql {
! 40: EXPLAIN INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
! 41: }
! 42: }
! 43: execsql {
! 44: INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
! 45: }
! 46: } {6}
! 47: do_test insert2-1.1.2 {
! 48: db changes
! 49: } {6}
! 50: do_test insert2-1.1.3 {
! 51: execsql {SELECT * FROM t1 ORDER BY log}
! 52: } {0 1 1 1 2 2 3 4 4 8 5 4}
! 53:
! 54: ifcapable compound {
! 55: do_test insert2-1.2.1 {
! 56: catch {execsql {DROP TABLE t1}}
! 57: execsql {
! 58: CREATE TABLE t1(log int, cnt int);
! 59: INSERT INTO t1
! 60: SELECT log, count(*) FROM d1 GROUP BY log
! 61: EXCEPT SELECT n-1,log FROM d1;
! 62: }
! 63: } {4}
! 64: do_test insert2-1.2.2 {
! 65: execsql {
! 66: SELECT * FROM t1 ORDER BY log;
! 67: }
! 68: } {0 1 3 4 4 8 5 4}
! 69: do_test insert2-1.3.1 {
! 70: catch {execsql {DROP TABLE t1}}
! 71: execsql {
! 72: CREATE TABLE t1(log int, cnt int);
! 73: PRAGMA count_changes=off;
! 74: INSERT INTO t1
! 75: SELECT log, count(*) FROM d1 GROUP BY log
! 76: INTERSECT SELECT n-1,log FROM d1;
! 77: }
! 78: } {}
! 79: do_test insert2-1.3.2 {
! 80: execsql {
! 81: SELECT * FROM t1 ORDER BY log;
! 82: }
! 83: } {1 1 2 2}
! 84: } ;# ifcapable compound
! 85: execsql {PRAGMA count_changes=off;}
! 86:
! 87: do_test insert2-1.4 {
! 88: catch {execsql {DROP TABLE t1}}
! 89: set r [execsql {
! 90: CREATE TABLE t1(log int, cnt int);
! 91: CREATE INDEX i1 ON t1(log);
! 92: CREATE INDEX i2 ON t1(cnt);
! 93: INSERT INTO t1 SELECT log, count() FROM d1 GROUP BY log;
! 94: SELECT * FROM t1 ORDER BY log;
! 95: }]
! 96: lappend r [execsql {SELECT cnt FROM t1 WHERE log=3}]
! 97: lappend r [execsql {SELECT log FROM t1 WHERE cnt=4 ORDER BY log}]
! 98: } {0 1 1 1 2 2 3 4 4 8 5 4 4 {3 5}}
! 99:
! 100: do_test insert2-2.0 {
! 101: execsql {
! 102: CREATE TABLE t3(a,b,c);
! 103: CREATE TABLE t4(x,y);
! 104: INSERT INTO t4 VALUES(1,2);
! 105: SELECT * FROM t4;
! 106: }
! 107: } {1 2}
! 108: do_test insert2-2.1 {
! 109: execsql {
! 110: INSERT INTO t3(a,c) SELECT * FROM t4;
! 111: SELECT * FROM t3;
! 112: }
! 113: } {1 {} 2}
! 114: do_test insert2-2.2 {
! 115: execsql {
! 116: DELETE FROM t3;
! 117: INSERT INTO t3(c,b) SELECT * FROM t4;
! 118: SELECT * FROM t3;
! 119: }
! 120: } {{} 2 1}
! 121: do_test insert2-2.3 {
! 122: execsql {
! 123: DELETE FROM t3;
! 124: INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
! 125: SELECT * FROM t3;
! 126: }
! 127: } {hi 2 1}
! 128:
! 129: integrity_check insert2-3.0
! 130:
! 131: # File table t4 with lots of data
! 132: #
! 133: do_test insert2-3.1 {
! 134: execsql {
! 135: SELECT * from t4;
! 136: }
! 137: } {1 2}
! 138: do_test insert2-3.2 {
! 139: set x [db total_changes]
! 140: execsql {
! 141: BEGIN;
! 142: INSERT INTO t4 VALUES(2,4);
! 143: INSERT INTO t4 VALUES(3,6);
! 144: INSERT INTO t4 VALUES(4,8);
! 145: INSERT INTO t4 VALUES(5,10);
! 146: INSERT INTO t4 VALUES(6,12);
! 147: INSERT INTO t4 VALUES(7,14);
! 148: INSERT INTO t4 VALUES(8,16);
! 149: INSERT INTO t4 VALUES(9,18);
! 150: INSERT INTO t4 VALUES(10,20);
! 151: COMMIT;
! 152: }
! 153: expr [db total_changes] - $x
! 154: } {9}
! 155: do_test insert2-3.2.1 {
! 156: execsql {
! 157: SELECT count(*) FROM t4;
! 158: }
! 159: } {10}
! 160: do_test insert2-3.3 {
! 161: ifcapable subquery {
! 162: execsql {
! 163: BEGIN;
! 164: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
! 165: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
! 166: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
! 167: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
! 168: COMMIT;
! 169: SELECT count(*) FROM t4;
! 170: }
! 171: } else {
! 172: db function max_x_t4 {execsql {SELECT max(x) FROM t4}}
! 173: execsql {
! 174: BEGIN;
! 175: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
! 176: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
! 177: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
! 178: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
! 179: COMMIT;
! 180: SELECT count(*) FROM t4;
! 181: }
! 182: }
! 183: } {160}
! 184: do_test insert2-3.4 {
! 185: execsql {
! 186: BEGIN;
! 187: UPDATE t4 SET y='lots of data for the row where x=' || x
! 188: || ' and y=' || y || ' - even more data to fill space';
! 189: COMMIT;
! 190: SELECT count(*) FROM t4;
! 191: }
! 192: } {160}
! 193: do_test insert2-3.5 {
! 194: ifcapable subquery {
! 195: execsql {
! 196: BEGIN;
! 197: INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
! 198: SELECT count(*) from t4;
! 199: ROLLBACK;
! 200: }
! 201: } else {
! 202: execsql {
! 203: BEGIN;
! 204: INSERT INTO t4 SELECT x+max_x_t4()+1,y FROM t4;
! 205: SELECT count(*) from t4;
! 206: ROLLBACK;
! 207: }
! 208: }
! 209: } {320}
! 210: do_test insert2-3.6 {
! 211: execsql {
! 212: SELECT count(*) FROM t4;
! 213: }
! 214: } {160}
! 215: do_test insert2-3.7 {
! 216: execsql {
! 217: BEGIN;
! 218: DELETE FROM t4 WHERE x!=123;
! 219: SELECT count(*) FROM t4;
! 220: ROLLBACK;
! 221: }
! 222: } {1}
! 223: do_test insert2-3.8 {
! 224: db changes
! 225: } {159}
! 226: integrity_check insert2-3.9
! 227:
! 228: # Ticket #901
! 229: #
! 230: ifcapable tempdb {
! 231: do_test insert2-4.1 {
! 232: execsql {
! 233: CREATE TABLE Dependencies(depId integer primary key,
! 234: class integer, name str, flag str);
! 235: CREATE TEMPORARY TABLE DepCheck(troveId INT, depNum INT,
! 236: flagCount INT, isProvides BOOL, class INTEGER, name STRING,
! 237: flag STRING);
! 238: INSERT INTO DepCheck
! 239: VALUES(-1, 0, 1, 0, 2, 'libc.so.6', 'GLIBC_2.0');
! 240: INSERT INTO Dependencies
! 241: SELECT DISTINCT
! 242: NULL,
! 243: DepCheck.class,
! 244: DepCheck.name,
! 245: DepCheck.flag
! 246: FROM DepCheck LEFT OUTER JOIN Dependencies ON
! 247: DepCheck.class == Dependencies.class AND
! 248: DepCheck.name == Dependencies.name AND
! 249: DepCheck.flag == Dependencies.flag
! 250: WHERE
! 251: Dependencies.depId is NULL;
! 252: };
! 253: } {}
! 254: }
! 255:
! 256: #--------------------------------------------------------------------
! 257: # Test that the INSERT works when the SELECT statement (a) references
! 258: # the table being inserted into and (b) is optimized to use an index
! 259: # only.
! 260: do_test insert2-5.1 {
! 261: execsql {
! 262: CREATE TABLE t2(a, b);
! 263: INSERT INTO t2 VALUES(1, 2);
! 264: CREATE INDEX t2i1 ON t2(a);
! 265: INSERT INTO t2 SELECT a, 3 FROM t2 WHERE a = 1;
! 266: SELECT * FROM t2;
! 267: }
! 268: } {1 2 1 3}
! 269: ifcapable subquery {
! 270: do_test insert2-5.2 {
! 271: execsql {
! 272: INSERT INTO t2 SELECT (SELECT a FROM t2), 4;
! 273: SELECT * FROM t2;
! 274: }
! 275: } {1 2 1 3 1 4}
! 276: }
! 277:
! 278: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to insert2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test