File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / insert.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    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.
   13: #
   14: # $Id: insert.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   15: 
   16: set testdir [file dirname $argv0]
   17: source $testdir/tester.tcl
   18: 
   19: # Try to insert into a non-existant table.
   20: #
   21: do_test insert-1.1 {
   22:   set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg]
   23:   lappend v $msg
   24: } {1 {no such table: test1}}
   25: 
   26: # Try to insert into sqlite_master
   27: #
   28: do_test insert-1.2 {
   29:   set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg]
   30:   lappend v $msg
   31: } {1 {table sqlite_master may not be modified}}
   32: 
   33: # Try to insert the wrong number of entries.
   34: #
   35: do_test insert-1.3 {
   36:   execsql {CREATE TABLE test1(one int, two int, three int)}
   37:   set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg]
   38:   lappend v $msg
   39: } {1 {table test1 has 3 columns but 2 values were supplied}}
   40: do_test insert-1.3b {
   41:   set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg]
   42:   lappend v $msg
   43: } {1 {table test1 has 3 columns but 4 values were supplied}}
   44: do_test insert-1.3c {
   45:   set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg]
   46:   lappend v $msg
   47: } {1 {4 values for 2 columns}}
   48: do_test insert-1.3d {
   49:   set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg]
   50:   lappend v $msg
   51: } {1 {1 values for 2 columns}}
   52: 
   53: # Try to insert into a non-existant column of a table.
   54: #
   55: do_test insert-1.4 {
   56:   set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg]
   57:   lappend v $msg
   58: } {1 {table test1 has no column named four}}
   59: 
   60: # Make sure the inserts actually happen
   61: #
   62: do_test insert-1.5 {
   63:   execsql {INSERT INTO test1 VALUES(1,2,3)}
   64:   execsql {SELECT * FROM test1}
   65: } {1 2 3}
   66: do_test insert-1.5b {
   67:   execsql {INSERT INTO test1 VALUES(4,5,6)}
   68:   execsql {SELECT * FROM test1 ORDER BY one}
   69: } {1 2 3 4 5 6}
   70: do_test insert-1.5c {
   71:   execsql {INSERT INTO test1 VALUES(7,8,9)}
   72:   execsql {SELECT * FROM test1 ORDER BY one}
   73: } {1 2 3 4 5 6 7 8 9}
   74: 
   75: do_test insert-1.6 {
   76:   execsql {DELETE FROM test1}
   77:   execsql {INSERT INTO test1(one,two) VALUES(1,2)}
   78:   execsql {SELECT * FROM test1 ORDER BY one}
   79: } {1 2 {}}
   80: do_test insert-1.6b {
   81:   execsql {INSERT INTO test1(two,three) VALUES(5,6)}
   82:   execsql {SELECT * FROM test1 ORDER BY one}
   83: } {{} 5 6 1 2 {}}
   84: do_test insert-1.6c {
   85:   execsql {INSERT INTO test1(three,one) VALUES(7,8)}
   86:   execsql {SELECT * FROM test1 ORDER BY one}
   87: } {{} 5 6 1 2 {} 8 {} 7}
   88: 
   89: # A table to use for testing default values
   90: #
   91: do_test insert-2.1 {
   92:   execsql {
   93:     CREATE TABLE test2(
   94:       f1 int default -111, 
   95:       f2 real default +4.32,
   96:       f3 int default +222,
   97:       f4 int default 7.89
   98:     )
   99:   }
  100:   execsql {SELECT * from test2}
  101: } {}
  102: do_test insert-2.2 {
  103:   execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)}
  104:   execsql {SELECT * FROM test2}
  105: } {10 4.32 -10 7.89}
  106: do_test insert-2.3 {
  107:   execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)}
  108:   execsql {SELECT * FROM test2 WHERE f1==-111}
  109: } {-111 1.23 222 -3.45}
  110: do_test insert-2.4 {
  111:   execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)}
  112:   execsql {SELECT * FROM test2 WHERE f1==77}
  113: } {77 1.23 222 3.45}
  114: do_test insert-2.10 {
  115:   execsql {
  116:     DROP TABLE test2;
  117:     CREATE TABLE test2(
  118:       f1 int default 111, 
  119:       f2 real default -4.32,
  120:       f3 text default hi,
  121:       f4 text default 'abc-123',
  122:       f5 varchar(10)
  123:     )
  124:   }
  125:   execsql {SELECT * from test2}
  126: } {}
  127: do_test insert-2.11 {
  128:   execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')}
  129:   execsql {SELECT * FROM test2}
  130: } {111 -2.22 hi hi! {}}
  131: do_test insert-2.12 {
  132:   execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')}
  133:   execsql {SELECT * FROM test2 ORDER BY f1}
  134: } {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}
  135: 
  136: # Do additional inserts with default values, but this time
  137: # on a table that has indices.  In particular we want to verify
  138: # that the correct default values are inserted into the indices.
  139: #
  140: do_test insert-3.1 {
  141:   execsql {
  142:     DELETE FROM test2;
  143:     CREATE INDEX index9 ON test2(f1,f2);
  144:     CREATE INDEX indext ON test2(f4,f5);
  145:     SELECT * from test2;
  146:   }
  147: } {}
  148: 
  149: # Update for sqlite3 v3:
  150: # Change the 111 to '111' in the following two test cases, because
  151: # the default value is being inserted as a string. TODO: It shouldn't be.
  152: do_test insert-3.2 {
  153:   execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
  154:   execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
  155: } {111 -3.33 hi hum {}}
  156: do_test insert-3.3 {
  157:   execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  158:   execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
  159: } {111 -3.33 hi hum {}}
  160: do_test insert-3.4 {
  161:   execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
  162: } {22 -4.44 hi abc-123 wham}
  163: ifcapable {reindex} {
  164:   do_test insert-3.5 {
  165:     execsql REINDEX
  166:   } {}
  167: }
  168: integrity_check insert-3.5
  169: 
  170: # Test of expressions in the VALUES clause
  171: #
  172: do_test insert-4.1 {
  173:   execsql {
  174:     CREATE TABLE t3(a,b,c);
  175:     INSERT INTO t3 VALUES(1+2+3,4,5);
  176:     SELECT * FROM t3;
  177:   }
  178: } {6 4 5}
  179: do_test insert-4.2 {
  180:   ifcapable subquery {
  181:     execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);}
  182:   } else {
  183:     set maxa [execsql {SELECT max(a) FROM t3}]
  184:     execsql "INSERT INTO t3 VALUES($maxa+1,5,6);"
  185:   }
  186:   execsql {
  187:     SELECT * FROM t3 ORDER BY a;
  188:   }
  189: } {6 4 5 7 5 6}
  190: ifcapable subquery {
  191:   do_test insert-4.3 {
  192:     catchsql {
  193:       INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6);
  194:       SELECT * FROM t3 ORDER BY a;
  195:     }
  196:   } {1 {no such column: t3.a}}
  197: }
  198: do_test insert-4.4 {
  199:   ifcapable subquery {
  200:     execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);}
  201:   } else {
  202:     set b [execsql {SELECT b FROM t3 WHERE a = 0}]
  203:     if {$b==""} {set b NULL}
  204:     execsql "INSERT INTO t3 VALUES($b,6,7);"
  205:   }
  206:   execsql {
  207:     SELECT * FROM t3 ORDER BY a;
  208:   }
  209: } {{} 6 7 6 4 5 7 5 6}
  210: do_test insert-4.5 {
  211:   execsql {
  212:     SELECT b,c FROM t3 WHERE a IS NULL;
  213:   }
  214: } {6 7}
  215: do_test insert-4.6 {
  216:   catchsql {
  217:     INSERT INTO t3 VALUES(notafunc(2,3),2,3);
  218:   }
  219: } {1 {no such function: notafunc}}
  220: do_test insert-4.7 {
  221:   execsql {
  222:     INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99);
  223:     SELECT * FROM t3 WHERE c=99;
  224:   }
  225: } {1 3 99}
  226: 
  227: # Test the ability to insert from a temporary table into itself.
  228: # Ticket #275.
  229: #
  230: ifcapable tempdb {
  231:   do_test insert-5.1 {
  232:     execsql {
  233:       CREATE TEMP TABLE t4(x);
  234:       INSERT INTO t4 VALUES(1);
  235:       SELECT * FROM t4;
  236:     }
  237:   } {1}
  238:   do_test insert-5.2 {
  239:     execsql {
  240:       INSERT INTO t4 SELECT x+1 FROM t4;
  241:       SELECT * FROM t4;
  242:     }
  243:   } {1 2}
  244:   ifcapable {explain} {
  245:     do_test insert-5.3 {
  246:       # verify that a temporary table is used to copy t4 to t4
  247:       set x [execsql {
  248:         EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4;
  249:       }]
  250:       expr {[lsearch $x OpenEphemeral]>0}
  251:     } {1}
  252:   }
  253:   
  254:   do_test insert-5.4 {
  255:     # Verify that table "test1" begins on page 3.  This should be the same
  256:     # page number used by "t4" above.
  257:     #
  258:     # Update for v3 - the first table now begins on page 2 of each file, not 3.
  259:     execsql {
  260:       SELECT rootpage FROM sqlite_master WHERE name='test1';
  261:     }
  262:   } [expr $AUTOVACUUM?3:2]
  263:   do_test insert-5.5 {
  264:     # Verify that "t4" begins on page 3.
  265:     #
  266:     # Update for v3 - the first table now begins on page 2 of each file, not 3.
  267:     execsql {
  268:       SELECT rootpage FROM sqlite_temp_master WHERE name='t4';
  269:     }
  270:   } {2}
  271:   do_test insert-5.6 {
  272:     # This should not use an intermediate temporary table.
  273:     execsql {
  274:       INSERT INTO t4 SELECT one FROM test1 WHERE three=7;
  275:       SELECT * FROM t4
  276:     }
  277:   } {1 2 8}
  278:   ifcapable {explain} {
  279:     do_test insert-5.7 {
  280:       # verify that no temporary table is used to copy test1 to t4
  281:       set x [execsql {
  282:         EXPLAIN INSERT INTO t4 SELECT one FROM test1;
  283:       }]
  284:       expr {[lsearch $x OpenTemp]>0}
  285:     } {0}
  286:   }
  287: }
  288: 
  289: # Ticket #334:  REPLACE statement corrupting indices.
  290: #
  291: ifcapable conflict {
  292:   # The REPLACE command is not available if SQLITE_OMIT_CONFLICT is 
  293:   # defined at compilation time.
  294:   do_test insert-6.1 {
  295:     execsql {
  296:       CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
  297:       INSERT INTO t1 VALUES(1,2);
  298:       INSERT INTO t1 VALUES(2,3);
  299:       SELECT b FROM t1 WHERE b=2;
  300:     }
  301:   } {2}
  302:   do_test insert-6.2 {
  303:     execsql {
  304:       REPLACE INTO t1 VALUES(1,4);
  305:       SELECT b FROM t1 WHERE b=2;
  306:     }
  307:   } {}
  308:   do_test insert-6.3 {
  309:     execsql {
  310:       UPDATE OR REPLACE t1 SET a=2 WHERE b=4;
  311:       SELECT * FROM t1 WHERE b=4;
  312:     }
  313:   } {2 4}
  314:   do_test insert-6.4 {
  315:     execsql {
  316:       SELECT * FROM t1 WHERE b=3;
  317:     }
  318:   } {}
  319:   ifcapable {reindex} {
  320:     do_test insert-6.5 {
  321:       execsql REINDEX
  322:     } {}
  323:   }
  324:   do_test insert-6.6 {
  325:     execsql {
  326:       DROP TABLE t1;
  327:     }
  328:   } {}
  329: }
  330: 
  331: # Test that the special optimization for queries of the form 
  332: # "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with 
  333: # INSERT statments.
  334: do_test insert-7.1 {
  335:   execsql {
  336:     CREATE TABLE t1(a);
  337:     INSERT INTO t1 VALUES(1);
  338:     INSERT INTO t1 VALUES(2);
  339:     CREATE INDEX i1 ON t1(a);
  340:   }
  341: } {}
  342: do_test insert-7.2 {
  343:   execsql {
  344:     INSERT INTO t1 SELECT max(a) FROM t1;
  345:   }
  346: } {}
  347: do_test insert-7.3 {
  348:   execsql {
  349:     SELECT a FROM t1;
  350:   }
  351: } {1 2 2}
  352: 
  353: # Ticket #1140:  Check for an infinite loop in the algorithm that tests
  354: # to see if the right-hand side of an INSERT...SELECT references the left-hand
  355: # side.
  356: #
  357: ifcapable subquery&&compound {
  358:   do_test insert-8.1 {
  359:     execsql {
  360:       INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)
  361:     }
  362:   } {}
  363: }
  364: 
  365: # Make sure the rowid cache in the VDBE is reset correctly when
  366: # an explicit rowid is given.
  367: #
  368: do_test insert-9.1 {
  369:   execsql {
  370:     CREATE TABLE t5(x);
  371:     INSERT INTO t5 VALUES(1);
  372:     INSERT INTO t5 VALUES(2);
  373:     INSERT INTO t5 VALUES(3);
  374:     INSERT INTO t5(rowid, x) SELECT nullif(x*2+10,14), x+100 FROM t5;
  375:     SELECT rowid, x FROM t5;
  376:   }
  377: } {1 1 2 2 3 3 12 101 13 102 16 103}
  378: do_test insert-9.2 {
  379:   execsql {
  380:     CREATE TABLE t6(x INTEGER PRIMARY KEY, y);
  381:     INSERT INTO t6 VALUES(1,1);
  382:     INSERT INTO t6 VALUES(2,2);
  383:     INSERT INTO t6 VALUES(3,3);
  384:     INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6;
  385:     SELECT x, y FROM t6;
  386:   }
  387: } {1 1 2 2 3 3 12 101 13 102 16 103}
  388: 
  389: integrity_check insert-99.0
  390: 
  391: finish_test

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>