sqlite3/test/insert2.test

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