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