AryaWu/sqlite

1# 2001 September 152#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this file is testing the INSERT statement that takes is13# result from a SELECT.14#15# $Id: insert2.test,v 1.19 2008/01/16 18:20:42 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix insert220 21# Create some tables with data that we can select against22#23do_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#34do_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}48do_test insert2-1.1.2 {49  db changes50} {6}51do_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 55ifcapable compound {56do_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 log62       EXCEPT SELECT n-1,log FROM d1;63  }64} {4}65do_test insert2-1.2.2 {66  execsql {67    SELECT * FROM t1 ORDER BY log;68  }69} {0 1 3 4 4 8 5 4}70do_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 log77       INTERSECT SELECT n-1,log FROM d1;78  }79} {}80do_test insert2-1.3.2 {81  execsql {82    SELECT * FROM t1 ORDER BY log;83  }84} {1 1 2 2}85} ;# ifcapable compound86execsql {PRAGMA count_changes=off;}87 88do_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 101do_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}109do_test insert2-2.1 {110  execsql {111    INSERT INTO t3(a,c) SELECT * FROM t4;112    SELECT * FROM t3;113  }114} {1 {} 2}115do_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}122do_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 130integrity_check insert2-3.0131 132# File table t4 with lots of data133#134do_test insert2-3.1 {135  execsql {136    SELECT * from t4;137  }138} {1 2}139do_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] - $x155} {9}156do_test insert2-3.2.1 {157  execsql {158    SELECT count(*) FROM t4;159  }160} {10}161do_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}185do_test insert2-3.4 {186  execsql {187    BEGIN;188    UPDATE t4 SET y='lots of data for the row where x=' || x189                     || ' and y=' || y || ' - even more data to fill space';190    COMMIT;191    SELECT count(*) FROM t4;192  }193} {160}194do_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}211do_test insert2-3.6 {212  execsql {213    SELECT count(*) FROM t4;214  }215} {160}216do_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}224do_test insert2-3.8 {225  db changes226} {159}227integrity_check insert2-3.9228 229# Ticket #901230#231ifcapable 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) references259# the table being inserted into and (b) is optimized to use an index260# only.261do_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}270ifcapable 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 279do_execsql_test 6.0 { 280  CREATE TABLE t5(a, b, c DEFAULT 'c', d);281}282do_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 287ifcapable 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 298finish_test299