1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this script is in-memory database backend. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: memdb.test,v 1.6 2003/08/05 13:13:39 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# In the following sequence of tests, compute the MD5 sum of the content 227c478bd9Sstevel@tonic-gate# of a table, make lots of modifications to that table, then do a rollback. 237c478bd9Sstevel@tonic-gate# Verify that after the rollback, the MD5 checksum is unchanged. 247c478bd9Sstevel@tonic-gate# 257c478bd9Sstevel@tonic-gate# These tests were browed from trans.tcl. 267c478bd9Sstevel@tonic-gate# 277c478bd9Sstevel@tonic-gatedo_test memdb-1.1 { 287c478bd9Sstevel@tonic-gate db close 297c478bd9Sstevel@tonic-gate sqlite db :memory: 307c478bd9Sstevel@tonic-gate # sqlite db test.db 317c478bd9Sstevel@tonic-gate execsql { 327c478bd9Sstevel@tonic-gate BEGIN; 337c478bd9Sstevel@tonic-gate CREATE TABLE t3(x TEXT); 347c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(randstr(10,400)); 357c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(randstr(10,400)); 367c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 377c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 387c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 397c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 407c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 417c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 427c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 437c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 447c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3; 457c478bd9Sstevel@tonic-gate COMMIT; 467c478bd9Sstevel@tonic-gate SELECT count(*) FROM t3; 477c478bd9Sstevel@tonic-gate } 487c478bd9Sstevel@tonic-gate} {1024} 497c478bd9Sstevel@tonic-gate 507c478bd9Sstevel@tonic-gate# The following procedure computes a "signature" for table "t3". If 51*1da57d55SToomas Soome# T3 changes in any way, the signature should change. 527c478bd9Sstevel@tonic-gate# 537c478bd9Sstevel@tonic-gate# This is used to test ROLLBACK. We gather a signature for t3, then 547c478bd9Sstevel@tonic-gate# make lots of changes to t3, then rollback and take another signature. 557c478bd9Sstevel@tonic-gate# The two signatures should be the same. 567c478bd9Sstevel@tonic-gate# 577c478bd9Sstevel@tonic-gateproc signature {{fn {}}} { 587c478bd9Sstevel@tonic-gate set rx [db eval {SELECT x FROM t3}] 597c478bd9Sstevel@tonic-gate # set r1 [md5 $rx\n] 607c478bd9Sstevel@tonic-gate if {$fn!=""} { 617c478bd9Sstevel@tonic-gate # set fd [open $fn w] 627c478bd9Sstevel@tonic-gate # puts $fd $rx 637c478bd9Sstevel@tonic-gate # close $fd 647c478bd9Sstevel@tonic-gate } 657c478bd9Sstevel@tonic-gate # set r [db eval {SELECT count(*), md5sum(x) FROM t3}] 667c478bd9Sstevel@tonic-gate # puts "SIG($fn)=$r1" 677c478bd9Sstevel@tonic-gate return [list [string length $rx] $rx] 687c478bd9Sstevel@tonic-gate} 697c478bd9Sstevel@tonic-gate 707c478bd9Sstevel@tonic-gate# Do rollbacks. Make sure the signature does not change. 717c478bd9Sstevel@tonic-gate# 727c478bd9Sstevel@tonic-gateset limit 10 737c478bd9Sstevel@tonic-gatefor {set i 2} {$i<=$limit} {incr i} { 747c478bd9Sstevel@tonic-gate set ::sig [signature one] 757c478bd9Sstevel@tonic-gate # puts "sig=$sig" 767c478bd9Sstevel@tonic-gate set cnt [lindex $::sig 0] 777c478bd9Sstevel@tonic-gate set ::journal_format [expr {($i%3)+1}] 787c478bd9Sstevel@tonic-gate if {$i%2==0} { 797c478bd9Sstevel@tonic-gate execsql {PRAGMA synchronous=FULL} 807c478bd9Sstevel@tonic-gate } else { 817c478bd9Sstevel@tonic-gate execsql {PRAGMA synchronous=NORMAL} 827c478bd9Sstevel@tonic-gate } 837c478bd9Sstevel@tonic-gate do_test memdb-1.$i.1-$cnt { 847c478bd9Sstevel@tonic-gate execsql { 857c478bd9Sstevel@tonic-gate BEGIN; 867c478bd9Sstevel@tonic-gate DELETE FROM t3 WHERE random()%10!=0; 877c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 887c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 897c478bd9Sstevel@tonic-gate ROLLBACK; 907c478bd9Sstevel@tonic-gate } 917c478bd9Sstevel@tonic-gate set sig2 [signature two] 927c478bd9Sstevel@tonic-gate } $sig 937c478bd9Sstevel@tonic-gate # puts "sig2=$sig2" 947c478bd9Sstevel@tonic-gate # if {$sig2!=$sig} exit 957c478bd9Sstevel@tonic-gate do_test memdb-1.$i.2-$cnt { 967c478bd9Sstevel@tonic-gate execsql { 977c478bd9Sstevel@tonic-gate BEGIN; 987c478bd9Sstevel@tonic-gate DELETE FROM t3 WHERE random()%10!=0; 997c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 1007c478bd9Sstevel@tonic-gate DELETE FROM t3 WHERE random()%10!=0; 1017c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 1027c478bd9Sstevel@tonic-gate ROLLBACK; 1037c478bd9Sstevel@tonic-gate } 1047c478bd9Sstevel@tonic-gate signature 1057c478bd9Sstevel@tonic-gate } $sig 1067c478bd9Sstevel@tonic-gate if {$i<$limit} { 1077c478bd9Sstevel@tonic-gate do_test memdb-1.$i.9-$cnt { 1087c478bd9Sstevel@tonic-gate execsql { 1097c478bd9Sstevel@tonic-gate INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; 1107c478bd9Sstevel@tonic-gate } 1117c478bd9Sstevel@tonic-gate } {} 1127c478bd9Sstevel@tonic-gate } 1137c478bd9Sstevel@tonic-gate set ::pager_old_format 0 1147c478bd9Sstevel@tonic-gate} 1157c478bd9Sstevel@tonic-gate 1167c478bd9Sstevel@tonic-gatedo_test memdb-2.1 { 1177c478bd9Sstevel@tonic-gate execsql { 1187c478bd9Sstevel@tonic-gate PRAGMA integrity_check 1197c478bd9Sstevel@tonic-gate } 1207c478bd9Sstevel@tonic-gate} {ok} 1217c478bd9Sstevel@tonic-gate 1227c478bd9Sstevel@tonic-gatedo_test memdb-3.1 { 1237c478bd9Sstevel@tonic-gate execsql { 1247c478bd9Sstevel@tonic-gate CREATE TABLE t4(a,b,c,d); 1257c478bd9Sstevel@tonic-gate BEGIN; 1267c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(1,2,3,4); 1277c478bd9Sstevel@tonic-gate SELECT * FROM t4; 1287c478bd9Sstevel@tonic-gate } 1297c478bd9Sstevel@tonic-gate} {1 2 3 4} 1307c478bd9Sstevel@tonic-gatedo_test memdb-3.2 { 1317c478bd9Sstevel@tonic-gate execsql { 1327c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table'; 1337c478bd9Sstevel@tonic-gate } 1347c478bd9Sstevel@tonic-gate} {t3 t4} 1357c478bd9Sstevel@tonic-gatedo_test memdb-3.3 { 1367c478bd9Sstevel@tonic-gate execsql { 1377c478bd9Sstevel@tonic-gate DROP TABLE t4; 1387c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table'; 1397c478bd9Sstevel@tonic-gate } 1407c478bd9Sstevel@tonic-gate} {t3} 1417c478bd9Sstevel@tonic-gatedo_test memdb-3.4 { 1427c478bd9Sstevel@tonic-gate execsql { 1437c478bd9Sstevel@tonic-gate ROLLBACK; 1447c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table'; 1457c478bd9Sstevel@tonic-gate } 1467c478bd9Sstevel@tonic-gate} {t3 t4} 1477c478bd9Sstevel@tonic-gate 1487c478bd9Sstevel@tonic-gate# Create tables for the first group of tests. 1497c478bd9Sstevel@tonic-gate# 1507c478bd9Sstevel@tonic-gatedo_test memdb-4.0 { 1517c478bd9Sstevel@tonic-gate execsql { 1527c478bd9Sstevel@tonic-gate CREATE TABLE t1(a, b, c, UNIQUE(a,b)); 1537c478bd9Sstevel@tonic-gate CREATE TABLE t2(x); 1547c478bd9Sstevel@tonic-gate SELECT c FROM t1 ORDER BY c; 1557c478bd9Sstevel@tonic-gate } 1567c478bd9Sstevel@tonic-gate} {} 1577c478bd9Sstevel@tonic-gate 1587c478bd9Sstevel@tonic-gate# Six columns of configuration data as follows: 1597c478bd9Sstevel@tonic-gate# 1607c478bd9Sstevel@tonic-gate# i The reference number of the test 1617c478bd9Sstevel@tonic-gate# conf The conflict resolution algorithm on the BEGIN statement 1627c478bd9Sstevel@tonic-gate# cmd An INSERT or REPLACE command to execute against table t1 1637c478bd9Sstevel@tonic-gate# t0 True if there is an error from $cmd 1647c478bd9Sstevel@tonic-gate# t1 Content of "c" column of t1 assuming no error in $cmd 1657c478bd9Sstevel@tonic-gate# t2 Content of "x" column of t2 1667c478bd9Sstevel@tonic-gate# 1677c478bd9Sstevel@tonic-gateforeach {i conf cmd t0 t1 t2} { 1687c478bd9Sstevel@tonic-gate 1 {} INSERT 1 {} 1 1697c478bd9Sstevel@tonic-gate 2 {} {INSERT OR IGNORE} 0 3 1 1707c478bd9Sstevel@tonic-gate 3 {} {INSERT OR REPLACE} 0 4 1 1717c478bd9Sstevel@tonic-gate 4 {} REPLACE 0 4 1 1727c478bd9Sstevel@tonic-gate 5 {} {INSERT OR FAIL} 1 {} 1 1737c478bd9Sstevel@tonic-gate 6 {} {INSERT OR ABORT} 1 {} 1 1747c478bd9Sstevel@tonic-gate 7 {} {INSERT OR ROLLBACK} 1 {} {} 1757c478bd9Sstevel@tonic-gate 8 IGNORE INSERT 0 3 1 1767c478bd9Sstevel@tonic-gate 9 IGNORE {INSERT OR IGNORE} 0 3 1 1777c478bd9Sstevel@tonic-gate 10 IGNORE {INSERT OR REPLACE} 0 4 1 1787c478bd9Sstevel@tonic-gate 11 IGNORE REPLACE 0 4 1 1797c478bd9Sstevel@tonic-gate 12 IGNORE {INSERT OR FAIL} 1 {} 1 1807c478bd9Sstevel@tonic-gate 13 IGNORE {INSERT OR ABORT} 1 {} 1 1817c478bd9Sstevel@tonic-gate 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 1827c478bd9Sstevel@tonic-gate 15 REPLACE INSERT 0 4 1 1837c478bd9Sstevel@tonic-gate 16 FAIL INSERT 1 {} 1 1847c478bd9Sstevel@tonic-gate 17 ABORT INSERT 1 {} 1 1857c478bd9Sstevel@tonic-gate 18 ROLLBACK INSERT 1 {} {} 1867c478bd9Sstevel@tonic-gate} { 1877c478bd9Sstevel@tonic-gate do_test memdb-4.$i { 1887c478bd9Sstevel@tonic-gate if {$conf!=""} {set conf "ON CONFLICT $conf"} 1897c478bd9Sstevel@tonic-gate set r0 [catch {execsql [subst { 1907c478bd9Sstevel@tonic-gate DELETE FROM t1; 1917c478bd9Sstevel@tonic-gate DELETE FROM t2; 1927c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2,3); 1937c478bd9Sstevel@tonic-gate BEGIN $conf; 194*1da57d55SToomas Soome INSERT INTO t2 VALUES(1); 1957c478bd9Sstevel@tonic-gate $cmd INTO t1 VALUES(1,2,4); 1967c478bd9Sstevel@tonic-gate }]} r1] 1977c478bd9Sstevel@tonic-gate catch {execsql {COMMIT}} 1987c478bd9Sstevel@tonic-gate if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 1997c478bd9Sstevel@tonic-gate set r2 [execsql {SELECT x FROM t2}] 2007c478bd9Sstevel@tonic-gate list $r0 $r1 $r2 2017c478bd9Sstevel@tonic-gate } [list $t0 $t1 $t2] 2027c478bd9Sstevel@tonic-gate} 2037c478bd9Sstevel@tonic-gate 2047c478bd9Sstevel@tonic-gatedo_test memdb-5.0 { 2057c478bd9Sstevel@tonic-gate execsql { 2067c478bd9Sstevel@tonic-gate DROP TABLE t2; 2077c478bd9Sstevel@tonic-gate DROP TABLE t3; 2087c478bd9Sstevel@tonic-gate CREATE TABLE t2(a,b,c); 2097c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(1,2,1); 2107c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(2,3,2); 2117c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(3,4,1); 2127c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(4,5,4); 2137c478bd9Sstevel@tonic-gate SELECT c FROM t2 ORDER BY b; 2147c478bd9Sstevel@tonic-gate CREATE TABLE t3(x); 2157c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1); 2167c478bd9Sstevel@tonic-gate } 2177c478bd9Sstevel@tonic-gate} {1 2 1 4} 2187c478bd9Sstevel@tonic-gate 2197c478bd9Sstevel@tonic-gate# Six columns of configuration data as follows: 2207c478bd9Sstevel@tonic-gate# 2217c478bd9Sstevel@tonic-gate# i The reference number of the test 2227c478bd9Sstevel@tonic-gate# conf1 The conflict resolution algorithm on the UNIQUE constraint 2237c478bd9Sstevel@tonic-gate# conf2 The conflict resolution algorithm on the BEGIN statement 2247c478bd9Sstevel@tonic-gate# cmd An UPDATE command to execute against table t1 2257c478bd9Sstevel@tonic-gate# t0 True if there is an error from $cmd 2267c478bd9Sstevel@tonic-gate# t1 Content of "b" column of t1 assuming no error in $cmd 2277c478bd9Sstevel@tonic-gate# t2 Content of "x" column of t3 2287c478bd9Sstevel@tonic-gate# 2297c478bd9Sstevel@tonic-gateforeach {i conf1 conf2 cmd t0 t1 t2} { 2307c478bd9Sstevel@tonic-gate 1 {} {} UPDATE 1 {6 7 8 9} 1 2317c478bd9Sstevel@tonic-gate 2 REPLACE {} UPDATE 0 {7 6 9} 1 2327c478bd9Sstevel@tonic-gate 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 2337c478bd9Sstevel@tonic-gate 4 FAIL {} UPDATE 1 {6 7 3 4} 1 2347c478bd9Sstevel@tonic-gate 5 ABORT {} UPDATE 1 {1 2 3 4} 1 2357c478bd9Sstevel@tonic-gate 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 2367c478bd9Sstevel@tonic-gate 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 2377c478bd9Sstevel@tonic-gate 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 2387c478bd9Sstevel@tonic-gate 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 2397c478bd9Sstevel@tonic-gate 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 2407c478bd9Sstevel@tonic-gate 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 2417c478bd9Sstevel@tonic-gate 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 2427c478bd9Sstevel@tonic-gate 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 2437c478bd9Sstevel@tonic-gate 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 2447c478bd9Sstevel@tonic-gate 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 2457c478bd9Sstevel@tonic-gate 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 2467c478bd9Sstevel@tonic-gate 17 {} IGNORE UPDATE 0 {6 7 3 9} 1 2477c478bd9Sstevel@tonic-gate 18 {} REPLACE UPDATE 0 {7 6 9} 1 2487c478bd9Sstevel@tonic-gate 19 {} FAIL UPDATE 1 {6 7 3 4} 1 2497c478bd9Sstevel@tonic-gate 20 {} ABORT UPDATE 1 {1 2 3 4} 1 2507c478bd9Sstevel@tonic-gate 21 {} ROLLBACK UPDATE 1 {1 2 3 4} 0 2517c478bd9Sstevel@tonic-gate 22 REPLACE IGNORE UPDATE 0 {6 7 3 9} 1 2527c478bd9Sstevel@tonic-gate 23 IGNORE REPLACE UPDATE 0 {7 6 9} 1 2537c478bd9Sstevel@tonic-gate 24 REPLACE FAIL UPDATE 1 {6 7 3 4} 1 2547c478bd9Sstevel@tonic-gate 25 IGNORE ABORT UPDATE 1 {1 2 3 4} 1 2557c478bd9Sstevel@tonic-gate 26 REPLACE ROLLBACK UPDATE 1 {1 2 3 4} 0 2567c478bd9Sstevel@tonic-gate} { 2577c478bd9Sstevel@tonic-gate if {$t0} {set t1 {column a is not unique}} 2587c478bd9Sstevel@tonic-gate do_test memdb-5.$i { 2597c478bd9Sstevel@tonic-gate if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 2607c478bd9Sstevel@tonic-gate if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 2617c478bd9Sstevel@tonic-gate set r0 [catch {execsql [subst { 2627c478bd9Sstevel@tonic-gate DROP TABLE t1; 2637c478bd9Sstevel@tonic-gate CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 2647c478bd9Sstevel@tonic-gate INSERT INTO t1 SELECT * FROM t2; 2657c478bd9Sstevel@tonic-gate UPDATE t3 SET x=0; 2667c478bd9Sstevel@tonic-gate BEGIN $conf2; 2677c478bd9Sstevel@tonic-gate $cmd t3 SET x=1; 2687c478bd9Sstevel@tonic-gate $cmd t1 SET b=b*2; 2697c478bd9Sstevel@tonic-gate $cmd t1 SET a=c+5; 2707c478bd9Sstevel@tonic-gate }]} r1] 2717c478bd9Sstevel@tonic-gate catch {execsql {COMMIT}} 2727c478bd9Sstevel@tonic-gate if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 2737c478bd9Sstevel@tonic-gate set r2 [execsql {SELECT x FROM t3}] 2747c478bd9Sstevel@tonic-gate list $r0 $r1 $r2 2757c478bd9Sstevel@tonic-gate } [list $t0 $t1 $t2] 2767c478bd9Sstevel@tonic-gate} 2777c478bd9Sstevel@tonic-gate 2787c478bd9Sstevel@tonic-gatedo_test memdb-6.1 { 2797c478bd9Sstevel@tonic-gate execsql { 2807c478bd9Sstevel@tonic-gate SELECT * FROM t2; 2817c478bd9Sstevel@tonic-gate } 2827c478bd9Sstevel@tonic-gate} {1 2 1 2 3 2 3 4 1 4 5 4} 2837c478bd9Sstevel@tonic-gatedo_test memdb-6.2 { 2847c478bd9Sstevel@tonic-gate execsql { 2857c478bd9Sstevel@tonic-gate BEGIN; 2867c478bd9Sstevel@tonic-gate DROP TABLE t2; 2877c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 2887c478bd9Sstevel@tonic-gate } 2897c478bd9Sstevel@tonic-gate} {t1 t3 t4} 2907c478bd9Sstevel@tonic-gatedo_test memdb-6.3 { 2917c478bd9Sstevel@tonic-gate execsql { 2927c478bd9Sstevel@tonic-gate ROLLBACK; 2937c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 2947c478bd9Sstevel@tonic-gate } 2957c478bd9Sstevel@tonic-gate} {t1 t2 t3 t4} 2967c478bd9Sstevel@tonic-gatedo_test memdb-6.4 { 2977c478bd9Sstevel@tonic-gate execsql { 2987c478bd9Sstevel@tonic-gate SELECT * FROM t2; 2997c478bd9Sstevel@tonic-gate } 3007c478bd9Sstevel@tonic-gate} {1 2 1 2 3 2 3 4 1 4 5 4} 3017c478bd9Sstevel@tonic-gatedo_test memdb-6.5 { 3027c478bd9Sstevel@tonic-gate execsql { 3037c478bd9Sstevel@tonic-gate SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1; 3047c478bd9Sstevel@tonic-gate } 3057c478bd9Sstevel@tonic-gate} {1 2 3 4 5} 3067c478bd9Sstevel@tonic-gatedo_test memdb-6.6 { 3077c478bd9Sstevel@tonic-gate execsql { 3087c478bd9Sstevel@tonic-gate CREATE INDEX i2 ON t2(c); 3097c478bd9Sstevel@tonic-gate SELECT a FROM t2 ORDER BY c; 3107c478bd9Sstevel@tonic-gate } 3117c478bd9Sstevel@tonic-gate} {1 3 2 4} 3127c478bd9Sstevel@tonic-gatedo_test memdb-6.6 { 3137c478bd9Sstevel@tonic-gate execsql { 3147c478bd9Sstevel@tonic-gate SELECT a FROM t2 ORDER BY c DESC; 3157c478bd9Sstevel@tonic-gate } 3167c478bd9Sstevel@tonic-gate} {4 2 3 1} 3177c478bd9Sstevel@tonic-gatedo_test memdb-6.7 { 3187c478bd9Sstevel@tonic-gate execsql { 3197c478bd9Sstevel@tonic-gate BEGIN; 3207c478bd9Sstevel@tonic-gate CREATE TABLE t5(x,y); 3217c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(1,2); 3227c478bd9Sstevel@tonic-gate SELECT * FROM t5; 3237c478bd9Sstevel@tonic-gate } 3247c478bd9Sstevel@tonic-gate} {1 2} 3257c478bd9Sstevel@tonic-gatedo_test memdb-6.8 { 3267c478bd9Sstevel@tonic-gate execsql { 3277c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 3287c478bd9Sstevel@tonic-gate } 3297c478bd9Sstevel@tonic-gate} {t1 t2 t3 t4 t5} 3307c478bd9Sstevel@tonic-gatedo_test memdb-6.9 { 3317c478bd9Sstevel@tonic-gate execsql { 3327c478bd9Sstevel@tonic-gate ROLLBACK; 3337c478bd9Sstevel@tonic-gate SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1; 3347c478bd9Sstevel@tonic-gate } 3357c478bd9Sstevel@tonic-gate} {t1 t2 t3 t4} 3367c478bd9Sstevel@tonic-gatedo_test memdb-6.10 { 3377c478bd9Sstevel@tonic-gate execsql { 3387c478bd9Sstevel@tonic-gate CREATE TABLE t5(x PRIMARY KEY, y UNIQUE); 3397c478bd9Sstevel@tonic-gate SELECT * FROM t5; 3407c478bd9Sstevel@tonic-gate } 3417c478bd9Sstevel@tonic-gate} {} 3427c478bd9Sstevel@tonic-gatedo_test memdb-6.11 { 3437c478bd9Sstevel@tonic-gate execsql { 3447c478bd9Sstevel@tonic-gate SELECT * FROM t5 ORDER BY y DESC; 3457c478bd9Sstevel@tonic-gate } 3467c478bd9Sstevel@tonic-gate} {} 3477c478bd9Sstevel@tonic-gatedo_test memdb-6.12 { 3487c478bd9Sstevel@tonic-gate execsql { 3497c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(1,2); 3507c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(3,4); 3517c478bd9Sstevel@tonic-gate REPLACE INTO t5 VALUES(1,4); 3527c478bd9Sstevel@tonic-gate SELECT rowid,* FROM t5; 3537c478bd9Sstevel@tonic-gate } 3547c478bd9Sstevel@tonic-gate} {3 1 4} 3557c478bd9Sstevel@tonic-gatedo_test memdb-6.13 { 3567c478bd9Sstevel@tonic-gate execsql { 3577c478bd9Sstevel@tonic-gate DELETE FROM t5 WHERE x>5; 3587c478bd9Sstevel@tonic-gate SELECT * FROM t5; 3597c478bd9Sstevel@tonic-gate } 3607c478bd9Sstevel@tonic-gate} {1 4} 3617c478bd9Sstevel@tonic-gatedo_test memdb-6.14 { 3627c478bd9Sstevel@tonic-gate execsql { 3637c478bd9Sstevel@tonic-gate DELETE FROM t5 WHERE y<3; 3647c478bd9Sstevel@tonic-gate SELECT * FROM t5; 3657c478bd9Sstevel@tonic-gate } 3667c478bd9Sstevel@tonic-gate} {1 4} 3677c478bd9Sstevel@tonic-gatedo_test memdb-6.15 { 3687c478bd9Sstevel@tonic-gate execsql { 3697c478bd9Sstevel@tonic-gate DELETE FROM t5 WHERE x>0; 3707c478bd9Sstevel@tonic-gate SELECT * FROM t5; 3717c478bd9Sstevel@tonic-gate } 3727c478bd9Sstevel@tonic-gate} {} 3737c478bd9Sstevel@tonic-gate 3747c478bd9Sstevel@tonic-gatedo_test memdb-7.1 { 3757c478bd9Sstevel@tonic-gate execsql { 3767c478bd9Sstevel@tonic-gate CREATE TABLE t6(x); 3777c478bd9Sstevel@tonic-gate INSERT INTO t6 VALUES(1); 3787c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+1 FROM t6; 3797c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+2 FROM t6; 3807c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+4 FROM t6; 3817c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+8 FROM t6; 3827c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+16 FROM t6; 3837c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+32 FROM t6; 3847c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+64 FROM t6; 3857c478bd9Sstevel@tonic-gate INSERT INTO t6 SELECT x+128 FROM t6; 3867c478bd9Sstevel@tonic-gate SELECT count(*) FROM (SELECT DISTINCT x FROM t6); 3877c478bd9Sstevel@tonic-gate } 3887c478bd9Sstevel@tonic-gate} {256} 3897c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=256} {incr i} { 3907c478bd9Sstevel@tonic-gate do_test memdb-7.2.$i { 3917c478bd9Sstevel@tonic-gate execsql "DELETE FROM t6 WHERE x=\ 3927c478bd9Sstevel@tonic-gate (SELECT x FROM t6 ORDER BY random() LIMIT 1)" 3937c478bd9Sstevel@tonic-gate execsql {SELECT count(*) FROM t6} 3947c478bd9Sstevel@tonic-gate } [expr {256-$i}] 3957c478bd9Sstevel@tonic-gate} 3967c478bd9Sstevel@tonic-gate 3977c478bd9Sstevel@tonic-gatefinish_test 398